sql 三类触发器

插入

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/***************************************

检测插入的触发器，当新建用户时，
会将用户的信息拷入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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

修改

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

删除

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO