create trigger ALL_Stu ON dbo.stu FOR INSERT,UPDATE,DELETE AS BEGIN set nocount on declare @Ins int,@Del int select @Ins=count(*) from Inserted select @Del=count(*) from Deleted if @Ins>0 and @Del=0 begin insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime) select id, name, sex, major, birdate, 'ADD',getdate() from Inserted end else if @Ins>0 and @Del>0 begin insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime) select id, name, sex, major, birdate, 'UPDATE1',getdate() from Deleted
insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime) select id, name, sex, major, birdate, 'UPDATE2',getdate() from Inserted end else if @Ins=0 and @Del>0 begin insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime) select id, name, sex, major, birdate, 'DEL',getdate() from Deleted end
END
create trigger trig_name after insert,update,delete as begin insert into t2 select * from t1 end
create trigger my_trig on t1 for insert ,update ,delete as if not exists(select 1 from inserted) insert t2 select ... from inserted else if not exists(select 1 from deleted) insert t2 select ... from deleted else insert t2 select ... from inserted go
create trigger t1_in_up_de
after insert,update,delete
as
insert into t2 select ....
ASinsert into t2 (col1) values (1);
select 的数据都是t1的数据,这样写在delete 的时候t1的数据查不到t2里。
ON dbo.stu
FOR INSERT,UPDATE,DELETE
AS
BEGIN
set nocount on
declare @Ins int,@Del int
select @Ins=count(*) from Inserted
select @Del=count(*) from Deleted
if @Ins>0 and @Del=0
begin
insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime)
select id, name, sex, major, birdate, 'ADD',getdate() from Inserted
end
else if @Ins>0 and @Del>0
begin
insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime)
select id, name, sex, major, birdate, 'UPDATE1',getdate() from Deleted
insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime)
select id, name, sex, major, birdate, 'UPDATE2',getdate() from Inserted
end
else if @Ins=0 and @Del>0
begin
insert into Stu_Log(id, name, sex, major, birdate, FType, FOpTime)
select id, name, sex, major, birdate, 'DEL',getdate() from Deleted
end
END
after insert,update,delete
as
begin
insert into t2 select * from t1
end
create trigger my_trig on t1 for insert ,update ,delete
as
if not exists(select 1 from inserted)
insert t2 select ... from inserted
else if not exists(select 1 from deleted)
insert t2 select ... from deleted
else
insert t2 select ... from inserted
go