65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 2000 中的触发器简介

SquaredRomi
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.15/5 (36投票s)

2004年6月5日

6分钟阅读

viewsIcon

289827

触发器是一个数据库对象，它绑定到一个表。在许多方面，它与存储过程非常相似。事实上，触发器通常被称为“一种特殊的存储过程”。

引言

触发器是一个数据库对象，它绑定到一个表。在许多方面，它与存储过程非常相似。事实上，触发器通常被称为“一种特殊的存储过程”。

何时使用触发器

使用触发器有很多原因。如果您有一个表用于记录消息日志，当消息紧急时，您可能希望将它们复制一份发送给您。如果没有触发器，您也会有一些解决方案，但它们不如触发器优雅。您可以修改记录消息的应用程序。这意味着您可能会在每个记录消息的应用程序中冗余地编写相同的内容。

表可以有多个触发器。CREATE TRIGGER 语句可以定义 FOR UPDATE、FOR INSERT 或 FOR DELETE 子句，将触发器定向到特定类型的数据修改操作。当指定 FOR UPDATE 时，可以使用 IF UPDATE (column_name) 子句将触发器定向到影响特定列的更新。

SQL Server 2000 大大增强了触发器的功能，扩展了您已经熟悉和喜爱的触发器的能力，并增加了一种全新的触发器，“Instead Of”触发器。

SQL Server 2000 有许多类型的触发器

  1. After 触发器
  2. 多个 After 触发器
  3. Instead Of 触发器
  4. 混合触发器类型

After 触发器

可以以多种方式使用在 update、insert 或 delete 之后运行的触发器。

  • 触发器可以更新、插入或删除同一表或其他表中的数据。这对于维护数据之间的关系或保留审计跟踪信息非常有用。
  • 触发器可以根据表中其余数据或其它表中的数据值来检查数据。当您无法使用 RI 约束或 CHECK 约束，因为它们引用了此表或其它表的其它行的某些数据时，这很有用。
  • 触发器可以使用用户定义函数来激活非数据库操作。例如，这对于发出警报或更新数据库外部的信息很有用。

注意：AFTER 触发器只能在表上创建，不能在视图上创建。

如何创建 After 触发器

  1. 使用 INSERT 触发器
    INSERT INTO Customers 
VALUES (‘Mayank’,’Gupta’,’Hauz Khas’,’Delhi’,
              ’Delhi’,’110016’,’01126853138’)
INSERT INTO Customers 
VALUES(‘Himanshu’,’Khatri’,’ShahjahanMahal ’,
  ’Jaipur’,’Rajesthan’,’326541’,’9412658745’)
INSERT INTO Customers 
VALUES (‘Sarfaraz’,’Khan’,’Green Market’,
   ’Hydrabad’,’AP’,’698542’,’9865478521’)
 
INSERT INTO Products
VALUES (‘ASP.Net Microsoft Press’,550)
INSERT INTO Products
VALUES (‘ASP.Net Wrox Publication’,435)
INSERT INTO Products
VALUES (‘ASP.Net Unleased’,320)
INSERT INTO Products
VALUES (‘ASP.Net aPress’,450)
 
CREATE TRIGGER invUpdate ON [Orders]
FOR INSERT
AS
UPDATE p SET p.instock=[p.instock – i.qty]
FROM products p JOIN inserted I ON p.prodid = i.prodid

    您创建了一个引用了逻辑插入表（inserted table）的 INSERT 触发器。现在，每当您在 Orders 表中插入新记录时，Products 表中的相应记录将被更新，从 Products 表的 instack 列中的可用数量中减去订单的数量。

  2. 使用 DELETE 触发器

    DELETE 触发器用于限制用户从数据库中删除数据。例如：

    CREATE TRIGGER DelhiDel ON [Customers]
FOR DELETE
AS
IF (SELECT state FROM deleted) = ‘Delhi’
BEGIN
PRINT ‘Can not remove customers from Delhi’
PRINT ‘Transaction has been canceled’
ROOLBACK
END

    DELETE 触发器使用逻辑删除表（deleted table）来确保您没有尝试从“德里”这个伟大的州删除客户 - 如果您试图删除这样的客户，您将收到一个错误消息（由触发器代码中的 PRINT 语句生成），其中包含 Mayank。

  3. 使用 UPDATE 触发器

    UPDATE 触发器用于限制用户发出的 UPDATE 语句，或支持您之前的数据。

    CREATE TRIGGER CheckStock ON [Products]
FOR UPDATE
AS
IF (SELECT InStock FROM inserted) < 0
BEGIN
PRINT ‘Cannot oversell Products’
PRINT ‘Transaction has been cancelled’
ROLLBACK
END

    您创建了一个引用了插入表（inserted table）的 UPDATE 触发器，以验证您没有尝试插入小于零的值。您只需要检查插入表，因为 SQL Server 在插入您的数据之前会执行任何必要的数学函数。

