SQLSERVER（MSSQL） Trigger before替代方案before使用场景MSSQL触发器方案代码实现结论

106 阅读 0 评论 70 点赞

我是靠谱客的博主粗犷橘子，这篇文章主要介绍SQLSERVER（MSSQL） Trigger before替代方案before使用场景MSSQL触发器方案代码实现结论，现在分享给大家，希望可以做个参考。

before使用场景

在ORACLE使用before的场景一般为一下几种

1.在执行dml语句之前做相关校验

2. 在执行dml语句之前，执行其他dml语句对本表或者其他表做dml操作

3.在执行dml语句对某个字段修改的时候，改变他的值

MSSQL触发器

首先目前MSSQL中没有类似Oracle、Postgresql数据库的before功能，但是最近项目中需要做数据库迁移，从postgresql迁移到SQLServer(MSSQL),其中涉及到trigger中before的替换，经过查阅资料目前为找到完美的替代，但是有两种方案可以去替代；

在介绍方案之前首先了解一下：

        触发器触发时，系统自动在内存中创建deleted表或inserted表，inserted表临时保存了插入或更新后的记录行，deleted表临时保存了删除或更新前的记录行，inserted和deleted这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中，不是存储在数据库中，因此，不允许用户直接对其修改。这两个表的结构与被该触发器作用的表在相同的表结构。这两个表是动态驻留在内存中的，当触发器工作完成，它们也被删除。

通过2个临时表的状态可以判断目前trigger处理的操作：

  inserted is null && deleted is not null => delete操作
  inserted is not null && deleted is not null => update操作
inserted is not null && deleted is null => insert操作

方案

一：instead of

目前网上很多介绍都是用instead of 去代替before，因为他们在执行顺序上和oracle上一致

instead of触发器是在insert、update、delete这些操作进行之前就被激活了，并且不再去执行原来的dml SQL操作，而是用触发器内部的SQL语句代替执行。所以用了instead of的表做任何dml操作，都只会执行触发器内部的语

优点：个人感觉他的唯一一个优点就是在执行顺序上在触发它的dml语句之前

坑点1：一个table或者view只能有一个instead of

坑点2：原始dml语句不会执行

这一点非常坑，如果原始dml不会执行，那我还要instead of 干什么？

坑点3：对于写在用了instead of 的trigger 里面的insert和update操作，就必须把所有列都写出来，否则你在trigger里面写了一个 update table A = ？where ID = ？，那么无论你在代码中update任何列都只会修改A列。这样就导致维护这个表就必须要去修改这个trigger，非常麻烦。

个人建议尽量不要用instead of出现在我们的项目中，太坑了！！！！！！

二：after 和 for

after和for都是在dml语句执行之后触发的（dml语句和 after trigger 要在同一个事物），不是特殊的before方法，用after和for都可以实现；

实现方式；在trigger中使用inserted和deleted做逻辑判断（是否重复等），如果逻辑校验失败就rolllback txn，将dml语句一起回滚；

缺点：dml语句会先于trigger执行

优点：其实单看after和for其实也没啥优点，但是和instead of相比，after 和 for 就显得非常利于维护

代码实现

以下分别实现了instead of trigger和 after trigger 实现了t_name的去重，和trigger对t_name 和t_desc的修改

复制代码

