个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
27
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章关于喜忧参半的SQL Server触发器详解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
前言 。
sql server触发器在非常有争议的主题。它们能以较低的成本提供便利，但经常被开发人员、dba误用，导致性能瓶颈或维护性挑战.
本文简要回顾了触发器，并深入讨论了如何有效地使用触发器，以及何时触发器会使开发人员陷入难以逃脱的困境.
虽然本文中的所有演示都是在sql server中进行的，但这里提供的建议是大多数数据库通用的。触发器带来的挑战在mysql、postgresql、mongodb和许多其他应用中也可以看到.
。
可以在数据库或表上定义sql server触发器，它允许代码在发生特定操作时自动执行。本文主要关注表上的dml触发器，因为它们往往被过度使用。相反，数据库的ddl触发器通常更集中，对性能的危害更小.
触发器是对表中数据更改时进行计算的一组代码。触发器可以定义为在插入、更新、删除或这些操作的任何组合上执行。merge操作可以触发语句中每个操作的触发器.
触发器可以定义为instead of或after。after触发器发生在数据写入表之后，是一组独立的操作，和写入表的操作在同一事务执行，但在写入发生之后执行。如果触发器失败，原始操作也会失败。instead of触发器替换调用的写操作。插入、更新或删除操作永远不会发生，而是执行触发器的内容.
触发器允许在发生写操作时执行tsql，而不管这些写操作的来源是什么。它们通常用于在希望确保执行写操作时运行关键操作，如日志记录、验证或其他dml。这很方便，写操作可以来自api、应用程序代码、发布脚本，或者内部流程，触发器无论如何都会触发.
。
用wideworldimporters示例数据库中的sales.orders 表举例，假设需要记录该表上的所有更新或删除操作，以及有关更改发生的一些细节。这个操作可以通过修改代码来完成，但是这样做需要对表的代码写入中的每个位置进行更改。通过触发器解决这一问题，可以采取以下步骤
1. 创建一个日志表来接受写入的数据。下面的tsql创建了一个简单日志表，以及一些添加的数据点:
|
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
|
create
table
sales.orders_log
( orders_log_id
int
not
null
identity(1,1)
constraint
pk_sales_orders_log
primary
key
clustered,
orderid
int
not
null
,
customerid_old
int
not
null
,
customerid_new
int
not
null
,
salespersonpersonid_old
int
not
null
,
salespersonpersonid_new
int
not
null
,
pickedbypersonid_old
int
null
,
pickedbypersonid_new
int
null
,
contactpersonid_old
int
not
null
,
contactpersonid_new
int
not
null
,
backorderorderid_old
int
null
,
backorderorderid_new
int
null
,
orderdate_old
date
not
null
,
orderdate_new
date
not
null
,
expecteddeliverydate_old
date
not
null
,
expecteddeliverydate_new
date
not
null
,
customerpurchaseordernumber_old nvarchar(20)
null
,
customerpurchaseordernumber_new nvarchar(20)
null
,
isundersupplybackordered_old
bit
not
null
,
isundersupplybackordered_new
bit
not
null
,
comments_old nvarchar(
max
)
null
,
comments_new nvarchar(
max
)
null
,
deliveryinstructions_old nvarchar(
max
)
null
,
deliveryinstructions_new nvarchar(
max
)
null
,
internalcomments_old nvarchar(
max
)
null
,
internalcomments_new nvarchar(
max
)
null
,
pickingcompletedwhen_old datetime2(7)
null
,
pickingcompletedwhen_new datetime2(7)
null
,
lasteditedby_old
int
not
null
,
lasteditedby_new
int
not
null
,
lasteditedwhen_old datetime2(7)
not
null
,
lasteditedwhen_new datetime2(7)
not
null
,
actiontype
varchar
(6)
not
null
,
actiontime datetime2(3)
not
null
,
username
varchar
(128)
null
);
|
该表记录所有列的旧值和新值。这是非常全面的，我们可以简单地记录旧版本的行，并能够通过将新版本和旧版本合并在一起来了解更改的过程。最后3列是新增的，提供了有关执行的操作类型(插入、更新或删除)、时间和操作人.
2. 创建一个触发器来记录表的更改
|
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
|
create
trigger
tr_sales_orders_audit
on
sales.orders
after
insert
,
update
,
delete
as
begin
set
nocount
on
;
insert
into
sales.orders_log
(orderid, customerid_old, customerid_new,
salespersonpersonid_old, salespersonpersonid_new,
pickedbypersonid_old, pickedbypersonid_new,
contactpersonid_old, contactpersonid_new,
backorderorderid_old, backorderorderid_new,
orderdate_old, orderdate_new, expecteddeliverydate_old,
expecteddeliverydate_new,
customerpurchaseordernumber_old,
customerpurchaseordernumber_new,
isundersupplybackordered_old,
isundersupplybackordered_new,
comments_old, comments_new,
deliveryinstructions_old, deliveryinstructions_new,
internalcomments_old, internalcomments_new,
pickingcompletedwhen_old,
pickingcompletedwhen_new, lasteditedby_old,
lasteditedby_new, lasteditedwhen_old,
lasteditedwhen_new, actiontype, actiontime, username)
select
isnull
(inserted.orderid, deleted.orderid)
as
orderid,
deleted.customerid
as
customerid_old,
inserted.customerid
as
customerid_new,
deleted.salespersonpersonid
as
salespersonpersonid_old,
inserted.salespersonpersonid
as
salespersonpersonid_new,
deleted.pickedbypersonid
as
pickedbypersonid_old,
inserted.pickedbypersonid
as
pickedbypersonid_new,
deleted.contactpersonid
as
contactpersonid_old,
inserted.contactpersonid
as
contactpersonid_new,
deleted.backorderorderid
as
backorderorderid_old,
inserted.backorderorderid
as
backorderorderid_new,
deleted.orderdate
as
orderdate_old,
inserted.orderdate
as
orderdate_new,
deleted.expecteddeliverydate
as
expecteddeliverydate_old,
inserted.expecteddeliverydate
as
expecteddeliverydate_new,
deleted.customerpurchaseordernumber
as
customerpurchaseordernumber_old,
inserted.customerpurchaseordernumber
as
customerpurchaseordernumber_new,
deleted.isundersupplybackordered
as
isundersupplybackordered_old,
inserted.isundersupplybackordered
as
isundersupplybackordered_new,
deleted.comments
as
comments_old,
inserted.comments
as
comments_new,
deleted.deliveryinstructions
as
deliveryinstructions_old,
inserted.deliveryinstructions
as
deliveryinstructions_new,
deleted.internalcomments
as
internalcomments_old,
inserted.internalcomments
as
internalcomments_new,
deleted.pickingcompletedwhen
as
pickingcompletedwhen_old,
inserted.pickingcompletedwhen
as
pickingcompletedwhen_new,
deleted.lasteditedby
as
lasteditedby_old,
inserted.lasteditedby
as
lasteditedby_new,
deleted.lasteditedwhen
as
lasteditedwhen_old,
inserted.lasteditedwhen
as
lasteditedwhen_new,
case
when
inserted.orderid
is
null
then
'delete'
when
deleted.orderid
is
null
then
'insert'
else
'update'
end
as
actiontype,
sysutcdatetime() actiontime,
suser_sname()
as
username
from
inserted
full
join
deleted
on
inserted.orderid = deleted.orderid;
end
|
该触发器的唯一功能是将数据插入到日志表中，每行数据对应一个给定的写操作。它很简单，随着时间的推移易于记录和维护，表也会发生变化。如果需要跟踪其他详细信息，可以添加其他列，如数据库名称、服务器名称、受影响列的行数或调用的应用程序.
3.最后一步是测试和验证日志表是否正确.
以下是添加触发器后对表进行更新的测试
|
1
2
3
4
5
6
7
8
|
update
orders
set
internalcomments =
'item is no longer backordered'
,
backorderorderid =
null
,
isundersupplybackordered = 0,
lasteditedby = 1,
lasteditedwhen = sysutcdatetime()
from
sales.orders
where
orders.orderid = 10;
|
结果如下:
点击并拖拽以移动 。
上面省略了一些列，但是我们可以快速确认已经触发了更改，包括日志表末尾新增的列.
。
前面的示例中，进行插入和删除操作后，读取日志表中使用的数据。这种特殊的表可以作为任何相关写操作的一部分。insert将包含被插入操作触发，delete将被删除操作触发，update包含被插入和删除操作触发.
对于insert和update，将包含表中每个列新值的快照。对于delete和update操作，将包含写操作之前表中每个列旧值的快照.
。
dml触发器的最佳使用是简短、简单且易于维护的写操作，这些操作在很大程度上独立于应用程序业务逻辑.
关键是让触发器代码保持足够的紧凑，从而便于维护。当触发器增长到成千上万行时，它们就成了开发人员不敢去打扰的黑盒。结果，更多的代码被添加进来，但是旧的代码很少被检查。即使有了文档，这也很难维护.
为了让触发器有效地发挥作用，应该将它们编写为基于设置的。如果存储过程必须在触发器中使用，则确保它们在需要时使用表值参数，以便可以基于集的方式移动数据。下面是一个触发器的示例，该触发器遍历id，以便使用结果顺序id执行示例存储过程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
create
trigger
tr_sales_orders_process
on
sales.orders
after
insert
as
begin
set
nocount
on
;
declare
@
count
int
;
select
@
count
=
count
(*)
from
inserted;
declare
@min_id
int
;
select
@min_id =
min
(orderid)
from
inserted;
declare
@current_id
int
= @min_id;
while @current_id < @current_id + @
count
begin
exec
dbo.process_order_fulfillment
@orderid = @current_id;
select
@current_id = @current_id + 1;
end
end
|
虽然相对简单，但当一次插入多行时对 sales.orders的insert操作的性能将受到影响，因为sql server在执行process_order_fulfillment存储过程时将被迫逐个执行。一个简单的修复方法是重写存储过程，并将一组order id传递到存储过程中，而不是一次一个地这样做
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create
type dbo.udt_orderid_list
as
table
(
orderid
int
not
null
,
primary
key
clustered
( orderid
asc
));
go
create
trigger
tr_sales_orders_process
on
sales.orders
after
insert
as
begin
set
nocount
on
;
declare
@orderid_list dbo.udt_orderid_list;
exec
dbo.process_order_fulfillment @orderids = @orderid_list;
end
|
更改的结果是将完整的id集合从触发器传递到存储过程并进行处理。只要存储过程以基于集合的方式管理这些数据，就可以避免重复执行，也就是说，避免在触发器内使用存储过程有很大的价值，因为它们添加了额外的封装层，进一步隐藏了在数据写入表时执行的tsql。它们应该被认为是最后的手段，只有当可以在应用程序的许多地方多次重写tsql时才使用.
。
架构师和开发人员面临的最大挑战之一是确保触发器只在需要时使用，而不允许它们成为一刀切的解决方案。向触发器添加tsql通常被认为比向应用程序添加代码更快、更容易，但随着时间的推移，这样做的成本会随着每添加一行代码而增加.
触发器在以下情况下会变得危险
这是一个很长的列表，但通常可以总结为短而简单的触发器会表现得更好，并避免上面的大多数陷阱。如果使用触发器来维护复杂的业务逻辑，那么随着时间的推移，越来越多的业务逻辑将被添加进来，并且不可避免地将违反上述最佳实践.
重要的是要注意，为了维护原子的、事务，受触发器影响的任何对象都将保持事务处于打开状态，直到该触发器完成。这意味着长触发器不仅会使事务持续时间更长，而且还会持有锁并导致持续时间更长。因此，在测试触发器时，在为现有触发器创建或添加额外逻辑时，应该了解它们对锁、阻塞和等待的影响.
。
有很多方法可以使触发器更易于维护、更容易理解和性能更高。以下是一些关于如何有效管理触发器和避免落入陷阱的建议.
触发器本身应该有良好的文档记录
此外，如果触发器中的tsql难以理解，那么可以添加内联注释，以帮助第一次查看它的开发人员.
下面是触发器文档的样例
|
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
|
/* 12/29/2020 ehp
this
trigger
logs
all
changes
to
the
table
to
the orders_log
table
that occur
for
non-internal customers.
customerid = -1 signifies an internal/test customer
and
these are
not
audited.
*/
create
trigger
tr_sales_orders_audit
on
sales.orders
for
insert
,
update
,
delete
as
begin
set
nocount
on
;
insert
into
sales.orders_log
(orderid, customerid_old, customerid_new,
salespersonpersonid_old, salespersonpersonid_new,
pickedbypersonid_old, pickedbypersonid_new,
contactpersonid_old, contactpersonid_new,
backorderorderid_old, backorderorderid_new,
orderdate_old, orderdate_new,
expecteddeliverydate_old,
expecteddeliverydate_new,
customerpurchaseordernumber_old,
customerpurchaseordernumber_new,
isundersupplybackordered_old,
isundersupplybackordered_new,
comments_old, comments_new,
deliveryinstructions_old, deliveryinstructions_new,
nternalcomments_old, internalcomments_new,
pickingcompletedwhen_old, pickingcompletedwhen_new,
lasteditedby_old, lasteditedby_new,
lasteditedwhen_old, lasteditedwhen_new,
actiontype, actiontime, username)
select
isnull
(inserted.orderid, deleted.orderid)
as
orderid,
-- the orderid can never change.
--this ensures we get the id correctly,
--regardless of operation type.
deleted.customerid
as
customerid_old,
inserted.customerid
as
customerid_new,
deleted.salespersonpersonid
as
salespersonpersonid_old,
inserted.salespersonpersonid
as
salespersonpersonid_new,
deleted.pickedbypersonid
as
pickedbypersonid_old,
inserted.pickedbypersonid
as
pickedbypersonid_new,
deleted.contactpersonid
as
contactpersonid_old,
inserted.contactpersonid
as
contactpersonid_new,
deleted.backorderorderid
as
backorderorderid_old,
inserted.backorderorderid
as
backorderorderid_new,
deleted.orderdate
as
orderdate_old,
inserted.orderdate
as
orderdate_new,
deleted.expecteddeliverydate
as
expecteddeliverydate_old,
inserted.expecteddeliverydate
as
expecteddeliverydate_new,
deleted.customerpurchaseordernumber
as
customerpurchaseordernumber_old,
inserted.customerpurchaseordernumber
as
customerpurchaseordernumber_new,
deleted.isundersupplybackordered
as
isundersupplybackordered_old,
inserted.isundersupplybackordered
as
isundersupplybackordered_new,
deleted.comments
as
comments_old,
inserted.comments
as
comments_new,
deleted.deliveryinstructions
as
deliveryinstructions_old,
inserted.deliveryinstructions
as
deliveryinstructions_new,
deleted.internalcomments
as
internalcomments_old,
inserted.internalcomments
as
internalcomments_new,
deleted.pickingcompletedwhen
as
pickingcompletedwhen_old,
inserted.pickingcompletedwhen
as
pickingcompletedwhen_new,
deleted.lasteditedby
as
lasteditedby_old,
inserted.lasteditedby
as
lasteditedby_new,
deleted.lasteditedwhen
as
lasteditedwhen_old,
inserted.lasteditedwhen
as
lasteditedwhen_new,
case
-- determine the operation type based on whether
--inserted exists, deleted exists, or both exist.
when
inserted.orderid
is
null
then
'delete'
when
deleted.orderid
is
null
then
'insert'
else
'update'
end
as
actiontype,
sysutcdatetime() actiontime,
suser_sname()
as
username
from
inserted
full
join
deleted
on
inserted.orderid = deleted.orderid
where
inserted.customerid <> -1
-- -1 indicates an internal/non-production
--customer that should not be audited.
or
deleted.customerid <> -1;
-- -1 indicates an internal/non-production
--customer that should not be audited.
end
|
请注意，该文档并不全面，但包含了一个简短的头，并解释了触发器内的一些tsql关键部分
。
在触发器中，update提供了判断是否将数据写入给定列的能力。这可以允许触发器检查列在执行操作之前是否发生了更改。下面是该语法的示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
create
trigger
tr_sales_orders_log_backorderid_change
on
sales.orders
after
update
as
begin
set
nocount
on
;
if
update
(backorderorderid)
begin
update
orderbackorderlog
set
backorderorderid = inserted.backorderorderid,
previousbackorderorderid = deleted.backorderorderid
from
dbo.orderbackorderlog
inner
join
inserted
on
inserted.orderid = orderbackorderlog.orderid
end
end
|
通过首先检查backorderid是否被更新，触发器可以在不需要时绕过后续操作。这是一种提高性能的好方法，它允许触发器根据所需列的更新值完全跳过代码.
columns_updated指示表中的哪些列作为写操作的一部分进行了更新，可以在触发器中使用它来快速确定指定的列是否受到插入或更新操作的影响。虽然有文档记录，但它使用起来很复杂，很难进行文档记录。我通常不建议使用它，因为它几乎肯定会使不熟悉它的开发人员感到困惑.
请注意，对于update或columns_updated，列是否更改并不重要。对列进行写操作，即使值没有改变，对于update操作仍然返回1，对于columns_updated操作仍然返回1。它们只跟踪指定的列是否是写操作的目标，而不跟踪值本身是否改变.
。
让触发代码尽可能的简单。数据库表的触发器数量增长会大大增加表的复杂性，理解其操作变得更加困难。.
例如，考虑以下表触发器定义方式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
create
trigger
tr_sales_orders_i
on
sales.orders
after
insert
create
trigger
tr_sales_orders_iu
on
sales.orders
after
insert
,
update
create
trigger
tr_sales_orders_ud
on
sales.orders
after
update
,
delete
create
trigger
tr_sales_orders_uid
on
sales.orders
after
update
,
insert
,
delete
create
trigger
tr_sales_orders_id
on
sales.orders
after
insert
,
delete
|
当插入一行时会发生什么触发器的触发顺序是什么这些问题的答案需要研究。维护更少的触发器是一个简单的解决方案，并且消除了对给定表中如何发生写操作的猜测。作为参考，可以使用系统存储过程sp_settriggerorder修改触发器顺序，不过这只适用于after触发器.
。
触发器的最佳实践是操作简单，执行迅速，并且不会因为它们的执行而触发更多的触发器。触发器的复杂程度并没有明确的规则，但有一条简单的指导原则是，理想的触发器应该足够简单，如果必须将触发器中包含的逻辑移到其他地方，那么迁移的代价不会高得令人望而却步。也就是说，如果触发器中的业务逻辑非常复杂，以至于移动它的成本太高而无法考虑，那么这些触发器很可能变得过于复杂.
使用我们前面的示例，考虑一下更改审计的触发器。这可以很容易地从触发器转移到存储过程或代码中，而这样做的工作量并不大。触发器中记录日志的方便性使它值得一做，但与此同时，我们应该知道开发人员将tsql从触发器迁移到另一个位置需要多少小时.
时间的计算可以看作是触发器的可维护性成本的一部分。也就是说，如果有必要，为摆脱触发机制而必须付出的代价。这听起来可能很抽象，但平台之间的数据库迁移是很常见的。在sql server中执行良好的一组触发器在oracle或postgresql中可能并不有效.
。
有时，一个触发器中需要临时表，以允许对数据进行多次更新。临时表存储在tempdb中，并且受到tempdb数据库大小、速度和性能约束的影响.
对于经常访问的临时表，优化表变量是在内存中(而不是在tempdb中)维护临时数据的好方法.
下面的tsql为内存优化数据配置了一个数据库(如果需要)
|
1
2
3
4
5
6
7
8
|
alter
database
wideworldimporters
set
memory_optimized_elevate_to_snapshot =
on
;
alter
database
wideworldimporters
add
filegroup wwi_inmemory_data
contains
memory_optimized_data;
alter
database
wideworldimporters
add
file
(
name
=
'wideworldimporters_imoltp_file_1'
,
filename=
'c:\sqldata\wideworldimporters_imoltp_file_1.mem'
)
to
filegroup wwi_inmemory_data;
|
一旦配置完成，就可以创建一个内存优化的表类型
|
1
2
3
4
5
6
7
8
9
|
create
type dbo.salesordermetadata
as
table
( orderid
int
not
null
primary
key
nonclustered,
customerid
int
not
null
,
salespersonpersonid
int
not
null
,
contactpersonid
int
not
null
,
index
ix_salesordermetadata_customerid nonclustered hash
(customerid)
with
(bucket_count = 1000))
with
(memory_optimized =
on
);
|
这个tsql创建了演示的触发器所需要的表
|
1
2
3
4
5
6
7
8
|
create
table
dbo.orderadjustmentlog
( orderadjustmentlog_id
int
not
null
identity(1,1)
constraint
pk_orderadjustmentlog
primary
key
clustered,
orderid
int
not
null
,
customerid
int
not
null
,
salespersonpersonid
int
not
null
,
contactpersonid
int
not
null
,
createtimeutc datetime2(3)
not
null
);
|
下面是一个使用内存优化表的触发器演示
|
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
|
create
trigger
tr_sales_orders_mem_test
on
sales.orders
after
update
as
begin
set
nocount
on
;
declare
@orderdata dbo.salesordermetadata;
insert
into
@orderdata
(orderid, customerid, salespersonpersonid,
contactpersonid)
select
orderid,
customerid,
salespersonpersonid,
contactpersonid
from
inserted;
delete
orderdata
from
@orderdata orderdata
inner
join
sales.customers
on
customers.customerid = orderdata.customerid
where
customers.isoncredithold = 0;
update
orderdata
set
contactpersonid = 1
from
@orderdata orderdata
where
orderdata.contactpersonid
is
null
;
insert
into
dbo.orderadjustmentlog
(orderid, customerid, salespersonpersonid,
contactpersonid, createtimeutc)
select
orderdata.orderid,
orderdata.customerid,
orderdata.salespersonpersonid,
orderdata.contactpersonid,
sysutcdatetime()
from
@orderdata orderdata;
end
|
触发器内需要的操作越多，节省的时间就越多，因为内存优化的表变量不需要io来读/写.
一旦读取了来自所插入表的初始数据，触发器的其余部分就可以不处理tempdb，从而减少使用标准表变量或临时表的开销.
下面的代码设置了一些测试数据，并运行一个更新来演示上述代码的结果
|
1
2
3
4
5
6
7
8
|
update
customers
set
isoncredithold = 1
from
sales.customers
where
customers.customerid = 832;
update
orders
set
salespersonpersonid = 2
from
sales.orders
where
customerid = 832;
|
一旦执行，orderadjustmentlog表的内容可以被验证
结果是意料之中的。通过减少对标准存储的依赖并将中间表移动到内存中，内存优化表提供了一种大大提高触发速度的方法。这仅限于对临时对象有大量调用的场景，但在存储过程或其他过程性tsql中也很有用.
。
像所有的工具一样，触发器也可能被滥用，并成为混乱、性能瓶颈和可维护性噩梦的根源。有许多比触发器更可取的替代方案，在实现(或添加到现有的)触发器之前应该考虑它们.
temporal tables 。
temporal tables是在sql server 2016中引入的，它提供了一种向表添加版本控制的简单方法，无需构建自己的数据结构和etl。这种记录对应用程序是不可见的，并提供了符合ansi标准的完整版本支持，使之成为一种简单的方法来解决保存旧版本数据的问题.
check约束 。
对于简单的数据验证，check约束可以提供所需的内容，而不需要函数、存储过程或触发器。在列上定义check约束，并在创建数据时自动验证数据.
下面是一个check约束的示例
|
1
2
3
4
|
alter
table
sales.invoices
with
check
add
constraint
ck_sales_invoices_returneddeliverydata_must_be_valid_json
check
([returneddeliverydata]
is
null
or
isjson([returneddeliverydata])<>(0))
|
这段代码检查一个列是否是有效的json。如果是，则执行正常进行。如果不是，那么sql server将抛出一个错误，写操作将失败。check约束可以检查列和值的任何组合，因此可以管理简单或复杂的验证任务.
创建check约束的成本不高，而且易于维护。它们也更容易记录和理解，因为check约束的范围仅限于验证传入数据和确保数据完整性，而触发器实际上可以做任何可以想象的事情.
唯一约束 。
如果一个列需要唯一的值，并且不是表上的主键，那么唯一约束是完成该任务的一种简单而有效的方法。唯一约束是索引和唯一性的组合。为了有效地验证唯一性，索引是必需的.
下面是一个唯一约束的例子
|
1
2
|
alter
table
warehouse.colors
add
constraint
uq_warehouse_colors_colorname
unique
nonclustered (colorname
asc
);
|
每当一行被插入到 warehouse.colors表中，将检查colorname的唯一性。如果写操作碰巧导致了重复的颜色，那么语句将失败，数据将不会被更改。为此目的构建了唯一约束，这是在列上强制唯一性的最简单方法.
内置的解决方案将更高效、更容易维护和更容易记录。任何看到唯一约束的开发人员都将立即理解它的作用，而不需要深入挖掘tsql来弄清事情是如何工作的，这种简单性使其成为理想的解决方案.
外键约束 。
与check约束和唯一约束一样，外键约束是在写入数据之前验证数据完整性的另一种方式。外键将一一表中的列链接到另一张表。当数据插入到目标表时，它的值将根据引用的表进行检查。如果该值存在，则写操作正常进行。如果不是，则抛出错误，语句失败.
这是一个简单的外键例子
|
1
2
3
|
alter
table
sales.orders
with
check
add
constraint
fk_sales_orders_customerid_sales_customers
foreign
key
(customerid)
references
sales.customers (customerid);
|
当数据写入sales.orders时，customerid列将根据sales.customers中的customerid列进行检查.
与唯一约束类似，外键只有一个目的:验证写入一个表的数据是否存在于另一个表中。它易于文档化，易于理解，实现效率高.
触发器不是执行这些验证检查的正确位置，与使用外键相比，它是效率较低的解决方案.
。
在触发器中实现的逻辑通常可以很容易地移动到存储过程中。这消除了大量触发代码可能导致的复杂性，同时允许开发人员更好的维护。存储过程可以自由地构造操作，以确保尽可能多的原子性.
实现触发器的基本原则之一是确保一组操作与写操作一致。所有成功或失败都是作为原子事务的一部分。应用程序并不总是需要这种级别的原子性。如果有必要，可以在存储过程中使用适当的隔离级别或表锁定来保证事务的完整性.
虽然sql server(和大多数rdbms)提供了acid保证事务将是原子的、一致的、隔离的和持久的，但我们自己代码中的事务可能需要也可能不需要遵循相同的规则。现实世界的应用程序对数据完整性的需求各不相同.
存储过程允许自定义代码，以实现应用程序所需的数据完整性，确保性能和计算资源不会浪费在不需要的数据完整性上.
例如，一个允许用户发布照片的社交媒体应用程序不太可能需要它的事务完全原子化和一致。如果我的照片出现在你之前或之后一秒，没人会在意。同样，如果你在我编辑照片的时候评论我的照片，时间对使用这些数据的人来说可能并不重要。另一方面，一个管理货币交易的银行应用程序需要确保交易是谨慎执行的，这样就不会出现资金丢失或数字报告错误的情况。如果我有一个银行账户，里面有20美元，我取出20美元的同时，其他人也取出了20美元，我们不可能都成功。我们中的一个先得到20美元，另一个遇到关于0美元余额的适当错误消息.
函数 。
函数提供了一种简单的方法，可以将重要的逻辑封装到一个单独的位置。在50个表插入中重用的单个函数比50个触发器(每个表一个触发器)执行相同逻辑要容易得多.
考虑以下函数
|
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
|
create
function
website.calculatecustomerprice
(@customerid
int
, @stockitemid
int
, @pricingdate
date
)
returns
decimal
(18,2)
with
execute
as
owner
as
begin
declare
@calculatedprice
decimal
(18,2);
declare
@unitprice
decimal
(18,2);
declare
@lowestunitprice
decimal
(18,2);
declare
@highestdiscountamount
decimal
(18,2);
declare
@highestdiscountpercentage
decimal
(18,3);
declare
@buyinggroupid
int
;
declare
@customercategoryid
int
;
declare
@discountedunitprice
decimal
(18,2);
select
@buyinggroupid = buyinggroupid,
@customercategoryid = customercategoryid
from
sales.customers
where
customerid = @customerid;
select
@unitprice = si.unitprice
from
warehouse.stockitems
as
si
where
si.stockitemid = @stockitemid;
set
@calculatedprice = @unitprice;
set
@lowestunitprice = (
select
min
(sd.unitprice)
from
sales.specialdeals
as
sd
where
((sd.stockitemid = @stockitemid)
or
(sd.stockitemid
is
null
))
and
((sd.customerid = @customerid)
or
(sd.customerid
is
null
))
and
((sd.buyinggroupid = @buyinggroupid)
or
(sd.buyinggroupid
is
null
))
and
((sd.customercategoryid = @customercategoryid)
or
(sd.customercategoryid
is
null
))
and
((sd.stockgroupid
is
null
)
or
exists (
select
1
from
warehouse.stockitemstockgroups
as
sisg
where
sisg.stockitemid = @stockitemid
and
sisg.stockgroupid = sd.stockgroupid))
and
sd.unitprice
is
not
null
and
@pricingdate
between
sd.startdate
and
sd.enddate);
if @lowestunitprice
is
not
null
and
@lowestunitprice < @unitprice
begin
set
@calculatedprice = @lowestunitprice;
end
;
set
@highestdiscountamount = (
select
max
(sd.discountamount)
from
sales.specialdeals
as
sd
where
((sd.stockitemid = @stockitemid)
or
(sd.stockitemid
is
null
))
and
((sd.customerid = @customerid)
or
(sd.customerid
is
null
))
and
((sd.buyinggroupid = @buyinggroupid)
or
(sd.buyinggroupid
is
null
))
and
((sd.customercategoryid = @customercategoryid)
or
(sd.customercategoryid
is
null
))
and
((sd.stockgroupid
is
null
)
or
exists
(
select
1
from
warehouse.stockitemstockgroups
as
sisg
where
sisg.stockitemid = @stockitemid
and
sisg.stockgroupid = sd.stockgroupid))
and
sd.discountamount
is
not
null
and
@pricingdate
between
sd.startdate
and
sd.enddate);
if @highestdiscountamount
is
not
null
and
(
@unitprice - @highestdiscountamount) < @calculatedprice
begin
set
@calculatedprice = @unitprice - @highestdiscountamount;
end
;
set
@highestdiscountpercentage = (
select
max
(sd.discountpercentage)
from
sales.specialdeals
as
sd
where
((sd.stockitemid = @stockitemid)
or
(sd.stockitemid
is
null
))
and
((sd.customerid = @customerid)
or
(sd.customerid
is
null
))
and
((sd.buyinggroupid = @buyinggroupid)
or
(sd.buyinggroupid
is
null
))
and
((sd.customercategoryid = @customercategoryid)
or
(sd.customercategoryid
is
null
))
and
((sd.stockgroupid
is
null
)
or
exists
(
select
1
from
warehouse.stockitemstockgroups
as
sisg
where
sisg.stockitemid = @stockitemid
and
sisg.stockgroupid = sd.stockgroupid))
and
sd.discountpercentage
is
not
null
and
@pricingdate
between
sd.startdate
and
sd.enddate);
if @highestdiscountpercentage
is
not
null
begin
set
@discountedunitprice = round(@unitprice *
@highestdiscountpercentage / 100.0, 2);
if @discountedunitprice < @calculatedprice
set
@calculatedprice = @discountedunitprice;
end
;
return
@calculatedprice;
end
;
|
就复杂性而言，这绝对是一头猛兽。虽然它接受标量参数来确定计算价格，但它执行的操作非常大，甚至包括对warehouse.stockitemstockgroups, warehouse.stockitems和sales.customers的额外读取。如果这是一个经常针对单行数据使用的关键计算，那么将其封装在一个函数中是获得所需计算的一种简单方法，而不会增加触发器的复杂性。小心使用函数，并确保使用大型数据集进行测试。简单的标量函数通常可以很好地伸缩性较大的数据，但更复杂的函数可能性能较差.
编码 。
当从应用程序修改表中的数据时，还可以在写入数据之前执行额外的数据操作或验证。这通常代价低廉，性能很好，并有助于减少失控触发器对数据库的负面影响.
将代码放入触发器的常见理由是，这样做可以避免修改代码、推送构建，否则会导致更改应用程序。这与在数据库中进行更改相关的任何风险直接相反。这通常是应用程序开发人员和数据库开发人员之间关于谁将负责新代码的讨论.
这是一个粗略的指导方针，但有助于在代码添加到应用程序或触发器之后测量可维护性和风险.
计算列 。
其他列发生更改时，计算列可以包括通过各种各样的算术运算和函数进行计算，得到结果。它们可以包含在索引中，也可以包含在唯一的约束中，甚至主键中.
当任何底层值发生变化时，sql server会自动维护计算的列。注意，每个计算出来的列最终都是由表中其他列的值决定的.
这是使用触发器来维护指定列值的一种很好的替代方法。计算列是高效的、自动的，并且不需要维护。它们只是简单地工作，甚至允许将复杂的计算直接集成到一个表中，而在应用程序或sql server中不需要额外的代码.
。
触发器在sql server中是一个有用的特性，但像所有工具一样，它也可能被误用或滥用。在决定是否使用触发器时，一定要考虑触发器的目的.
如果一个触发器被用来将简短的事务数据写入日志表，那么它很可能是一个很好的触发器。如果触发器被用来强制执行复杂的业务规则，那么很可能需要重新考虑处理这类操作的最佳方式.
有很多工具可以作为触发器的可行替代品，比如检查约束、计算列等，解决问题的方法并不短缺。数据库体系结构的成功在于为工作选择正确的工具.
原文链接：https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-triggers-good-scary/ 。
总结 。
到此这篇关于关于sql server触发器的文章就介绍到这了,更多相关sql server触发器内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！ 。
原文链接：https://www.cnblogs.com/hhhnicvscs/p/14444984.html 。
最后此篇关于关于喜忧参半的SQL Server触发器详解的文章就讲到这里了,如果你想了解更多关于关于喜忧参半的SQL Server触发器详解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
27
4
0
我是一名优秀的程序员,十分优秀！