sql触发器语法

1、 第七章 触发器 本节要点A 认识触发器B 触发器的分类C触发器的创建D使用触发器E 触发器练习的巩固典型的应用就是银行的取款机系统 帐户信息表bank 交易信息表transInfo 张三取钱200 问题：没有自动修改张三的余额最优的解决方案就是采用触发器：它是一种特殊的存储过程它是一种特殊的存储过程 也具备事务的功能也具备事务的功能 它能在多表之间执行特殊的业务规则它能在多表之间执行特殊的业务规则 张三开户1000元,李四开户1元 为什么需要触发器一、认识触发器1、触发器的概述定义：触发器是在对指定表插入(insert)、更新(update)或删除(delete)操作时所自动执行的存储过程。

2、作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。 触发器是一种高级约束，可以定义比用check 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case） 可引用其它表中的列 所有数据值均正确的状态一、认识触发器相似：触发器与存储过程相似，触发器也是SQL语句集。区别：触发器不能用execute语句调用，而是在用户执行Transact-SQL语句自动执行。2、触发器与存储过程的区别运行方式区别：存储过程用户应用程序或触发器调用并执行触发器特定事件，自动执行二、触发器的分类1、DDL触发器定义：当服务器或者数据库中发生数据定义语言（DDL）时触发器将被调用。DDL

3、数据定义语言：创建，修改或删除数据库中各种对象，包括表，视图，索引等。以下操作可以使用DDL触发器：1、要防止对数据库架构进行某些修改 3、要记录数据架构中的更改或者事件。 2、希望数据库中发生某种情况以响应数据库架构中的修改二、触发器的分类1、DML触发器定义：当数据库服务器中发生数据操作语言（DML）事件时要执行的操作。DML数据定义语言：对已存在数据库表进行元组插入，删除，修改。2、DML触发器的用途( 1 ) DML触发器可通过数据库中相关表实现级联修改，通过级联引用完整性约束可以更有效的更改。( 2 )DML触发器可以防止恶意或者错误insert，update，以及delete操作，

4、并强制执行比check约束 定义的限制更为复杂的其他限制。( 3 )DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。( 4 )一个表的多个同类DML触发器，允许采取多个不同的操作来响应同一个修改语句。二、触发器的分类 触发器触发时 创建了DELETED表和INSERED表 只读，不允许修改；触发器执行完成后，自动删除1、delete表 delete表存放由执行delete或者update语句而要从表中删除所有行，在执行delete或update被删除的行从触发触发器表中移动到delete表中。2、insert表insert表存放由执行insert或者update语句而要向表中插

5、入的所有的行，在执行insert或update新的行同时添加到触发器的表和insert表中，insert表的内容是触发触发器的表新行的副本。二、触发器的分类修改操作inserted表deleted表增加增加(INSERT)(INSERT)记录记录存放新增的记录存放新增的记录-删除删除(DELETE)(DELETE)记录记录-存放被删除的记录存放被删除的记录修改修改(UPDATE)(UPDATE)记录记录存放更新后的记录存放更新后的记录存放更新前的记录存放更新前的记录inserted表和deleted表存放的信息三、触发器创建语法create trigger 触发器名称on 表名 / 视图名fo

6、r / after /insted of -触发的时机delete/insert/update -触发器的类型assql语句end例：在student数据库的学员信息表表中 创建一个stuinfo_delete触发器create trigger stuinfo_deleteon 学员信息表for delete asdelete 成绩表from 成绩表 a , deleted bwhere a.学号=b.学号四、DML触发器1、insert触发器create trigger stuinfo_insert -创建触发器on 学生表after insert asupdate stuinfo -插入年

7、龄set 年龄=datediff（year，s.生日，getdate（）from 学生表 a inner join inserted bwhere a.学号=b.学号-after 之insert触发器insert触发器对目标表执行insert语句时，就会调用触发器。例如我们添加一个年龄列，这个触发器的名称为stuinfo_insert插入一个新的学员信息，以验证触发器是否执行insert into stuinfo value （0002, mike, 男, 1993-01-25, null）select *from stuinfotransInfocardIDtransType transMo

8、ney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300300存入 5005001001 0001支取 200插入记录行触发insert触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作INSERT触发器的工作原理：触发器的工作原理：四、DML触发器1、delete触发器定义：当触发delete触发器时

