触发器 -- SQL Server

Sudipta Chaudhari

4.84/5 (237投票s)

2008年4月26日

CPOL

4分钟阅读

1760585

本文简要介绍了 SQL Server 2000/2005 中的触发器。

引言

本文简要介绍了 SQL Server 2000/2005 中的触发器。

什么是触发器

触发器是一种特殊的存储过程，它响应对表的操作（如插入、删除或更新数据）而执行。它是一个数据库对象，绑定到表上并自动执行。您无法显式调用触发器。唯一的方法是通过在它们所属的表上执行所需的操作来触发它们。

触发器类型

SQL 中有三种您会用到的操作查询类型：INSERT、UPDATE 和 DELETE。因此，有三种类型的触发器，以及通过混合和匹配事件和触发它们的时机而产生的混合触发器。基本上，触发器分为两大类：

After Triggers (For Triggers)
Instead Of Triggers

(i) After Triggers (后置触发器)

这些触发器在对表执行插入、更新或删除操作之后运行。它们**不支持视图**。
AFTER TRIGGERS (后置触发器) 可进一步分为三种类型：

AFTER INSERT Trigger (插入后触发器)
AFTER UPDATE Trigger (更新后触发器)
AFTER DELETE Trigger (删除后触发器)

让我们创建 After Triggers。首先，让我们创建一个表并插入一些示例数据。然后，我将把几个触发器附加到此表上。

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

我将创建一个 AFTER INSERT TRIGGER，它将插入到表中的行插入到另一个审计表中。此审计表的主要目的是记录主表中的更改。这可以看作是一个通用的审计触发器。

现在，创建审计表，如下所示：

CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

(a) After Insert Trigger (插入后触发器)

此触发器在对表执行 INSERT 操作后触发。让我们创建触发器，如下所示：

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);

	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	set @audit_action='Inserted Record -- After Insert Trigger.';

	insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());

	PRINT 'AFTER INSERT trigger fired.'
GO

CREATE TRIGGER 语句用于创建触发器。ON 子句指定要附加触发器的表名。FOR INSERT 指定这是一个 AFTER INSERT 触发器。可以使用 AFTER INSERT 替换 FOR INSERT。它们的意思相同。

在触发器正文中，使用了名为 inserted 的表。这是一个逻辑表，包含已插入的行。我已从逻辑 inserted 表中选择字段，并将已插入行的值赋给不同的变量，最后将这些值插入到 Audit 表中。

要查看新创建的触发器如何工作，让我们向主表中插入一行，如下所示：

 insert into Employee_Test values('Chris',1500);

现在，已将一条记录插入到 Employee_Test 表中。附加到此表的 AFTER INSERT 触发器已将记录插入到 Employee_Test_Audit 中，如下所示：

6   Chris  1500.00   Inserted Record -- After Insert Trigger.	2008-04-26 12:00:55.700

(b) AFTER UPDATE Trigger (更新后触发器)

此触发器在对表执行 update 操作后触发。让我们创建触发器，如下所示：

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);

	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	
	if update(Emp_Name)
		set @audit_action='Updated Record -- After Update Trigger.';
	if update(Emp_Sal)
		set @audit_action='Updated Record -- After Update Trigger.';

	insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());

	PRINT 'AFTER UPDATE Trigger fired.'
GO

创建了 AFTER UPDATE Trigger，它将更新的记录插入到 audit 表中。**没有像逻辑表 inserted 那样的逻辑表 updated**。我们可以通过 update(column_name) 函数获取字段的更新值。在我们的触发器中，我们使用了 if update(Emp_Name) 来检查 Emp_Name 列是否已被更新。我们同样检查了 Emp_Sal 列是否已被更新。

让我们更新一条记录的列，看看会发生什么。

 update Employee_Test set Emp_Sal=1550 where Emp_ID=6

这会将行插入到审计表中，如下所示：

6  Chris  1550.00  Updated Record -- After Update Trigger.	  2008-04-26 12:38:11.843

(c) AFTER DELETE Trigger (删除后触发器)

此触发器在对表执行 delete 操作后触发。让我们创建触发器，如下所示：

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);

	select @empid=d.Emp_ID from deleted d;	
	select @empname=d.Emp_Name from deleted d;	
	select @empsal=d.Emp_Sal from deleted d;	
	set @audit_action='Deleted -- After Delete Trigger.';

	insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());

	PRINT 'AFTER DELETE TRIGGER fired.'
GO

在此触发器中，已删除记录的数据从**逻辑 deleted 表**中获取，并插入到 audit 表中。让我们对主表执行 delete 操作。已将一条记录插入到 audit 表中，如下所示：

 6  Chris	1550.00  Deleted -- After Delete Trigger.  2008-04-26 12:52:13.867

可以使用以下语句在表上启用/禁用所有触发器：

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

可以按如下方式启用或禁用特定触发器：

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

这将禁用指定表上名为 trgAfterDelete 的 After Delete Trigger。

(ii) Instead Of Triggers (代替触发器)

这些触发器可以用作对任何人尝试对我们的表或视图执行的任何操作的拦截器。如果您为表定义了一个 Delete 操作的 Instead Of trigger，那么当他们尝试删除行时，这些行实际上不会被删除（除非您从触发器内部发出另一个 delete 指令）。

INSTEAD OF TRIGGERS (代替触发器) 可进一步分为三种类型：

INSTEAD OF INSERT Trigger (代替插入触发器)
INSTEAD OF UPDATE Trigger (代替更新触发器)
INSTEAD OF DELETE Trigger (代替删除触发器)

让我们创建一个 Instead Of Delete Trigger，如下所示：

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
	declare @emp_id int;
	declare @emp_name varchar(100);
	declare @emp_sal int;
	
	select @emp_id=d.Emp_ID from deleted d;
	select @emp_name=d.Emp_Name from deleted d;
	select @emp_sal=d.Emp_Sal from deleted d;

	BEGIN
		if(@emp_sal>1200)
		begin
			RAISERROR('Cannot delete where salary > 1200',16,1);
			ROLLBACK;
		end
		else
		begin
			delete from Employee_Test where Emp_ID=@emp_id;
			COMMIT;
			insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,
                                                        Audit_Timestamp)
			values(@emp_id,@emp_name,@emp_sal,
                               'Deleted -- Instead Of Delete Trigger.',getdate());
			PRINT 'Record Deleted -- Instead Of Delete Trigger.'
		end
	END
GO

此触发器将阻止删除表中 Emp_Sal > 1200 的记录。如果尝试删除此类记录，Instead Of Trigger 将回滚事务，否则事务将提交。现在，让我们尝试删除 Emp_Sal >1200 的记录，如下所示：

delete from Employee_Test where Emp_ID=4

这将打印出 RAISE ERROR 语句中定义的错误消息，如下所示：

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200

并且此记录不会被删除。

同样，您也可以在表上编写 Instead Of Insert 和 Instead Of Update 触发器。

结论

在本文中，我简要介绍了触发器，解释了各种触发器类型——After Triggers 和 Instead Of Triggers 及其变体，并说明了它们各自的工作方式。希望您能清楚地理解 SQL Server 中的触发器及其用法。