SQL server使用触发器的步骤，方法，技巧

SQL server怎么使用触发器

什么是触发器

当执行一张表的插入、更新、删除时，会触发另一张表的插入、更新和删除

为什么需要触发器

对表的插入、更新和删除是敏感操作，我们希望能把这些操作的信息记录在另一张表格中，将来就能清晰的知道是做了那些操作

怎么使用触发器

第一步 确定触发器的类型

AFTER	后触发器，是在触发操作（INSERT、UPDATE或 DELETE）后激发
INSTEAD OF	替代触发器，操作（INSERT、UPDATE或 DELETE）并未真正执行

例如，有一张buy表(buyid,name,cost)，现在希望buy上插入一条数据后，在detail表(detailid,buyid,name,kind,oldcost,newcost)中新增一条相应记录

首先，我们是希望把这条记录插入buy中的，并且在这个操作之后在detail上新增一条记录，所以我们使用AFTER；其次，我们希望的是buy表上的INSERT操作，才触发detail上新增一条记录这个操作，因此可以这么表示AFTER INSERT；最后，综上所述，可以写出语句：

CREATE TRIGGER tri_buy_insert on buy
AFTER INSERT	
AS
BEGIN
detail上的新增操作...
END

现在，我们希望在buy表上创建一个删除触发器，使得在buy上试图删除一条数据时，不执行删除，而是在detail表中新增一条相应删除记录

首先，我们是不希望执行删除操作的，所以我们需要使用替代触发器INSTEAD OF；其次，我们希望的是buy表上的DELETE操作，才触发detail表上新增一条操作这个操作，因此可以这么表示INSTEAD OF DELETE；最后，综上所述，可以写出语句：

CREATE TRIGGER tri_buy_delete on buy
INSTEAD OF DELETE
AS
BEGIN
detail上的新增操作...
END

可见，创建触发器的框架基本上是一样的：

CREATE TRIGGER 1.触发器名称 on 2.表名称
3.确定触发器类型
AS
BEGIN
4.detail上的新增操作...
END

只需要把1、2、3、4各自补充完整即可

其中1、2都很简单，3中确定触发器类型的方法已说明，下面说一下4怎么确定

第二步 触发后的操作

插入操作

首先，INSERT语句的一种写法是：

INSERT INTO 1.表名称
SELECT ...
FROM 2.表名称

1.表名称指的是记录插入到那张表格中

2.表名称指的是从那张表格获取数据

还是刚才的例子，有一张buy表(buyid,name,cost)，现在希望buy上插入一条数据后，在detail表(detailid,buyid,name,kind,oldcost,newcost)中新增一条相应记录

先确定需要插入记录的表格是detail表

然后确定是从buy表刚插入的那条记录获取数据

于是，可以这么写：

INSERT INTO detail
SELECT ...
FROM buy上刚插入的记录

现在问题来了，buy上刚插入的记录怎么表示？

其实，如果我们的操作(INSERT,DELETE,UPDATE)有触发器，它会为我们创建一张临时表，其中插入操作的数据会存放在inserted记录中；而删除操作的数据会存放在deleted记录中；更新操作的数据可以看成是先做DELETE、再做INSERT，所以更新操作的旧数据存放在deleted中，新数据存放在inserted中。

于是，我们可以这么写：

INSERT INTO detail
SELECT ...
FROM insert i

接着，还有SELECT后面的语句还没写，根据detail表的字段写就行：

INSERT INTO detail(buyid,name,kind,oldcost,newcost)
SELECT i.buyid, i.name, "新增", 0, i.cost
FROM insert i --表示插入buy上的那行记录

和我们的触发器框架结合起来就是：

CREATE TRIGGER tri_buy_insert on buy
AFTER INSERT	-- 1.确定类型
AS
BEGIN
    -- 2.触发后的操作
    INSERT INTO detail(buyid,name,kind,oldcost,newcost)
    SELECT i.buyid, i.name, "新增", 0, i.cost
    FROM insert i --表示插入buy上的那行记录
