sql-触发器

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。

DML触发器分为：

    1、 after触发器（之后触发）

        a、 insert触发器

        b、 update触发器

        c、 delete触发器

    2、 instead of 触发器 （之前触发）

 其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。

    触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

例：

最近碰到的一个需求，信息表中存储的是通知信息，发送者、接收者、信息是否已读等字段，另一张表有用户id、未读信息条数，信息表的变动，动态同步另一张表中的未读信息总数。

表结构如下：

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[Message_Trigger]
ON [dbo].[DMessage]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN

SET NOCOUNT ON;
DECLARE
@IsInsert bit,
@IsUpdate bit,
@IsDelete bit

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
SET @IsInsert = 1
ELSE
SET @IsInsert = 0

IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsUpdate = 1
ELSE
SET @IsUpdate = 0

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsDelete = 1
ELSE
SET @IsDelete = 0

if(@IsUpdate=1 OR @IsInsert=1)
BEGIN
UPDATE MI SET MI.UnreadMessageCount=ISNULL(COU1,0) FROM MerchantInfo MI LEFT JOIN
(SELECT M.ReserviceId, COUNT(*) AS COU1 FROM DMessage M LEFT JOIN inserted ON M.id=inserted.id
WHERE M.IsRead=0
GROUP BY M.ReserviceId) A ON MI.Mid=A.ReserviceId
END

IF(@IsDelete=1)
BEGIN
UPDATE MI SET MI.UnreadMessageCount=ISNULL(COU1,0) FROM MerchantInfo MI LEFT JOIN
(SELECT M.ReserviceId, COUNT(*) AS COU1 FROM DMessage M LEFT JOIN deleted ON M.id=deleted.id
WHERE M.IsRead=0
GROUP BY M.ReserviceId) A ON MI.Mid=A.ReserviceId
END
END

GO

sql-触发器

标签：