SQL Server Trigger Example

--查看数据库中所有触发器 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15-- 涂聚文(Geovin Du) edit https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/select * from sysobjects where xtype='TR'--exec sp_helptext 'TriClerkOfficeInsert'--创建insert插入类型触发器 PositionRoleDefaut ,当添加工员资料，在角色表中的添加if (object_id('TriClerkOfficeInsert', 'tr') is not null) drop trigger TriClerkOfficeInsertgocreate trigger TriClerkOfficeInserton ClerkOffice -- 指定创建触发器的表 for insert --插入触发as --定义变量 declare @id uniqueidentifier, @DefaultRoleId int, @PositionId int; --在inserted表中查询已经插入记录信息 select @id = ClerkId, @PositionId = ClerkPosition from inserted; select @DefaultRoleId= RoleDefautSet from PositionRoleDefaut where RolePositionId=@PositionId insert into ClerkOfficeRole(ClerkRoleKey,ClerkRoleSet) values(@id,@DefaultRoleId) print '添加成功！';go--修改时触发器if (object_id('TriClerkOfficeUpdate', 'tr') is not null) drop trigger TriClerkOfficeUpdategocreate trigger TriClerkOfficeUpdateon ClerkOffice -- 指定创建触发器的表 for update --修改时触发as --定义变量 declare @id uniqueidentifier, @DefaultRoleId int, @PositionId int,@ClerkName nvarchar(100),@OldClerkName nvarchar(100); --更新前的数据 --select @id = ClerkId,@OldClerkName=ClerkName,@PositionId=ClerkPosition from deleted; -- 修改前的数据就存在 deleted 这个表中	 --if (exists (select * from ClerkOffice where ClerkName like '%'+ @OldClerkName + '%'))  -- begin		 --更新后的数据		select @id = ClerkId,@ClerkName=ClerkName,@PositionId=ClerkPosition from inserted;-- 修改后的数据就存在 inserted 这个表中	 --end --select @id = ClerkId, @PositionId = ClerkPosition from ClerkOffice; select @DefaultRoleId= RoleDefautSet from PositionRoleDefaut where RolePositionId=@PositionId;	if(exists (select * from ClerkOfficeRole where ClerkRoleKey=@id))	begin update ClerkOfficeRole set ClerkRoleSet=@DefaultRoleId where ClerkRoleKey=@id;	end	else	begin	 insert into ClerkOfficeRole(ClerkRoleKey,ClerkRoleSet) values(@id,@DefaultRoleId);	end print '修改成功！';go--delete删除类型触发器if (object_id('TriClerkOfficeDelete', 'TR') is not null)drop trigger TriClerkOfficeDeletegocreate trigger TriClerkOfficeDeleteon ClerkOffice for delete --删除触发as  declare @id uniqueidentifier select @id=ClerkId from deleted; delete ClerkOfficeRole where ClerkRoleKey=@id; print '删除数据成功！';go

　　

SQL Server Trigger Example深挖数据 亚马逊运营绩效提升技巧沙龙、 WE海外仓项目、 深圳市跨境电子商务协会2016年度总结会、 良心干货：2019卖家做亚马逊如何突破运营瓶颈达到巅峰？、 Joom新招商政策曝光 剑指速卖通卖家？、 扒一扒Wish与抖音的算法，什么是大数据的流量池？、 春节去云台花园赏花要多少钱？、 从化宝趣玫瑰园有没有学生票？、 温泉有哪些种类？、