Create trigger yourTriggerName on yourTable
For Insert
As
....Select * from inserted ...
(没有deleted表) Create trigger yourTriggerName on yourTable
For Update
As
....select * from inserted a,deleted b...
Create trigger yourTriggerName on yourTable
For Delete
As
....select * from deleted ...
(没有inserted表)
For Insert
As
....Select * from inserted ...
(没有deleted表) Create trigger yourTriggerName on yourTable
For Update
As
....select * from inserted a,deleted b...
Create trigger yourTriggerName on yourTable
For Delete
As
....select * from deleted ...
(没有inserted表)
For Insert
As
....Select *,'Insert' as 操作类型 from inserted ...
(没有deleted表) Create trigger yourTriggerName on yourTable
For Update
As
....select *,'Update' as 操作类型 from inserted a,deleted b...
Create trigger yourTriggerName on yourTable
For Delete
As
....select *,'delete' as 操作类型 from deleted ...
(没有inserted表)
???
什么 意思阿?
在删除时,没有Inserted这个表,只有Deleted表
只有在Update时,才有这两个表
所以要看你的需要决定要建几个触发器
inserted 和 deleted这两个表是系统自动生成的.
inserted中记录的是新增或修改后的内容
deleted中记录的是删除或修改前的内容
a
id,name
1 aa
2 bb1.新增
执行:insert into a(id,name) values(3,'cc')
这时,在触发器中
inserted表的内容就是:
3 cc
deleted表内容为空2.修改
执行:update a set name=name+'1' where id<3
这时,在触发器中
inserted表的内容就是:
1 aa1
2 bb1
deleted表内容为空
1 aa
2 bb3.删除
执行:delete from a where id=2
这时,在触发器中
inserted表的内容就是空
deleted表内容为空
2 bb
方法1
create trigger aa on 你的表
for insert,delete,update
if exists(select * from inserted)
if exists(select * from deleted)
....... --记录操作为修改的处理语句
else
.......... --记录操作为新增的处理语句
else
......... --记录操作为删除的处理语句
方法2.分开写
create trigger aa on 你的表
for insert
as
........ --记录操作为新增的处理语句create trigger aa on 你的表
for update
as
........ --记录操作为修改的处理语句create trigger aa on 你的表
for delete
as
........ --记录操作为删除的处理语句
for insert
as
declare @id int,@name varchar(50),@test varchar(50),@test2 varchar(50)
select @id = a,id,@name=a.name,@test= a.test,@test2 = a.test2 from inserted a
insert into tableName (id,name,test,tes2) values (@id,@name,@test,@test2)gocreate trigger t_name1 on tableName
for update,delete
as
declare @id int,@name varchar(50),@test varchar(50),@test2 varchar(50)
select @id = a,id,@name=a.name,@test= a.test,@test2 = a.test2 from deleted a
insert into tableName (id,name,test,tes2) values (@id,@name,@test,@test2)go
for insert,update,delete
as
begin
declare @id int,@name varchar(50),@test varchar(50),@test2 varchar(50)if update(name)
begin
select @id = a,id,@name=a.name,@test= a.test,@test2 = a.test2 from deleted a
insert into tableName (id,name,test,tes2) values (@id,@name,@test,@test2)
select @id = a,id,@name=a.name,@test= a.test,@test2 = a.test2 from inserted a
insert into tableName (id,name,test,tes2) values (@id,@name,@test,@test2)
endselect @id = a,id,@name=a.name,@test= a.test,@test2 = a.test2 from inserted a
insert into tableName (id,name,test,tes2) values (@id,@name,@test,@test2)select @id = a,id,@name=a.name,@test= a.test,@test2 = a.test2 from deleted a
insert into tableName (id,name,test,tes2) values (@id,@name,@test,@test2)
end
go