SQL Server Trigger and Cursor Example

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

--查看数据库中所有触发器 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

--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