在mssql中,使用触发器有一定的好处,比如同步更新数据到某个表等,不过触发器不能使用过多,会造成性能上的影响,这方面的影响还挺大,如果有一个上百万记录的表,不推荐使用触发器,下面我们来看下使用sql触发器的应用。
主要有:
inserted 一个是deleted
inserted表示insert或update的新值集合
deleted 是update时的旧值或要删除的值集合
应用1:插入记录触发器
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/*
**************************************
检测插入的触发器,当新建用户时,
会将用户的信息拷入ZWUserSysc以便同步
************************************** */
CREATE TRIGGER Trg_ZWTLog_Insert
ON [ DN_user ]
for Insert
AS
begin
declare @id varchar ( 50 )
declare @Name varchar ( 50 )
declare @LogonName varchar ( 50 )
declare @Password varchar ( 50 )
select @id = id, @Name = name, @LogonName = LogonNm, @password = password from inserted
insert into ZWUserSysc (ID,Name,LogonNm,Password,status) values ( @id , @Name , @LogonName , @password , ' I ' )
end
检测插入的触发器,当新建用户时,
会将用户的信息拷入ZWUserSysc以便同步
************************************** */
CREATE TRIGGER Trg_ZWTLog_Insert
ON [ DN_user ]
for Insert
AS
begin
declare @id varchar ( 50 )
declare @Name varchar ( 50 )
declare @LogonName varchar ( 50 )
declare @Password varchar ( 50 )
select @id = id, @Name = name, @LogonName = LogonNm, @password = password from inserted
insert into ZWUserSysc (ID,Name,LogonNm,Password,status) values ( @id , @Name , @LogonName , @password , ' I ' )
end
应用二、更新记录触发器
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/*
**************************************
检测更新的触发器,当有用户修改了自己的密码,
会将用户的信息拷入ZWUserSysc以便同步
************************************** */
CREATE TRIGGER Trg_ZWTLog_UPDATE
ON [ DN_user ]
for UPDATE
AS
begin
declare @id varchar ( 50 )
declare @Name varchar ( 50 )
declare @LogonName varchar ( 50 )
declare @Password varchar ( 50 )
select @id = id, @Name = name, @LogonName = LogonNm, @password = password from inserted
insert into ZWUserSysc (ID,Name,LogonNm,Password,status) values ( @id , @Name , @LogonName , @password , ' U ' )
end
GO
检测更新的触发器,当有用户修改了自己的密码,
会将用户的信息拷入ZWUserSysc以便同步
************************************** */
CREATE TRIGGER Trg_ZWTLog_UPDATE
ON [ DN_user ]
for UPDATE
AS
begin
declare @id varchar ( 50 )
declare @Name varchar ( 50 )
declare @LogonName varchar ( 50 )
declare @Password varchar ( 50 )
select @id = id, @Name = name, @LogonName = LogonNm, @password = password from inserted
insert into ZWUserSysc (ID,Name,LogonNm,Password,status) values ( @id , @Name , @LogonName , @password , ' U ' )
end
GO
应用三、删除记录触发器
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/*
**************************************
检测删除的触发器,当用户被删除时,
会将用户的信息拷入ZWUserSysc以便同步
************************************** */
CREATE TRIGGER Trg_ZWTLog_Delete
ON [ DN_user ]
for Delete
AS
begin
declare @id varchar ( 50 )
declare @Name varchar ( 50 )
declare @LogonName varchar ( 50 )
declare @Password varchar ( 50 )
select @id = id, @Name = name, @LogonName = LogonNm, @password = password from Deleted
insert into ZWUserSysc (ID,Name,LogonNm,Password,status) values ( @id , @Name , @LogonName , @password , ' D ' )
end
GO
检测删除的触发器,当用户被删除时,
会将用户的信息拷入ZWUserSysc以便同步
************************************** */
CREATE TRIGGER Trg_ZWTLog_Delete
ON [ DN_user ]
for Delete
AS
begin
declare @id varchar ( 50 )
declare @Name varchar ( 50 )
declare @LogonName varchar ( 50 )
declare @Password varchar ( 50 )
select @id = id, @Name = name, @LogonName = LogonNm, @password = password from Deleted
insert into ZWUserSysc (ID,Name,LogonNm,Password,status) values ( @id , @Name , @LogonName , @password , ' D ' )
end
GO