SQL Server 创建触发器

一、概述

创建 DML、DDL 或登录触发器。 触发器是一种特殊类型的存储过程，在数据库服务器中发生事件时自动运行。 如果用户尝试通过数据操作语言 (DML) 事件修改数据，DML 触发器运行。 DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。 此类触发器在任何有效事件触发时触发，无论表行是否受影响。 有关详细信息，请参阅 DML Triggers。

DDL 触发器是为了响应各种数据定义语言 (DDL) 事件而运行。 这些事件主要对应于 Transact-SQL CREATE、ALTER 和 DROP 语句，以及执行类似 DDL 操作的某些系统存储过程。

登录触发器是为了响应在建立用户会话时触发的 LOGON 事件而触发。 可以直接使用 Transact-SQL 语句创建触发器，也可以使用程序集方法，它们是在 Microsoft .NET Framework 公共语言运行时 (CLR) 中创建，并上传到 SQL Server 实例中。 使用 SQL Server，可以为任何特定语句创建多个触发器。

二、语法

1、DML触发器

-- SQL Server Syntax 
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name  
ON { table | view }  
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }  
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }  
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

<method_specifier> ::=  
    assembly_name.class_name.method_name

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a
-- table (DML Trigger on memory-optimized tables)  

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name  
ON { table }  
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }  
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }  
AS { sql_statement  [ ; ] [ ,...n ] }  

<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

2、DDL触发器

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY,
-- REVOKE or UPDATE statement (DDL Trigger)  

CREATE [ OR ALTER ] TRIGGER trigger_name  
ON { ALL SERVER | DATABASE }  
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

3、登录触发器

-- Trigger on a LOGON event (Logon Trigger) 
CREATE [ OR ALTER ] TRIGGER trigger_name  
ON ALL SERVER  
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

三、实例

1、DML触发器

USE db_student
IF OBJECT_ID(‘loving’,’tr’) IS NOT NULL
DROP TRIGGER loving
GO

CREATE TRIGGER loving
ON course
AFTER INSERT,UPDATE
AS RAISERROR(‘Notify course Relations’,16,10)
GO

2、DDL触发器

USE db_student

--如果触发器存在,则删除它
IF EXISTS(SELECT * FROM sys.triggers WHERE name=’loving’)
DROP TRIGGER loving30
On database
GO

CREATE TRIGGER loving30
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
PRINT ‘在做删除或更新中国’
ROLLBACK
END

三、总结

1、deleted表存放由于执行delete或update语句而要从表中删除的所有行。

在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted表，这两个表不会有共同的行。

2、inserted表存放由于执行insert或update语句而要向表中插入的所有行。

在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中，inserted表的内容是激活触发器的表中新行的拷贝。

注意：

1、update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。

2、Inserted表和Deleted表仅仅在触发器在执行时存在，这一点是非常重要的，我们一定要明确。它们在某一特定时间和某一特定表相关。一旦某一个触发器结束执行时，相应的在两个表内的数据都会丢失。如果你想创建一个在任意一个这些表内数据的永久拷贝，你需要在触发器内把这些表内的数据拷贝到一个永久的表内。