9、，从表中删除的行将会放置在一个特殊的delete表中，delete跟deleted表一样是个临时表。区别：delete触发器并不执行truncate table语句，原因在于日志不记录 truncate table例题：删除数据库【student】中【学员信息】表时，相应的考试成绩备份表也会删除,使用delete语句删除002这个学员信息，验证触发器就会自动执行，并删除学员成绩备份表中的信息create trigger stuinfo_delete -创建触发器on 学员信息for delete asdelete 成绩表from 成绩表 a , deleted bwhere a.学号=b.学号

10、insert 成绩表 values（0002 ,90,95）select *from 成绩表 delete from 学员信息 where stuno=0001select * from 成绩表transInfocardIDtransType transMoney存入 300存入 5001001 0001支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500deletedcardIDtransType transMoney1001 0001支取 200删除记录行触发delete触发器向deleted表中插入

11、被删除的副本触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作DELETE触发器的工作原理：触发器的工作原理：四、DML触发器update触发器定义：当一个update语句在目标表运行的时候就调用更新触发器，例题：数据库【student】中【stuinfo】表中生产日期的改变，对应学员的年龄也发生改变create trigger stuinfo_update -创建触发器on stuinfoafter stuinfo asif update(birthday) beginupdate stuinfo -执行SQL语句set stuage=datediff(year,s.b

12、irthday,getdate()from stuinfo s inner join insertd ion s.stuno=i.stunoend四、DDL触发器定义：当服务器或者数据库中发生数据定义语言（DDL）时触发器将被调用。DDL数据定义语言：创建，修改或删除数据库中各种对象，包括表，视图，索引等。例题：如何使用DDL触发器来防止在【student】数据库中表被修改删除的操作，首先要在 【student】数据库中定义一个数据库的DDL触发器。create trigger TRIG_DDL -创建一个DDL触发器for drop_table,drop_view -删除表或删除视图on d

13、atabase -数据库asbegin print 无法修改或者删除表，请在之前禁用或删除DDL触发器TRIG_DDL！ ROLLBACK TRANSACTION -采用回滚的办法以达到阻止操作的目的end 嵌套触发器 如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，那么就形成了触发器的嵌套。任何触发器都可以包含影响另一个表的UPDATE、INSERT或者DELETE语句。嵌套触发器在安装时就被启用嵌套触发器在安装时就被启用可以使用系统存储过程可以使用系统存储过程sp_configuresp_configure禁用和重新启用嵌套禁用和重新启用嵌套触发器最多可以

14、嵌套触发器最多可以嵌套3232层层禁用嵌套禁用嵌套EXEC sp_configure nested triggers,0启用嵌套启用嵌套EXEC sp_configure nested triggers,1管理触发器查看触发器：查看触发器：可以使用像sp_helptext、sp_help和sp_depends等系统存储过程来查看触发器的有关信息修改触发器：修改触发器：修改触发器的语法与创建触发器的语法一样，只要把create改为alter即可删除触发器：删除触发器：禁用禁用DML触发器：触发器：禁用禁用DDL触发器：触发器：禁用所有触发器：禁用所有触发器：启用触发器：启用触发器：只要将DISA

15、BLE变为ENABLE即可exec sp_helptext TRIG_DDL -查看触发器的内容Alter trigger TRIG_DDL drop trigger TRIG_DDLDISABLE TRIGGER tri_stuinfo_update ON stuInfoDISABLE TRIGGER TRIG_DDL ON DATABASEDISABLE TRIGGER ALL ON ALL SERVERENABLE TRIGGER tri_stuinfo_update ON stuInfo -启用触发器ENABLE TRIGGER trig_DDL ON DATABASE -启用数据库触

16、发器ENABLE TRIGGER ALL ON ALL SERVER -启用以同一作用域定义的所有触发器不能在触发器中使用的语句不能在触发器中使用的语句 触发器中可以使用大多数触发器中可以使用大多数 T-SQL T-SQL 语句，语句，但如下一些语句是不能在触发器中使用的。但如下一些语句是不能在触发器中使用的。CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。ALTER 语句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。DROP 语句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。DISK 语句，如：DISK INIT、DISK RESIZE。LOAD 语句，如：LOAD DATABASE、LOAD LOG。RESTORE 语句，如：RESTORE DATABASE、RESTORE LOG。RECONFIGURETRUNCATE TABLE 语句在sybase的触发器中不可使用！触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，触发器通常用于强制业务规则触发器通常用于强制业务规则触发器还是一个特殊的事务单元，当出现错误时，可以执行触发器还是一个特殊的事务单元，当出现