怎样在触发器中区分Update,insert,delete动作?
alost 2007-08-11 11:36:44 由于update是一个delete和insert的组合,不知道怎么才能区分
哪位高手能给个答案啊?
create trigger tr_test_insert on test
for insert,delete,update
as
declare
@rows int
select @rows=@@rowcount ---把@@rowcount记录下来,防止变化
if @@rowcount<1 return ---没有修改
if update(f1) --修改了第一个字段a1,你也可以判断其他的条件
begin
if exists(select * from deleted) and @rows>=1
begin --修改以前的值
insert into test1 select f1, user_name(), host_name(), 'old value', getdate() from inserted
if exists(select * from inserted) and @rows>1 --修改以后的值
select @rows
insert into test1 select f1, user_name(), host_name(), 'new value', getdate() from inserted
end
if exists(select * from inserted) and @rows =0
begin
select @rows
insert into test1 select f1, user_name(), host_name(), 'insert', getdate() from inserted i
end
if exists(select * from deleted) and @rows =0
begin
select @rows
insert into test1 select f1, user_name(), host_name(), 'delete', getdate() from deleted
end
/*
insert into test1 select f1, user_name(), host_name(), 'old value', getdate() from deleted
insert into test1 select f1, user_name(), host_name(), 'new value', getdate() from inserted
*/
end
实在弄不清该怎么判断条件了,各位兄弟姐妹给解围啊!