触发器的样例，存储过程样例

 

CREATE TRIGGER

创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft® SQL Server™ 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。

语法

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}

参数

trigger_name

是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。

Table | view

是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

WITH ENCRYPTION

加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。

AFTER

指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。

如果仅指定 FOR 关键字，则 AFTER 是默认设置。

不能在视图上定义 AFTER 触发器。

INSTEAD OF

指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。

在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。

INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。

对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。

WITH APPEND

指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时，才需要使用该可选子句。如果兼容级别是 70 或更高，则不必使用 WITH APPEND 子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CREATE TRIGGER 的默认行为）。有关更多信息，请参见 sp_dbcmptlevel。

WITH APPEND 不能与 INSTEAD OF 触发器一起使用，或者，如果显式声明 AFTER 触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR（将被解释为 AFTER）。

NOT FOR REPLICATION

表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

AS

是触发器要执行的操作。

sql_statement

是触发器的条件和操作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。

当尝试 DELETE、INSERT 或 UPDATE 操作时，Transact-SQL语句中指定的触发器操作将生效。

触发器可以包含任意数量和种类的 Transact-SQL 语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的 Transact-SQL 语句常常包含控制流语言。CREATE TRIGGER 语句中使用几个特殊的表：

• deleted 和 inserted 是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，请使用：

  SELECT *
  FROM deleted
  

• 如果兼容级别等于 70，那么在 DELETE、INSERT 或 UPDATE 触发器中，SQL Server 将不允许引用 inserted 和 deleted 表中的 text、ntext 或 image 列。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。当兼容级别是 65 或更低时，对 inserted 或 deleted 表中允许空值的text、ntext 或 image 列，将返回空值；如果这些列不可为空，则返回零长度字符串。

  当兼容级别是 80 或更高时，SQL Server 允许在表或视图上通过 INSTEAD OF 触发器更新 text、ntext 或 image 列。

n

是表示触发器中可以包含多条 Transact-SQL 语句的占位符。对于 IF UPDATE (column) 语句，可以通过重复 UPDATE (column) 子句包含多列。

IF UPDATE (column)

测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。若要测试在多个列上进行的 INSERT 或 UPDATE 操作，请在第一个操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中 IF UPDATE 将返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。

说明  IF UPDATE (column) 子句的功能等同于 IF、IF...ELSE 或 WHILE 语句，并且可以使用 BEGIN...END 语句块。有关更多信息，请参见控制流语言。

 

可以在触发器主体中的任意位置使用 UPDATE (column)。

column

是要测试 INSERT 或 UPDATE 操作的列名。该列可以是 SQL Server 支持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，请参见数据类型。

IF (COLUMNS_UPDATED())

测试是否插入或更新了提及的列，仅用于 INSERT 或 UPDATE 触发器中。COLUMNS_UPDATED 返回 varbinary 位模式，表示插入或更新了表中的哪些列。

COLUMNS_UPDATED 函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含 8 列以上，则 COLUMNS_UPDATED 返回多个字节，最左边的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。

可以在触发器主体中的任意位置使用 COLUMNS_UPDATED。

bitwise_operator

是用于比较运算的位运算符。

updated_bitmask

是整型位掩码，表示实际更新或插入的列。例如，表 t1 包含列 C1、C2、C3、C4 和 C5。假定表 t1 上有 UPDATE 触发器，若要检查列 C2、C3 和 C4 是否都有更新，指定值 14；若要检查是否只有列 C2 有更新，指定值 2。

comparison_operator

是比较运算符。使用等号 (=) 检查 updated_bitmask 中指定的所有列是否都实际进行了更新。使用大于号 (>) 检查 updated_bitmask 中指定的任一列或某些列是否已更新。

column_bitmask

是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。

 

--当在“出货表_明细”增、删、改时，相应地在“订单表_明细”对已交货数量和未交货数量作出修改。
Create trigger tr_出货表_明细
on 出货表_明细
For DELETE,INSERT,UPDATE
as
Declare @JiaoHuo varchar(20)
Begin
 Set @JiaoHuo = (Select Sum(交货数量) from 出货表_明细 Group by 序号 Having 序号 = (Select 序号 From Deleted))
 Set @JiaoHuo = ISNULL(@JiaoHuo,(Select Sum(交货数量) from 出货表_明细 Group by 序号 Having 序号 = (Select 序号 From Inserted)))
        Set @JiaoHuo = ISNULL(@JiaoHuo,0)
 UPDATE N SET 已交货数量 = @JiaoHuo, 未交货数量 = N.订购数量 - @JiaoHuo
                From 订单表_明细 AS N INNER Join Deleted AS D
      ON N.序号 = D.序号
