触发器的8个使用范例!
A.使用包含提醒消息的 DML 触发器
USE AdventureWorks
IF OBJECT_ID('Sales.reminder1','TR') IS NOT NULL
DROP TRIGGER Sales.reminder1 --删除索引
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT,UPDATE
AS RAISERROR('NOTIFY CUSTOMER RELATEIONS',16,10)
GO
USE AdventureWorks
GO
INSERT INTO Sales.Customer VALUES('1','S','83905BEC-6F5E-4F71-B162-C98DA069F38A','2004-10-13 11:15:07.263')
消息 50000,级别 16,状态 10,过程 reminder1,第 4 行
NOTIFY CUSTOMER RELATEIONS
(1 行受影响)
B. 使用包含提醒电子邮件的 DML 触发器
USE AdventureWorks
GO
IF OBJECT_ID('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT,UPDATE,DELETE
AS
EXEC msdb.dbo.sp_send_dbmail --邮件存储过程在 msdb 数据库中
@profile_name='AdventureWorks Administrator',
@recipients='danw@Adventure-works.com',
@body='body context,dont''t forget to print a report for the sales force',
@subject='Reminder'
GO
USE AdventureWorks
GO
INSERT INTO Sales.Customer VALUES('1','S','83905BEC-6F5E-4F76-B163-C98DA069F38A','2004-10-13 11:15:07.263')
---下面代码开启sql服务器email服务,默认不开的
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
C. 使用 DML AFTER 触发器在 PurchaseOrderHeader 和 Vendor 表之间强制实现业务规则
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL DROP TRIGGER Purchasing.LowCredit GO CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS DECLARE @creditrating tinyint, @vendorid int SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i ON p.PurchaseOrderID = i.PurchaseOrderID JOIN Purchasing.Vendor v on v.VendorID = i.VendorID IF @creditrating = 5 BEGIN RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1) END
D. 使用延迟名称解析
以下示例将创建两个触发器,用于阐释延迟名称解析。
USE AdventureWorks
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS
SELECT e.EmployeeID, e.BirthDate, x.info
FROM HumanResources.Employee e INNER JOIN does_not_exist x
ON e.EmployeeID = x.xID
GO
-- Here is the statement to actually see the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers t INNER JOIN sys.sql_modules m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO
-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE AdventureWorks
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig2
GO
CREATE TRIGGER trig2
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = 'AltPhone'
FROM HumanResources.Employee
GO
-- Here is the statement to actually see the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers t INNER JOIN sys.sql_modules m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO
E. 运用具有数据库范围的 DDL 触发器
下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。
复制代码
USE AdventureWorks IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'safety') DROP TRIGGER safety ON DATABASE GO CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1) ROLLBACK GO DROP TRIGGER safety ON DATABASE GO
F. 运用具有服务器范围的 DDL 触发器
在以下示例中,如果当前服务器实例上出现任何 CREATE DATABASE 事件,则使用 DDL 触发器输出一条消息,并使用 EVENTDATA
函数检索对应 Transact-SQL 语句的文本。
注意:
若要查看在 DDL 触发器中使用 EVENTDATA 的更多示例,请参阅使用 EVENTDATA 函数。
复制代码
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'ddl_trig_database') DROP TRIGGER ddl_trig_database ON ALL SERVER GO CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'Database Created.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') GO DROP TRIGGER ddl_trig_database ON ALL SERVER GO
G. 使用登录触发器
下面的登录触发器示例拒绝了作为 login_test 登录名的成员登录 SQL Server 的尝试(如果在此登录名下已运行三个用户会话)。
USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE, CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END;
H. 查看导致触发器触发的事件
以下示例将查询 sys.triggers
和 sys.trigger_events
目录视图,以确定是哪个 Transact-SQL 语言事件导致触发了触发器 safety
。其中的 safety
是在前一个示例中创建的。
复制代码
SELECT TE.* FROM sys.trigger_events AS TE JOIN sys.triggers AS T ON T.object_id = TE.object_id WHERE T.parent_class = 0 AND T.name = 'safety' GO