时间:2020-08-13来源:www.pcxitongcheng.com作者:电脑系统城
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 |
2023-03-15
SQLserver存储过程写法与设置定时执行存储过程方法详解2023-03-11
sql语句将数据库一条数据通过分隔符切割成多列方法实例2023-03-06
MySQL中的常用树形结构设计总结最详细的 SQLSERVER ALWAYSON配置教程 一、准备工作: 二、搭建故障转移群集 三、搭建数据库AlwaysOn 四、过程中可能遇到的问题...
2023-03-06
2.1破解的时候,先要断网,然后再操作,按如图打开界面,点击Manage License 2.2点击Activate 2.3打开SQL.Prompt.Keygen.exe,将生成的注册码拷贝到输入框内,然后点击Activate 2.4 继续点击Activate manually 2.5 按如下步骤操...
2023-03-06