SQL语法整理[7]——触发器

 

SQL语法整理[7]——触发器

     对数据库的数据操作,可能自动地触发一些其他的操作或者事件的发生，这就是触发器.触发器不同于存储过程,存储过程定义在数据库上,并且可以携带输入、输出参数，同时需要用户使用EXECUTE来进行调用。而触发器大多是定义在表上，没有任何参数，不需要用户调用，而是自动执行的。也就是说当用户操作某表的时候，系统就会自动调用定义上的操作事件。

     触发器是一种特殊的存储过程，也是提前编译好的SQL语句，它不同于前面介绍的存储过程，它与表紧密相连，可以看作表定义的一部分，当用户修改表的时候，触发器将会自动执行。

SQL Server2005包括两大类触发器：DML触发器和DDL触发器。

DML触发器

DML触发器是基于表而创建的，可以在一张表创建多个DML触发器。其特点是定义在表或者视图上、自动触发、不能被直接调用。用户可以针对INSERT、UPDATE、DELETE语句分别设置触发器，也可以针对一张表上的特定操作设置。触发器可以容纳非常复杂的SQL语句，但不管操作多么复杂，也只能作为一个独立的单元被执行、看作一个事务。如果在执行触发器的过程中发生了错误，则整个事务都会回滚。

 

DDL触发器

DDL触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。可以用于在数据库中执行管理任务，例如审核以及规范数据库操作。

 

实现DML触发器

(1)DML触发器

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 [ ; ] > }

（当INSERT、UPDATE、DELETE语句引起表中的数据变化时，将会自动激活触发器。例如，校验输入的数据是否合法、删除的是否合理等。

 

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 触发器。）

 

(2)AFTER触发器

AFTER触发器是在DML语句执行以后才触发的。该类型要求只有在执行某一操作，如INSERT、UPDATE、DELETE之后才能被触发，而且只能在表上定义。

 

a.UPDATE操作

/*在学生表格上创建一个UPDATE类型的触发器*/

/*创建一个触发器基于学生表格的学生编号列，年龄列*/

CREATE   TRIGGER     tg_student_id_age

ON   student

AFTER    UPDATE

AS

   IF  UPDATE(id)

    BEGIN

          INSERT   INTO     student_update_log(sysdate,log_info,dml_user)

          VALUES(GETDATE(),’修改了学生编号’,USER_NAME())

          PRINT(‘您正在更改学生表格中的学生编号关键字’)

    END

   IF  UPDATE(age)

    BEGIN

          INSERT   INTO     student_update_log(sysdate,log_info,dml_user)

          VALUES(GETDATE(),’修改了学生年龄’,USER_NAME())

          PRINT(‘您正在更改学生表格中的学生年龄关键字’)

    END

GO

 

 

/*在表格student上创建一个有关删除操作的触发器*/

CREATE   TRIGGER     tg_std_del

ON   student

AFTER   DELETE

AS

BEGIN

    INSERT    INTO   student_del_log(sysdate,del_info,dml_user)

    VALUES

    (GETDATE(),’删除了学生表格信息，将要影响表格teachers与parents’,USER_NAME())

    PRINT(‘您正在删除学生表格中的信息’)

END

GO

 

 

/*在表格student上创建一个有关插入操作的触发器*/

CREATE TRIGGER tg_student_insert

ON student

AFTER INSERT

AS

DECLARE @phone char(20)

SELECT @phone=p.phone

FROM inserted as i INNER JOIN parents as p

ON i.parents_id=p.id

IF @phone IS NULL

BEGIN

    RAISERROR('父母没有填家庭电话，请填写',16,1)

    ROLLBACK TRANSACTION

END

 

（由于 CHECK 约束只能引用定义了列级或表级约束的列，表间的任何约束（在本例中如果插入的学生数据的父母对应的phone字段为空，则不允许插入该记录）都必须定义为触发器。）

 

 (3)INSTEAD OF触发器

INSTEAD OF触发器是代替在表上的DML操作，执行了SQL语句。

AFTER触发器:在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。该类型触发器要求只有执行某一操作（如INSERT、UPDATE或DELETE）之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。

INSTEAD OF触发器：使用INSTEAD OF触发器可以代替通常的触发动作。还可为带有一个或多个基表的视图定义INSTEAD OF触发器，而这些触发器能够扩展视图可支持的更新类型。INSTEAD OF触发器执行时并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。

/*在老师表格上创建INSTEAD OF触发器*/

/*创建名称为tg_teachers_del触发器*/

CREATE   TRIGGER    tg_del_teachers

ON   teachers

INSTEAD  OF  DELETE

AS