CREATE TABLE [dbo].[qf_tb](
	[t_no] [varchar](9) NOT NULL,
	[t_name] [varchar](200) NOT NULL,
	[t_desc] [varchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[t_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-- instead of trigger
 create trigger trigger_qftb on [qf_tb]
	instead of insert,update
as
begin
	declare @new_no varchar(8),
			@change_value varchar(200),
			@new_desc varchar(200),
			@new_name varchar(200);
	--insert
	if (exists(select 1 from inserted) and not exists(select 1 from deleted))
		begin
			if exists(select 1 from qf_tb  where t_name = (select t_name  from inserted) )
				begin
					RAISERROR('insert  error',16,1);
				end
			else 
				begin
			
					   select  @change_value = t_desc  from inserted;
					  set @change_value = @change_value + '1111';

insert into qf_tb  select t_no,t_name,@change_value from inserted;

-- insert into qf_tb  select * from inserted;	  
				end
		end
	else  -- update => exists(select 1 from inserted) and  exists(select 1 from deleted)  
	   begin
			select @new_no = t_no ,@new_name = t_name ,@new_desc = t_desc from  inserted;
			if exists(select 1 from qf_tb  where t_name = (select t_name  from inserted) )
				begin
					RAISERROR('update  error',16,1);
				end
			else 
				begin
					set  @new_desc =  @new_desc + '2222';
					 update qf_tb set t_no = @new_no,t_name = @new_name ,t_desc = @new_desc where t_no =(select t_no from deleted);
				end
		end
end
go

-- after of trigger
 create trigger trigger_qftb_after on [qf_tb]
	after insert,update
as
begin
	 declare @new_name varchar(200);
	 print('start trigger_qftb_after ');
	--insert

if (exists(select 1 from inserted) and not exists(select 1 from deleted))
		begin
			if exists(select 1 from qf_tb t,inserted i where t.t_name = i.t_name and t.t_no <> i.t_no )
				begin
					 print('error insert ');
					ROLLBACK TRANSACTION;
				end
			else 
				begin
					select   @new_name = t_name + 'after' from  inserted;
					 print(@new_name);
					update   qf_tb  set t_name = @new_name where t_no  = (select t_no from inserted);
				end

end
	else  -- update => exists(select 1 from inserted) and  exists(select 1 from deleted)  
	   begin
			if exists(select 1 from qf_tb t,inserted i where t.t_name = i.t_name and t.t_no <> i.t_no ) 
				begin
					 print('error update ');
					ROLLBACK TRANSACTION;
				end
			else
				begin
					select   @new_name = t_name + 'after'  from  inserted;
					update   qf_tb  set t_name = @new_name where t_no  = (select t_no from deleted);
				end
		end
	print('end trigger_qftb_after ');
end
go

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

CREATE TABLE [dbo].[qf_tb](
	[t_no] [varchar](9) NOT NULL,
	[t_name] [varchar](200) NOT NULL,
	[t_desc] [varchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[t_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


 -- instead of trigger
 create trigger trigger_qftb on [qf_tb]
	instead of insert,update
as
begin
	declare @new_no varchar(8),
			@change_value varchar(200),
			@new_desc varchar(200),
			@new_name varchar(200);
	--insert
	if (exists(select 1 from inserted) and not exists(select 1 from deleted))
		begin
			if exists(select 1 from qf_tb  where t_name = (select t_name  from inserted) )
				begin
					RAISERROR('insert  error',16,1);
				end
			else 
				begin
			
					   select  @change_value = t_desc  from inserted;
					  set @change_value = @change_value + '1111';

					  insert into qf_tb  select t_no,t_name,@change_value from inserted;

					 -- insert into qf_tb  select * from inserted;	  
				end
		end
	else  -- update => exists(select 1 from inserted) and  exists(select 1 from deleted)  
	   begin
			select @new_no = t_no ,@new_name = t_name ,@new_desc = t_desc from  inserted;
			if exists(select 1 from qf_tb  where t_name = (select t_name  from inserted) )
				begin
					RAISERROR('update  error',16,1);
				end
			else 
				begin
					set  @new_desc =  @new_desc + '2222';
					 update qf_tb set t_no = @new_no,t_name = @new_name ,t_desc = @new_desc where t_no =(select t_no from deleted);
				end
		end
end
go


 -- after of trigger
 create trigger trigger_qftb_after on [qf_tb]
	after insert,update
as
begin
	 declare @new_name varchar(200);
	 print('start trigger_qftb_after ');
	--insert

	if (exists(select 1 from inserted) and not exists(select 1 from deleted))
		begin
			if exists(select 1 from qf_tb t,inserted i where t.t_name = i.t_name and t.t_no <> i.t_no )
				begin
					 print('error insert ');
					ROLLBACK TRANSACTION;
				end
			else 
				begin
					select   @new_name = t_name + 'after' from  inserted;
					 print(@new_name);
					update   qf_tb  set t_name = @new_name where t_no  = (select t_no from inserted);
				end

		end
	else  -- update => exists(select 1 from inserted) and  exists(select 1 from deleted)  
	   begin
			if exists(select 1 from qf_tb t,inserted i where t.t_name = i.t_name and t.t_no <> i.t_no ) 
				begin
					 print('error update ');
					ROLLBACK TRANSACTION;
				end
			else
				begin
					select   @new_name = t_name + 'after'  from  inserted;
					update   qf_tb  set t_name = @new_name where t_no  = (select t_no from deleted);
				end
		end
	print('end trigger_qftb_after ');
end
go