End


--当“订单表_明细”中增、删、改时、判断该笔订单的未交货数量合计是否为0，修改“订单表”的订单状态为“已完成”，否则为“未完成”
Create trigger tr_Status
ON 订单表_明细
FOR INSERT,UPDATE,DELETE
AS
Declare @Order varchar(20)
 Begin
         Set @Order = (Select 订单号 from Deleted)
         Set @Order = ISNULL(@Order,(Select 订单号 from Inserted))
         IF(Select Sum(未交货数量) From 订单表_明细 Group by 订单号 Having 订单号 = @Order)= 0 
              Begin 
                   UPDATE 订单表 Set 订单状态 = '已完成',完成日期 = GetDate() Where 订单号 = @Order
              End
         Else
              Begin  
                   UPDATE 订单表 Set 订单状态 = '未完成',完成日期 = NUll Where 订单号 = @Order
              End
 End


======================================================================
--出货表_明细
CREATE TABLE [出货表_明细] (
 [序号] [int] NULL ,
 [订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [出货单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [料号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [品名_1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [品名_2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [批号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [单位] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [规格型号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [交货数量] [int] NULL ,
 [交货情况] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [客户要求交货期] [datetime] NULL ,
 [预交日期] [datetime] NULL ,
 [交货日期] [datetime] NULL ,
 [sysRowInfo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 CONSTRAINT [PK_出货表_明细] PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_出货表_出货表_明细] FOREIGN KEY
 (
  [出货单号]
 ) REFERENCES [出货表] (
  [出货单号]
 ) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


--订单表
CREATE TABLE [订单表] (
 [订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [客户编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [客户名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [下单日期] [datetime] NULL ,
 [签收日期] [datetime] NULL ,
 [接单业务员] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [客户要求完成日期] [datetime] NULL ,
 [完成日期] [datetime] NULL ,
 [订单状态] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [总金额] [money] NULL ,
 [出货人] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [收货地址] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [收货人] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [备注] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
 [sysRowInfo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 CONSTRAINT [PK_订单表] PRIMARY KEY  CLUSTERED
 (
  [订单号]
 )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--订单表_明细
CREATE TABLE [订单表_明细] (
 [序号] [int] IDENTITY (1, 1) NOT NULL ,
 [订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [料号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [品名_1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [品名_2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [批号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [单位] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [规格型号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [订购数量] [int] NULL ,
 [已交货数量] [int] NULL ,
 [未交货数量] [int] NULL ,
 [单价] [money] NULL ,
 [合计金额] [money] NULL ,
 [客户要求交货期] [datetime] NULL ,
 [预交日期] [datetime] NULL ,
 [sysRowInfo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 CONSTRAINT [PK_订单表_明细] PRIMARY KEY  CLUSTERED
 (
  [序号]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_订单表_订单表_明细] FOREIGN KEY
 (
  [订单号]
 ) REFERENCES [订单表] (
  [订单号]
 ) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE PROCEDURE dbo.p_my_DocumentAdd
(
  @DocumentID AS char(10) out,
  @Title AS VarChar(100),
  @Comment AS Text,
  @Hits AS Int,
  @Type AS VarChar(20),
  @Gender AS TinyInt
)
AS
BEGIN
  BEGIN TRAN--开始事务
        --生成主键
        exec p_my_GetTableNextID 'Document','D',10,@DocumentID output
        INSERT INTO Document (
        DocumentID,
        Title,
        Comment,
        Hits,
        Type,
        Gender)
    VALUES(
        @DocumentID,
        @Title,
        @Comment,
        @Hits,
        @Type,
        @Gender)
    --出错处理
    IF (@@error!=0)
        BEGIN
            RAISERROR 20000 'p_my_DocumentAdd: Cannot insert data into  p_my_DocumentAdd '--向应用程序抛出错误
            ROLLBACK TRAN--回滚事务
            Return (1)--设置返回值
        End
    COMMIT TRAN--如果没有错误则提交事务
END