SQL server2008 中的触发器怎么定义，以及应用？

SQL server2008 中的触发器怎么定义，以及应用？最好是可以有例子解释下，，各位大神，，求专业解释

9 个解决方案

#1

查看联机文档

#2

语法
 
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE 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

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE 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 ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE 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 ]

<method_specifier> ::=
    assembly_name.class_name.method_name
 

#3

参数
schema_name 
DML 触发器所属架构的名称。DML 触发器的作用域是为其创建该触发器的表或视图的架构。对于 DDL 或登录触发器，无法指定 schema_name。

trigger_name 
触发器的名称。trigger_name 必须遵循标识符规则，但 trigger_name 不能以 # 或 ## 开头。

table | view
对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。视图只能被 INSTEAD OF 触发器引用。不能对局部或全局临时表定义 DML 触发器。

DATABASE
将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器。

ALL SERVER
将 DDL 或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现 event_type 或 event_group，就会激发该触发器。

WITH ENCRYPTION
对 CREATE TRIGGER 语句的文本进行模糊处理。使用 WITH ENCRYPTION 可以防止将触发器作为 SQL Server 复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。

EXECUTE AS
指定用于执行该触发器的安全上下文。允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。

有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。

FOR | AFTER
AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。

如果仅指定 FOR 关键字，则 AFTER 为默认值。

不能对视图定义 AFTER 触发器。

INSTEAD OF
指定执行 DML 触发器而不是触发 SQL 语句，因此，其优先级高于触发语句的操作。不能为 DDL 或登录触发器指定 INSTEAD OF。

对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。但是，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。

INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将引发错误。用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。

{ [DELETE] [,] [INSERT] [,] [UPDATE] }
指定数据修改语句，这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。

对于 INSTEAD OF 触发器，不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。同样，也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。

event_type 
执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。DDL 事件中列出了 DDL 触发器的有效事件。

event_group 
预定义的 Transact-SQL 语言事件分组的名称。执行任何属于 event_group 的 Transact-SQL 语言事件之后，都将激发 DDL 触发器。DDL 事件组中列出了 DDL 触发器的有效事件组。

CREATE TRIGGER 运行完毕之后，event_group 还可通过将其涵盖的事件类型添加到 sys.trigger_events 目录视图中来作为宏使用。

WITH APPEND
指定应该再添加一个现有类型的触发器。仅当兼容级别等于或低于 65 时，才需要使用此可选子句。如果兼容级别等于或高于 70，则不需要使用 WITH APPEND 子句来添加现有类型的其他触发器。这是兼容级别设置等于或高于 70 的 CREATE TRIGGER 的默认行为。有关详细信息，请参阅 sp_dbcmptlevel (Transact-SQL)。

WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器，则也不能使用该子句。仅当为了向后兼容而指定了 FOR 时（但没有 INSTEAD OF 或 AFTER）时，才能使用 WITH APPEND。如果指定了 EXTERNAL NAME（即触发器为 CLR 触发器），则不能指定 WITH APPEND。

重要提示： 
Microsoft SQL Server 的下一版本将删除 WITH APPEND。请不要在新的开发工作中使用 WITH APPEND，并准备修改当前使用它的应用程序。
 


NOT FOR REPLICATION
指示当复制代理修改涉及到触发器的表时，不应执行触发器。有关详细信息，请参阅使用 NOT FOR REPLICATION 来控制约束、标识和触发器。

sql_statement 
触发条件和操作。触发器条件指定其他标准，用于确定尝试的 DML、DDL 或 logon 事件是否导致执行触发器操作。

尝试上述操作时，将执行 Transact-SQL 语句中指定的触发器操作。

触发器可以包含任意数量和种类的 Transact-SQL 语句，但也有例外。有关详细信息，请参阅“备注”。触发器的用途是根据数据修改或定义语句来检查或更改数据；它不应向用户返回数据。触发器中的 Transact-SQL 语句常常包含控制流语言。

DML 触发器使用 deleted 和 inserted 逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，则使用：

 复制代码 
SELECT *
FROM deleted
 

有关详细信息，请参阅使用插入的和删除的表。

DDL 和登录触发器通过使用 EVENTDATA (Transact-SQL) 函数来获取有关触发事件的信息。有关详细信息，请参阅使用 EVENTDATA 函数。

在 DELETE、INSERT 或 UPDATE 触发器中，如果兼容级别设置为 70，则 SQL Server 不允许在 inserted 和 deleted 表中存在 text、ntext 或 image 列引用。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。如果兼容级别等于或低于 65，则将为 inserted 或 deleted 的文本、ntext 或者允许 Null 值的 image 列返回 Null 值；如果列不能为 Null，则返回零长度的字符串。

如果兼容级别等于或高于 80，SQL Server 将允许通过表或视图的 INSTEAD OF 触发器来更新 text、ntext 或 image 列。

重要提示： 
在 Microsoft SQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。AFTER 和 INSTEAD OF 触发器均支持 inserted 和 deleted 表中的 varchar(MAX)、nvarchar(MAX) 和 varbinary(MAX) 数据。
 


