sql 2008触发器(一)AFTER触发器

 

[dbo].[userTab]--->主表里面的字段有：

[dbo].[userTab].[userID]

[dbo].[userTab].[userName]

[dbo].[userTab].[password]

[dbo].[userTab].[sex]

[dbo].[userTab].[signature]

[dbo].[userTab].[headPic]

[dbo].[userTab].[Remind]

 

[dbo].[test]---->触发表字段:

[dbo].[test].[name]

[dbo].[test].[password]

[dbo].[test].[signature]

 

 

----创建添加触发器  关键字inserted

create trigger tri_QQ --触发器名

on [userTab]

after insert

as

begin

insert into [dbo].[test] select inserted.[userName],inserted.[password],inserted.[signature] from inserted

end

 

--创建删除触发器

create trigger tri_QQDelete

on userTab

after delete

as

begin

delete from test where name=(select userName from deleted)

--注意这里是怎么获取删除条件的

end

 

--创建更新触发器

 

create trigger tri_QQUpdate

on [userTab]

after update not for replication --为了防止删除主键

as

if(update([userName]))

--这里需要注意你的触发表中可能有多个字段，不知道更新的是哪个字段，需要判断一下

begin

update test set name = (select userName from inserted) where test.ID=(select userID from inserted) 

--注意如何获取更改字段以及更改条件

end

else if(update([password]))

begin

update test set [test].[password] = (select [password] from inserted) where test.ID=(select userID from inserted) 

end

else if(update([signature]))

begin

update test set [test].[signature] = (select [signature] from inserted) where test.ID=(select userID from inserted) 

end