在 SQL Server 2000 中实现触发器
3.50/5 (2投票s)
在 SQL Server 2000 中实现触发器。
引言
触发器是一种特殊的存储过程，用于在数据修改之前或之后自动执行。它们可以自动在 INSERT、DELETE 和 UPDATE 触发操作时执行。
Microsoft SQL Server 2000 中有两种不同类型的触发器：INSTEAD OF 触发器和 AFTER 触发器。这两种触发器在目的和执行时间上有所不同。在本文中，我们将讨论每种触发器类型。
首先，让我们使用以下脚本创建一个包含一些表和一些示例数据的示例数据库。
Create Database KDMNN
GO
USE KDMNN
GO
CREATE TABLE [dbo].[User_Details] (
[UserID] [int] NULL ,
[FName] [varchar] (50) NOT NULL ,
[MName] [varchar] (50) NULL ,
[LName] [varchar] (50) NOT NULL ,
[Email] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[User_Master] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) NULL ,
[Password] [varchar] (50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User_Master] WITH NOCHECK ADD
CONSTRAINT [PK_User_Master] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User_Details] ADD
CONSTRAINT [FK_User_Details_User_Master] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[User_Master] (
[UserID]
)
GO
INSERT INTO USER_MASTER(USERNAME, PASSWORD)
SELECT 'Navneeth','Navneeth' UNION
SELECT 'Amol','Amol' UNION
SELECT 'Anil','Anil' UNION
SELECT 'Murthy','Murthy'
INSERT INTO USER_DETAILS(USERID, FNAME, LNAME, EMAIL)
SELECT 1,'Navneeth','Naik','navneeth@kdmnn.com' UNION
SELECT 2,'Amol','Kulkarni','amol@kdmnn.com' UNION
SELECT 3,'Anil','Bahirat','anil@kdmnn.com' UNION
SELECT 4,'Murthy','Belluri','murthy@kdmnn.com'
AFTER 触发器
在触发它的语句完成之后自动执行的触发器类型称为 AFTER 触发器。AFTER 触发器是在事务提交或回滚之前自动执行的触发器。
使用以下脚本，首先，我们将在 USER_MASTER 表上为该表的 INSERT 事件创建一个触发器。
USE KDMNN
Go
CREATE TRIGGER trgInsert
ON User_Master
FOR INSERT
AS
Print ('AFTER Trigger [trgInsert] – Trigger executed !!')
GO
BEGIN TRANSACTION
DECLARE @ERR INT
INSERT INTO USER_MASTER(USERNAME, PASSWORD)
VALUES('Damerla','Damerla')
SET @ERR = @@Error
IF @ERR = 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END
输出
AFTER Trigger [trgInsert] – Trigger executed !!
(1 row(s) affected)
回滚事务
从输出中，我们可以得出结论，在事务回滚或提交之前，AFTER 触发器会自动执行。一个表可以为每个触发操作（即 INSERT、DELETE 和 UPDATE）拥有多个 AFTER 触发器。使用以下脚本，我们将在 User_Master 表上为 INSERT 触发操作创建两个触发器。
CREATE TRIGGER trgInsert2
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert2] – Trigger executed !!')
END
GO
CREATE TRIGGER trgInsert3
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert3] – Trigger executed !!')
END
GO
BEGIN TRANSACTION
DECLARE @ERR INT
INSERT INTO USER_MASTER(USERNAME, PASSWORD)
VALUES('Damerla','Damerla')
SET @ERR = @@Error
IF @ERR = 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END
输出
AFTER Trigger [trgInsert] – Trigger executed !!
AFTER Trigger [trgInsert2] – Trigger executed !!
AFTER Trigger [trgInsert3] – Trigger executed !!
(1 row(s) affected)
回滚事务
从输出中，我们可以得出结论，当用户尝试将数据插入 USER_MASTER 表时，会自动执行三个触发器。也就是说，您可以为三个触发操作中的每一个，在一个表上编写多个 AFTER 触发器。
同样，我们可以为 DELETE 和 UPDATE 触发操作编写多个 AFTER 触发器。
注意：如果一个表有多个 AFTER 触发器，您可以使用存储过程 sp_settriggerorder 来指定哪个触发器应首先执行，哪个触发器应最后执行。所有其他触发器都处于无法控制的未定义顺序。
AFTER 触发器只能在表上创建，不能在视图上创建。使用以下脚本，首先，我们将创建一个简单的视图 [vwUserMaster]，它将从 USER_MASTER 表中提取用户名和密码。
Create View vwUserMaster
as
SELECT USERNAME, PASSWORD FROM USER_MASTER
GO
CREATE TRIGGER trgOnView
ON vwUserMaster
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgOnView] – vwUserMaster !!')
END
GO
输出
Server: Msg 208, Level 16, State 4, Procedure trgOnView,
Line 2 Invalid object name 'vwUserMaster'.
从输出中，我们可以得出结论，我们无法在视图上创建 AFTER 触发器。
与存储过程和视图一样，触发器也可以被加密。然后，触发器定义将以不可读的形式存储。一旦加密，触发器的定义就无法解密，任何人都无法查看，包括触发器所有者或系统管理员。
CREATE TRIGGER trgEncrypted
ON User_Master WITH ENCRYPTION
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!')
END
GO
SELECT
sysobjects.name AS [Trigger Name],
SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
syscomments.encrypted AS [IsEncrpted]
FROM
sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
(sysobjects.xtype = 'TR')
输出
| 触发器名称 | 触发器定义 | 表名 | 是否加密 |
|---|---|---|---|
| trgInsert | CREATE TRIGGER trgInsert | User_Master | 0 |
| trgInsert1 | CREATE TRIGGER trgInsert1 | User_Master | 0 |
| trgInsert2 | CREATE TRIGGER trgInsert2 | User_Master | 0 |
| trgEncrypted | ??????????????? | User_Master | 1 |
由于触发器 trgEncrypted 是使用 WITH ENCRYPTION 选项创建的，因此触发器定义被隐藏了，我们无法轻松解密触发器代码。
我们都知道 DML 语句会更改或修改数据。有时，触发器需要能够访问 DML 语句所引起的更改。SQL Server 2000 提供了四种不同的方法来确定 DML 语句的效果。INSERTED 和 DELETED 表（通常称为 MAGIC TABLES）以及 update () 和 columns_updated() 函数可用于确定 DML 语句所引起的更改。
下表显示了三种不同表事件的 INSERTED 和 DELETED 表的内容。
| 事件 | INSERTED | DELETED |
Insert |
包含插入的行 | Empty |
删除 |
Empty | 包含要删除的行 |
更新 |
包含更新后的行 | 包含更新前的行 |
请注意，Magic Tables 不包含数据类型为 text、ntext 或 image 的列的信息。尝试访问这些列将导致错误。
update() 函数用于查找特定列是否已被更新。此函数通常用于数据检查。
CREATE TRIGGER trgUddate
ON User_Details
FOR UPDATE
AS
If UPDATE(FName)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - First Name has been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(LName)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Last Name has been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(MName)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - MName Name has been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(Email)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Email has been updated')
ROLLBACK TRANSACTION
END
GO
UPDATE User_Details
SET MName = 'Diwaker'
WHERE UserID = 1
输出
AFTER Trigger [trgUddate] - Executed - MName Name has been updated
根据更新的列，将显示一条消息。通过此功能，我们可以确定表中哪个列已被更新，然后继续进一步实现业务规则。
Columns_Update() 函数返回一个 varbinary 数据类型的表示，表示已更新的列。此函数返回一个十六进制值，我们可以从中确定表中哪些列已被更新。
INSTEAD OF 触发器
在触发操作（即 INSERT、DELETE 和 UPDATE）的替代位置自动执行的触发器称为 INSTEAD OF 触发器。
INSTEAD OF 触发器在检查主键和外键约束之前自动执行，而传统的 AFTER 触发器在检查这些约束之后自动执行。
CREATE TRIGGER trgAfterInsert
On User_Details
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgAfterInsert] – Trigger executed !!')
END
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')
输出
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_User_Details_User_Master'.
The conflict occurred in database 'KDMNN', table 'User_Master', column 'UserID'.
The statement has been terminated.
UserID 100 不存在于 User_Master 表中，因此已检查外键约束并显示错误消息。我们可以得出结论：AFTER 触发器在 PK 和 FK 约束之后自动执行。
Create Trigger trgInsteadInsert
On User_Details
INSTEAD OF INSERT
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!')
END
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')
输出
INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!
(1 row(s) affected)
即使 UserID 100 不存在于 User_Master 表中，触发器也会自动执行。
与 AFTER 触发器不同，INSTEAD OF 触发器可以创建在视图上。
Create trigger trgOnView
on vwUserMaster
INSTEAD OF INSERT
AS
begin
Print ('INSTEAD OF Trigger [trgOnView] – vwUserMaster !!!')
End
INSERT INTO VWUSERMASTER(USERNAME, PASSWORD)
VALUES('Damerla','Venkat')
输出
INSTEAD OF Trigger [trgOnView] – vwUserMaster !!
(1 row(s) affected)
因此，每当用户尝试将数据插入视图 vwUserMaster 时，INSTEAD OF 触发器 trgOnView 都会自动执行。在 SQL Server 2000 中，可以使用视图来 INSERT/DELETE 和 UPDATE 多个表中的数据，这可以通过 INSTEAD OF 触发器来实现。
CREATE VIEW vwUser
AS
SELECT
[User_Master].[Username],
[User_Master].[Password],
[User_Details].[FName],
[User_Details].[MName],
[User_Details].[LName],
[User_Details].[Email]
FROM
[User_Master], [User_Details]
WHERE
[User_Master].[UserID]=[User_Details].[UserID]
CREATE TRIGGER tgrInsertData
ON vwUser
INSTEAD OF INSERT
AS
BEGIN
Declare @UserName varchar(50)
Declare @Password varchar(50)
Declare @FName varchar(50)
Declare @MName varchar(50)
Declare @LName varchar(50)
Declare @Email varchar(50)
SELECT
@UserName = UserName,
@Password = Password,
@FName = FName,
@MName = MName,
@LName = LName,
@Email = Email
FROM INSERTED
INSERT INTO User_Master(UserName, Password)
VALUES(@UserName, @Password)
INSERT INTO User_Details(UserID,FName,LName,MName,Email)
VALUES(@@Identity, @FName, @LName, @MName, @Email)
END
INSERT INTO vwUser(UserName,Password,FName,LName,MName,Email)
VALUES ('Dhananjay','Dhananjay','Dhananjay','Nagesh',NULL,
'Dhananjay@kdmnn.com'
输出
AFTER Trigger [trgInsert] – Trigger executed !!
AFTER Trigger [trgInsert2] – Trigger executed !!
AFTER Trigger [trgInsert3] – Trigger executed !!
AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!
(1 row(s) affected)
AFTER Trigger [trgAfterInsert] – Trigger executed !!
(1 row(s) affected)
检查以下表中的数据：User_Master 和 User_Details。新行同时插入到这两个表中。视图或表对于每个 INSERT、UPDATE 和 DELETE 事件只能有一个 INSTEAD OF 触发器。我们已经看到，您可以为同一事件在表上创建任意数量的 AFTER 触发器，而 INSTEAD OF 触发器则不能。
CREATE TRIGGER trgInsteadOfTrigger1
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadOfTrigger1] – Trigger executed !!')
END
CREATE TRIGGER trgInsteadOfTrigger2
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadOfTrigger2] – Trigger executed !!')
END
输出
Server: Msg 2111, Level 16, State 1, Procedure trgInsteadOfTrigger2, Line 6
Cannot CREATE trigger 'trgInsteadOfTrigger2' for view 'vwUserMaster'
because an INSTEAD OF UPDATE trigger already exists.
从输出中可以清楚地看出，您不能为同一事件在视图/表上创建两个 INSTEAD OF 触发器。注意：需要注意的一个重要点是，对于具有相应 ON DELETE 或 ON UPDATE 级联引用完整性定义的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。
最后，您将如何知道与表关联的触发器是什么，以及它们的类型是什么？是 AFTER 还是 INSTEAD OF？这个问题的解决方案是 sp_helptrigger。此存储过程提供了有关触发器的所有信息，例如触发器执行的事件、触发器类型等。
Sp_helptrigger User_Master
输出
| trigger_name | trigger_owner | isupdate | isdelete | isinsert | isafter | isinsteadof |
| trgInsert | dbo | 0 | 0 | 1 | 1 | 0 |
| trgInsert2 | dbo | 0 | 0 | 1 | 1 | 0 |
| trgInsert3 | dbo | 0 | 0 | 1 | 1 | 0 |
| trgEncrypted | dbo | 0 | 0 | 1 | 1 | 0 |
触发器可用于以下场景：例如，如果数据库被反范式化，需要一种自动方式来更新多个表中包含的冗余数据，或者需要自定义消息和复杂的错误处理，或者一个表中的值必须针对另一个表中的非相同值进行验证。
触发器是一个强大的工具，可在数据修改时自动强制执行业务规则。触发器还可以用于维护数据完整性。只有当您无法使用约束、规则和默认值强制执行数据完整性时，才应使用触发器来维护数据完整性。不能在临时表上创建触发器。