< method_specifier> 
对于 CLR 触发器，指定程序集与触发器绑定的方法。该方法不能带有任何参数，并且必须返回空值。class_name 必须是有效的 SQL Server 标识符，并且该类必须存在于可见程序集中。如果该类有一个使用“.”来分隔命名空间部分的命名空间限定名称，则类名必须用 [] 或“ ”分隔符分隔。该类不能为嵌套类。

注意： 
默认情况下，SQL Server 无法运行 CLR 代码。您可以创建、修改和删除引用托管代码模块的数据库对象，但是，除非使用 sp_configure 启用了 clr enabled Option，否则不会在 SQL Server 实例中执行这些引用。 

#4

好麻烦，，，，

#5

引用 4 楼  的回复:

好麻烦，，，，

拿给你简单的解释一下：
触发器一般情况下是值对某个表进行了insert ,delete, update操作后被触发，从而执行触发器内部定义的语句：

有after,instead of,for 三个类型的触发器，after是指在触发触发器的操作执行完以后执行触发器内部的操作，for类同与after触发器instead of是指不执行触发触发器的操作，只执行触发器内部的操作：

例子：

触发器部分练习:

--创建工资表，要求建立触发器，当插入数据是工资小于的
--修改为，大于的修改为

create table tbl_salary(
empid int not null,
empname varchar(20) not null,
salary int
)
--创建触发器实现上述功能：
create trigger tri_insert on tbl_salary
instead of insert
as
declare @id int,@empname varchar(20),@salary int
select @id=empid,@empname=empname, @salary=salary
       from inserted
if @salary<3000
   set @salary=3000
if @salary>8000
   set @salary=8000
insert into tbl_salary values(@id,@empname,@salary)

--插入数据，验证触发器是否正确使用：
insert into tbl_salary values(1002,'tracy',9800)
insert into tbl_salary values(1004,'kobe',2800)
insert into tbl_salary values(1006,'tom',4800)
select *from tbl_salary

/*
创建两表，建立一个视图基于这两张表。
创建触发器实现向视图插入数据时等于
分别向两表插入数据
*/

--创建表tbl_dept：
create table tbl_dept(
deptid varchar(4) primary key,
deptname varchar(10) not null
)

--创建表tbl_emp：
create table tbl_emp(
empid varchar(10) primary key,
empname varchar(20) not null,
deptid varchar(4) foreign key
references tbl_dept(deptid) 
not null
)

--创建视图用于存储表tbl_dept和tbl_emp表中的数据：
create view v_empinfo 
as 
select 
tbl_emp.empid,tbl_emp.empname,tbl_emp.deptid,
tbl_dept.deptname 
from 
tbl_emp,tbl_dept
where 
tbl_emp.deptid=tbl_dept.deptid
select *from v_empinfo

--创建触发器实现上述功能：
create trigger tr_insert on v_empinfo
instead of insert
as
declare @eid varchar(10),@ename varchar(20),
        @did varchar(4),@dname varchar(10)
select @eid=empid,@ename=empname,
       @did=deptid,@dname=deptname
from 
       inserted
insert into tbl_dept values(@did,@dname)
insert into tbl_emp values(@eid,@ename,@did)

--验证是否成功向两表中插入数据：
insert into v_empinfo values('200911076','tracy',
                             '1001','sales')
select *from tbl_emp
select *from tbl_dept

--创建触发器实现删除主表中的数据时自动删除附表中的数据:

--创建表tbl_stu:
create table tbl_stu(
stuid varchar(15) primary key,
stuname varchar(20) not null
)

--插入数据:
insert into tbl_stu values('200911076','李白和')
insert into tbl_stu values('200911077','王洪洋')

--创建表tbl_score:
create table tbl_score(
stuid varchar(15) 
foreign key 
references tbl_stu(stuid)
not null,
Math int default(0)
)

--插入数据:
insert into tbl_score values('200911076',85)
insert into tbl_score values('200911077',95)

--创建视图v_view:
create view v_view 
as
select 
s.stuid,s.stuname,sc.math
from 
tbl_stu s,tbl_score sc
where
s.stuid=sc.stuid

--为视图创建触发器tr_delete:
create trigger tr_delete on v_view
instead of delete
as
declare @stuid varchar(15),@stuname varchar(20),
        @math int
select @stuid=stuid,@stuname=stuname,@math=math
       from deleted
delete tbl_score where stuid=@stuid
delete tbl_stu where stuid=@stuid

--验证是否删除两表中对应的所有信息
delete v_view where stuid='200911077'
select *from tbl_stu
select *from tbl_score


希望你能看懂

#6

例如有两张表，员工人事表employee和销售表sales；员工表的主码emp_no作为sales的外码，sales的主码为sale_id，要保证修改emp_no时sales的sale_id也要改变；这里肿么用触发器实现呢，，，，

#7

谢谢大神，，，解释的相当透彻，，理解了，，，

#8

可以顺便举下update的例子吗

#9

sql触发器的概念及优点
http://3aj.cn/article/1772.html