多个 After 触发器

现在，对于每个 Insert/Update/Delete，可以在一个表上定义多个触发器。虽然通常您可能不希望这样做（过度使用触发器很容易导致混淆），但在某些情况下这是理想的。一个想到的例子是，您可以将触发器分为两类：

  • 基于应用程序的触发器（例如，级联删除或验证）。
  • 审计触发器（用于记录对关键数据更改的详细信息）。

这将使您能够更改某一类触发器，而不必担心意外破坏另一类。

如果您使用多个触发器，了解它们的执行顺序至关重要。一个新的名为 sp_settriggerorder 的存储过程允许您将一个触发器设置为“第一个”或“最后一个”执行。

如果您希望多个触发器按特定顺序执行，则没有办法明确定义。我进行的一项极不科学的测试表明，同一个表和操作的多个触发器将按照它们创建的顺序运行，除非您明确指定了它们。但我不会建议依赖这一点。

Instead Of 触发器

Instead Of 触发器在触发触发器的操作之前执行，所以如果您在表上为 Delete 操作定义了一个 Instead Of 触发器，并且用户尝试删除行，那么这些行实际上不会被删除（除非您在触发器内部发出另一个 delete 指令），如下面的简单示例所示。

CREATE TABLE Mayank (Name  varchar(32))
GO
 
CREATE TRIGGER tr_mayank ON Mayank 
INSTEAD OF DELETE
AS
    PRINT 'Sorry - you cannot delete this data'
GO
 
INSERT Mayank
    SELECT 'Cannot' union
    SELECT 'Delete' union
    SELECT 'Me'
GO
 
DELETE Mayank
GO
 
SELECT * FROM Mayank
GO
DROP TABLE Mayank

如果您从 Instead Of 触发器内部打印出插入表（inserted table）和删除表（deleted table）的内容，您会发现它们的行为与普通触发器完全相同。在这种情况下，删除表（deleted table）包含了您试图删除的行，尽管它们不会被删除。

Instead of 触发器可以以一些非常强大的方式使用！

  • 您可以在视图上定义 Instead Of 触发器（After 触发器无法执行此操作），这是用于将数据分散到 SQL Server 集群中的分布式分区视图的基础。
  • 您可以使用 Instead Of 触发器来简化应用程序开发人员更新多个表的过程。
  • 混合触发器类型。

如果您为同一个操作在同一个表上定义了一个 Instead Of 触发器和一个 After 触发器，会发生什么？

由于 After 触发器在操作完成后执行，而 'instead of' 触发器会阻止操作发生，所以在这种情况下 After 触发器永远不会执行。

然而，如果一个（例如）delete 操作的 Instead Of 触发器包含对同一表上的后续 delete 操作，那么对于该表的 delete 操作定义的任何 After 触发器都将基于 Instead Of 触发器中发出的 delete 语句执行。原始的 delete 语句不会执行，只有 Instead Of 触发器中的 Delete 会执行。

此代码示例创建了每种类型的触发器，并更改了发出的 delete 语句的性质，以便只有 Preserve 列值为 0 的漫画可以被删除。

CREATE TABLE Gupta (Comic VARCHAR (32), Preserve INT)
GO
 
INSERT Gupta
    SELECT 'groucho', 1 UNION
    SELECT 'chico', 1 UNION
    SELECT 'harpo', 0 UNION
    SELECT 'zeppo', 0
GO
 
CREATE TRIGGER trGuptaDelete ON Gupta 
FOR DELETE
AS
    SELECT Comic AS "deleting_these_names_only"
    FROM deleted
GO
 
CREATE TRIGGER tr_Gupta_InsteadOf ON Gupta
INSTEAD OF DELETE
AS
    DELETE Gupta
    FROM Gupta
    INNER JOIN Deleted
    ON Gupta.Comic = Deleted.Comic
    WHERE Gupta.Preserve= 0
GO
 
DELETE Gupta WHERE Comic IN ('GROUCHO', 'HARPO')
GO
 
SELECT * FROM Gupta
 
DROP TABLE Gupta

重要

触发器可用于以下场景，例如：如果数据库反规范化并需要一种自动方式来更新多个表中包含的冗余数据，或者如果需要自定义消息和复杂的错误处理，或者如果一个表中的值必须与另一个表中的非相同值进行验证。

触发器是一个强大的工具，可以在数据修改时自动强制执行业务规则。触发器也可用于维护数据完整性。但它们不是用来维护数据完整性的。只有当您无法使用 CONSTRAINTS、RULES 和 DEFAULTS 来强制执行数据完整性时，才应使用触发器来维护数据完整性。不能在临时表上创建触发器。

© . All rights reserved.