SQL Server Trigger and Cursor 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 TriClerkOfficeInsert
go
create trigger TriClerkOfficeInsert
on 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 TriClerkOfficeUpdate
go
create trigger TriClerkOfficeUpdate
on 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 TriClerkOfficeDelete
go
create trigger TriClerkOfficeDelete
on ClerkOffice
    for delete --删除触发
as    
   declare @id uniqueidentifier
    select @id=ClerkId from deleted;
    delete ClerkOfficeRole where ClerkRoleKey=@id;
    print '删除数据成功！';
go

　　

 string_split 分割字符串函数  SQL SERVER 2016 以上版本

--1
declare @linkmanno int ,@name nvarchar(500),@str nvarchar(50),@key varchar(50)
set @name=''
set @key='1,2,3'
select @linkmanno=min(RelationId) from RelationCo where RelationId in (SELECT value FROM string_split(@key,','))
select @str=RelationName from RelationCo where RelationId=@linkmanno --- 第一条
set @name=@str
--select @linkmanno
while @linkmanno is not null 
begin 
--针对当前ID号为@linkmanno的记录执行一些操作 Geovin Du
select @linkmanno=min(RelationId) from RelationCo where RelationId > @linkmanno and RelationId in (SELECT value FROM string_split(@key,','))  --循环
if @linkmanno is null
break
--select @linkmanno
select @str=RelationName from RelationCo where RelationId=@linkmanno
if @name=''
	set @name=@str
else
	set @name=@name+','+@str
end
select @name
go 

--2 游标
declare @linkmanno int ,@name nvarchar(500),@str nvarchar(50),@key varchar(50)
set @name=''
set @key='1,3'
DECLARE Du_Cursor CURSOR --定义游标 
FOR (SELECT * FROM RelationCo  where RelationId in (SELECT value FROM string_split(@key,','))) --查出需要的集合放到游标中 
OPEN Du_Cursor; --打开游标 
FETCH NEXT FROM Du_Cursor into @linkmanno,@str; --读取第一行数据 
WHILE @@FETCH_STATUS = 0     
　　BEGIN  
	   if @name=''
			set @name=@str
		else
			set @name=@name+','+@str
　　　　--UPDATE MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新         
　　　　--DELETE FROM MemberAccount WHERE CURRENT OF My_Cursor; --删除         
　　　　　　FETCH NEXT FROM Du_Cursor into @linkmanno,@str; --读取下一行数据
　　END 
　　CLOSE Du_Cursor; --关闭游标 
　　DEALLOCATE Du_Cursor; --释放游标 GO
select @name,@linkmanno
go