22,209
社区成员
发帖
与我相关
我的任务
分享
-- 修改一下测试内容,使其更具一般性
use db1
go
-- 在 db1 中创建用户帐户 test
create user test without login;
go
grant select,delete on dbo.tab to test;
go
-- 由 test 删除 db1.dbo.tab 表中的行
execute as user='test';
delete from dbo.tab where id=1;
select user_name() username;
revert;
go
select * from db1.dbo.tab;
select * from db2.dbo.tab;
-- 创建测试数据库
use master
go
create database db1;
go
create database db2;
go
use db1
go
create table dbo.tab (id int);
go
insert into dbo.tab values(1);
go
-- 触发器确保删除 db1.dbo.tab 表行时,db2.dbo.tab 表上对应的 fid 列的值都设置 null
create trigger dbo.trg_tab_delete on dbo.tab
with execute as owner -- 让触发器以 dbo 的身份运行
for delete
as
update t set fid=null from db2.dbo.tab t, deleted i where t.fid=i.id;
go
use db2
go
create table dbo.tab (id int, fid int);
go
insert into dbo.tab values(1,1);
insert into dbo.tab values(2,1);
go
-- 关键:扩展模拟作用域
-- 有以下两个条件:
-- 其一,模拟账户的身份验证者(即账户所在数据库的 dbo)必须在目标数据库或实例内是可信的。
-- 即 dbo 在目标数据库所对应的用户帐户必须拥有目标数据库的 AUTHENTICATE 权限。
-- 由于 db1 和 db2 数据库的所有者为同一个登陆帐户,因此这里不必配置权限。
-- 其二,模拟账户所在的数据库必须被标记为可信的。
alter database db1 set trustworthy on;
go
-- 测试
delete from db1.dbo.tab where id=1;
go
select * from db1.dbo.tab;
select * from db2.dbo.tab;
/*
1 NULL
2 NULL
*/
use master
go
drop database db1;
go
drop database db2;
go
USE 正确的库名
GO
ALTER TRIGGER [accessevent_trigger] ON [dbo].[AccessEvent]
AFTER INSERT
AS
BEGIN
DECLARE @AccessTime DATETIME, @DoorName VARCHAR(128), @SiteCode SMALLINT, @CardNum INT
SET @AccessTime = GETDATE()
SET @DoorName = 'myroom'
SET @SiteCode = 1
SET @CardNum = 11111
INSERT INTO mytmpdb.dbo.accessevent( TIMESTAMP, PointDoor, CardSite, CardNumber )
VALUES( @AccessTime, @DoorName, @SiteCode, @CardNum )
DECLARE cur_insert CURSOR FOR
SELECT i.TimeStamp, d.uiname, ISNULL(i.SiteCode, 0), ISNULL(i.NonABACardNumber, 0)
FROM inserted i, [dbo].[door] d
WHERE i.EventClass = 4
AND i.DoorIdHi = d.ObjectIdHi
AND i.DoorIdLo = d.ObjectIdLo
AND ISNULL(i.SiteCode, 0)<>0
AND ISNULL(i.NonABACardNumber, 0)<>0
AND d.uiname<>''
OPEN cur_insert
FETCH NEXT FROM cur_insert INTO @AccessTime,@DoorName,@SiteCode,@CardNum
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO mytmpdb.dbo.accessevent( TIMESTAMP, PointDoor, CardSite, CardNumber )
VALUES(@AccessTime, @DoorName, @SiteCode, @CardNum )
FETCH NEXT FROM cur_insert INTO @AccessTime,@DoorName,@SiteCode,@CardNum
END
CLOSE cur_insert
DEALLOCATE cur_insert
END
GO
没发现什么大问题。你确定在查询分析器执行正常,确认触发器创建正确的数据库上吗?