BEGIN

   INSERT   INTO    teachers_del_log(sysdate,del_info,dml_user)

   VALUES(GETDATE(),’老师表格执行了删除信息的操作’,USER_NAME())

   PRINT(‘老师表格的信息现在不允许删除，如果删除请删除或者禁用名为tg_del_teachers的触发器’)

END

 

老师表中的信息是不能够删除的，如果对信息进行删除操作，一方面需要报警，另一方面要记录下删除操作的时间、使用的用户。

用左边的语句验证上边创建的触发器，右边的语句查看触发器执行的结果。

删除信息时系统作出了警报——“老师表格的信息现在不允许删除，如果删除请删除或者禁用名为tg_del_teachers的触发器”，DELETE操作被代替为记录日志中信息。

INSTEAD OF触发器，还可以创建在UPDATE以及INSERT关键字，其创建方法与上面的DELETE触发器是一致。

 

 (4)inserted与deleted表

在使用触发器的时候，SQL Server在系统中保存了两张临时表，分别为inserted与deleted表。这两张表都存储在高速缓存中，实际上就是事务日志的视图，它们与创建触发器的表拥有同样的结构。

inserted与deleted表中的数据是不能修改的，在inserted中存储着被INSERT和UPDATE语句影响的新数据行。当用户执行INSERT以及UPDATE的时候，将会把要INSERT以及UPDATE的数据保存在inserted表中。

例如：创建一触发器，要求在删除学生信息的时候相应地删除记录学生成绩的信息。

/*在表格student上创建名为tg_student_del的触发器*/

CREATE    TRIGGER      tg_student_del

ON   student

AFTER   DELETE

AS

BEGIN

DELETE     FROM    result

WHERE  student_id  IN

(

  SELECT     id    FROM      deleted

)

INSERT   INTO   student_del_log

(sysdate,del_info,dml_user,student_id)

SELECT    GETDATE(),’用户运行了删除操作’,USER_NAME(),id  FROM deleted

PRINT(‘用户运行了删除操作，将学生表格中的学生信息删除了’)

END

GO

 

 

实现DDL触发器

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

 

DDL触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。其可以用于在数据库中执行管理任务。

从语法上看，DDL与DML触发器不同，DML是创建表上的，而DDL则是创建在实例或者数据库上的。对于DDL是不能够使用INSTEAD OF关键字的，只能用AFTER类型的触发器。

例如，创建一个作用在数据库上的触发器，不允许删除school数据库中的任何表。

/*禁止在school数据库上删除表格或修改表的结构*/

CREATE   TRIGGER   forb_drop_table

ON   DATABASE

FOR   DROP_TABLE,ALTER_TABLE

AS

BEGIN

     PRINT’对不起，这个数据库不允许删除表格或修改表的结构，如果要删除表格，请先删除触发器forb_drop_table’

     ROLLBACK

END

GO

 

/*删除school数据库中的student表格*/

DROP    TABLE    student

GO

 

管理触发器

触发器是特殊的存储过程，所有适用于存储过程的管理方式都适合于触发器。用户可以使用SP_HELPTEXT、SP_HELP、SP_DEPENDS、SP_HELPTRIGGER等系统存储过程以及使用企业管理器来浏览触发器的有关信息，也可以使用SP_RENAME系统存储过程来为触发器重新命名。

 

触发器的管理

使用SP_HELPTRIGGER查看当前创建的触发器的信息。语法如下：

SP_HELPTRIGGER [@tablename=]’table’[,[@triggertype=]’type’]

其中type是触发器类型的取值范围，包括INSERT、UPDATE，DELETE。如果不指定type的值，那么返回定义在该表上的所有触发器的信息。type可以是下表中的任一值。

值

说明

DELETE

返回DELETE触发器信息

INSERT

返回INSERT触发器信息

UPDATE

返回UPDATE触发器信息

触发器与其他维护数据一致性方法的比较

数据完整性方法

影响

功能

系统开销

事务前或后

约束

定义在表上，在数据库操作之前作检查

中

中

前

DEFAULT/RULE

独立于表，在数据操作之前检查

弱

弱

前

TRIGGER

DML触发器定义在表上，可以实现复杂的商业逻辑。DDL触发器是定义在数据库作用域或者服务器作用域上。

弱

高

后

如果定义了外键，同时也定义了AFTER触发的触发器，如果后者违反了外键的约束，则触发器不工作，因为外键是前触发，TRIGGER是后触发，如果TRIGGER不符合要求，还需要回滚。

 

修改触发器

ALTER TRIGGER

 

删除触发器

DROP TRIGGER trigger_name

     ——大二的期末考试整理 [e-mail]