END

再来看一下执行过程：

首先，当往buy表插入数据后，激发触发器，执行触发后的操作。

接着，在触发后的操作中，先执行FROM insert i，获取插入buy上的那行记录，并取了个别名i。

然后，执行SELECT i.buyid, i.name, “新增”, 0, i.cost操作，将i记录的投影构造成我们需要的样子

最后，将构造完成的这个记录插入到detail表中，由于detail中的detailid是自增的，因此不需要我们指定

删除操作

还是刚刚的第二个例子，我们希望在buy表上创建一个删除触发器，使得在buy上试图删除一条数据时，不执行删除，而是在detail表中新增一条相应删除记录。

CREATE TRIGGER tri_buy_insert on buy
INSTEAD OF DELETE	-- 1.确定类型
AS
BEGIN
    -- 2.触发后的操作
    INSERT INTO detail(buyid,name,kind,oldcost,newcost)
    SELECT d.buyid, d.name, "删除", d.cost, 0
    FROM deleted d --表示在buy表上要删除的那行记录
END

执行步骤跟插入操作差不多，首先当试图删除buy表中的一行数据时，激活触发器，执行触发后的操作。然后就是，先从FROM拿出数据，投影到SELECT中，最后插入到detail表上，最大的区别就是删除操作使用的触发器类型是INSTEAD OF触发器，它是不会真正执行删除操作的，也就是说要删除的那条记录仍然还在buy表上

更新操作

创建一个更新触发器，使得在buy上更新一条数据后，在detail表中新增一条相应记录

CREATE TRIGGER tri_buy_update on buy
AFTER UPDATE
AS
	INSERT INTO detail(buyid, name, kind, oldcost, newcost)
	SELECT d.buyid, i.name, '更新', d.cost, i.cost 
	FROM inserted i, deleted d

跟新增和删除操作差不多，很简单。

触发后的操作中的判断语句

有一张统计表calc(maxcost,mincost,totalcost,avgcost)，现在希望在buy表上创建一个插入触发器。如果clac表当前是空的，那么在buy上试图插入一条数据时，则插入一条calc上的统计记录。如果calc表当前有数据，那么在buy上试图插入一条数据时，则更新calc上的统计记录。

CREATE TRIGGER tri_buy_insert1 on buy
AFTER INSERT
AS
	IF NOT EXISTS( SELECT * FROM calc )	-- 如果calc表不存在，就新增
	BEGIN
		INSERT INTO calc 
		SELECT MAX(b.cost), MIN(b.cost), SUM(b.cost), AVG(b.cost) 
		FROM buy b
	END
	ELSE								-- 否则，即calc表存在，就更新
	BEGIN
		DELETE FROM calc
		INSERT INTO calc 
		SELECT MAX(b.cost), MIN(b.cost), SUM(b.cost), AVG(b.cost) 
		FROM buy b
	END

这个例子，与其他例子的语句有着明显的不同。

首先，这个例子有判断语句，这说明触发后的操作是运行有判断语句的，这样可以更加灵活

其次，FROM的对象不再是inserted和deleted了，这是因为聚合函数MAX、MIN等显然是必须得拿到整张buy表的数据才能得出结论，而inserted和deleted都只是表示被操作的一行而已。

最后，我们是希望calc表存在执行calc表的更新的，但实际上我们是先将其删除，再重新插入两步实现的，为什么这么做呢，这是因为UPDATE语句用不了聚合函数，我只能使用这种方法代替。

总结

本文主要分为两部分
第一部分是确定触发器的类型，写出一个触发器的基本框架，只要能默写这个框架就能轻松的写出触发器
第二部分是触发后的操作，触发后的操作可以是INSERT、DELETE、UPDATE，执行的步骤一般都是先从FROM中取出数据，然后投影到SELECT中，最后完成INSERT、UPDATE操作等等