当前位置：首页 > 数据库 > MsSql > 详细页面

SQL Server Trigger Example

时间：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