create trigger tri_g on 表 for insert,update,delete as begin ...语句 end
给你个例子,很相似的 use song go if exists (select name from sysobjects where name= 'tr_songlistchanged' and type='TR') drop trigger tr_songlistchanged go create trigger tr_songlistchanged on songlist for delete,insert,update asdeclare @insertCount int declare @deleteCount int declare @typeChange varchar(6) declare @updateType varchar(4)select @insertCount=count(*) from inserted select @deleteCount=count(*) from deleted select @updateType='' select @typeChange= case when @insertCount>0 and @deleteCount>0 then 'UPDATE' when @insertCount=0 and @deleteCount>0 then 'DELETE' else 'INSERT' endif @typeChange='DELETE' select @updateType='old' insert into watchsong (TypeChange,SongID,Artist,Title,Songaddtime) select @typeChange+@updateType,SongID,Artist,Title,Songaddtime from deleted
其中当songlist表变化,watchsong表会相应地加入变化的内容
if exists(select name from sysobjects where name='sample_trig' and type='TR') drop trigger sample_trig go create trigger sample_trig on table_name for insert, update, delete as begin insert into table_name select * from inserted update table_name set ..... delete from table_name where ..... end
for insert,update,delete
as
begin
...语句
end
use song
go
if exists (select name from sysobjects
where name= 'tr_songlistchanged' and type='TR')
drop trigger tr_songlistchanged
go
create trigger tr_songlistchanged
on songlist
for delete,insert,update
asdeclare @insertCount int
declare @deleteCount int
declare @typeChange varchar(6)
declare @updateType varchar(4)select @insertCount=count(*) from inserted
select @deleteCount=count(*) from deleted
select @updateType=''
select @typeChange=
case
when @insertCount>0 and @deleteCount>0
then 'UPDATE'
when @insertCount=0 and @deleteCount>0
then 'DELETE'
else 'INSERT'
endif @typeChange='DELETE' select @updateType='old'
insert into watchsong
(TypeChange,SongID,Artist,Title,Songaddtime)
select @typeChange+@updateType,SongID,Artist,Title,Songaddtime
from deleted
where name='sample_trig' and type='TR')
drop trigger sample_trig
go
create trigger sample_trig
on table_name
for insert, update, delete
as
begin
insert into table_name select * from inserted
update table_name set .....
delete from table_name where .....
end