SQL Server 触发器（ trigger ) |
您所在的位置：网站首页 › 教师表数据库 › SQL Server 触发器（ trigger ) |
|
trigger 导航专用 第一部分1. 概述① 触发器的特点② 触发器的作用③ 触发器的分类④ DML 触发器的分类 第二部分 实现1. 触发器的创建① insert 触发器的创建② delete 触发器的创建③ update 触发器的创建 2. 多表级联插入触发器3. 多表级联删除触发器4. 多表级联修改触发器5. DDL 触发器6. 使用系统存储过程查看触发器7. 修改触发器① 改名② 改质 8. 禁用启用触发器9. 删除触发器 第一部分 1. 概述 ① 触发器的特点 触发器不能被直接调用执行，它只能由事件触发而自动执行。触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。触发器可以实现比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。 ② 触发器的作用实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。 ③ 触发器的分类 DDL主要包括 create alter drop DML主要包括 insert update delete 登录触发器登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。如果身份验证失败，将不激发登录触发器。 登录触发器可从任何数据库创建，在服务器级注册，并驻留在 master 数据库中。可以使用登录触发器来审核和控制服务器会话。 ④ DML 触发器的分类 AFTER 触发器AFTER触发器又称为后触发器，该类触发器是在触发操作（INSERT、UPDATE或 DELETE）后和处理完任何约束后激发。 此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（INSERT，UPDATE或DELETE）创建多个AFTER触发器。 INSTEAD OF 触发器INSTEAD OF触发器又称为替代触发器，该类触发器代替触发动作进行激发，并在处理约束之前激发。 该类触发器既可定义在表上，也可定义在视图上。对于每个触发操作（UPDATE、DELETE 和 INSERT），每个表或视图只能定义一个 INSTEAD OF 触发器。 CLR 触发器CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。CLR 触发器还可以是 DDL 触发器。CLR 触发器将执行在托管代码中编写的方法，而不用执行 Transact-SQL 存储过程。 第二部分 实现 1. 触发器的创建 ① insert 触发器的创建在 STUMS 数据库的教师表上创建一个名为 js_insert_trigger 的触发器，当执行 INSERT 操作时，该触发器被触发，提示 “ 禁止插入记录！” 。 CREATE TRIGGER js_insert_trigger ON 教师 FOR INSERT AS BEGIN PRINT('禁止插入记录！') ROLLBACK TRANSACTION END GO在 STUMS 数据库的专业表上创建一个名为 zy_insert_trigger 的触发器，当执行 INSERT 操作时，该触发器被触发，提示 “ 禁止插入记录！” 。 代码如下： USE STUMS GO CREATE TRIGGER zy_insert_trigger ON 专业 INSTEAD OF INSERT AS PRINT('禁止插入记录！') GO 当用户向上面两张表中插入记录时，触发器被激发，插入操作将告失败！ ② delete 触发器的创建在 STUMS 数据库的教师表上创建一个名为 js_delete_trigger 的触发器，当执行 DELETE 操作时，该触发器被触发，提示 “ 禁止删除记录！” 。 代码如下： USE STUMS GO CREATE TRIGGER js_delete_trigger ON 教师 FOR DELETE AS BEGIN PRINT('禁止删除记录！') ROLLBACK TRANSACTION END GO ③ update 触发器的创建在 STUMS 数据库的 “ 教师 ” 表上创建一个名为 js_update_trigger 的 DML 触发器，用以检查是否修改了 “ 教师 ” 表中姓名列的数据，若作了修改，该触发器被触发，提示 “ 不允许修改！” 。 代码如下： USE STUMS GO CREATE TRIGGER js_update_trigger ON 教师 FOR UPDATE AS BEGIN IF UPDATE(姓名) /*检测是否修改了姓名列数据*/ PRINT('不允许修改！') ROLLBACK TRANSACTION END GO 2. 多表级联插入触发器在 STUMS 数据库的学生基本信息表上创建一个名为 xs_insert_trigger 的触发器，当在学生基本信息表中插入记录时，将该记录中的学号自动插入 Student 表。 代码如下： USE STUMS GO CREATE TRIGGER xs_insert_trigger ON 学生基本信息 FOR INSERT AS DECLARE @XH CHAR(9) /*定义局部变量*/ SELECT @XH = 学号 FROM INSERTED /*从INSERTED表中取出学号赋给变量@XH */ INSERT Student(学号) VALUES(@XH) /*将变量@XH的值插入到选课表*/ GO在 STUMS 数据库的选课表上创建一个名称为 xk_insert_trigger 触发器，当向选课表中插入记录时，检查该记录的学号在学生基本信息表中是否存在，如果不存在，则不允许插入。 代码如下： USE STUMS GO CREATE TRIGGER xk_insert_trigger ON 选课 FOR INSERT AS /* 定义局部变量 */ DECLARE @XH CHAR(9) /* 根据 inserted 表中的学号，查询 “ 学生基本信息 ” 表中对应的学号并赋给变量 @XH */ SELECT @XH = 学生基本信息.学号 FROM 学生基本信息, inserted WHERE 学生基本信息.学号 = inserted.学号 /* 根据 @XH 变量的值，作出相应的处理 */ IF @XH '' PRINT('记录插入成功') ELSE BEGIN PRINT('学号不存在，不能插入记录，插入将终止！') ROLLBACK TRANSACTION END GO 3. 多表级联删除触发器在 STUMS 数据库的学生基本信息表上创建一个名称为 xs_delete_trigger 触发器，当删除学生基本信息表中的记录时，同步删除该学号在选课表中的所有记录，并显示提示信息 “ 选课表中相应记录也被删除！” 。 代码如下： USE STUMS GO CREATE TRIGGER xs_delete_trigger ON 学生基本信息 FOR DELETE AS BEGIN DELETE 选课 WHERE 学号 IN (SELECT 学号 FROM DELETED) PRINT('选课表中相应记录也被删除!') END在STUMS数据库的系部表上创建一个名称为 xibu_delete_trigger 触发器，当删除系部表中的记录时，如果学生基本信息表中引用了此记录的系部代码，则提示 “ 用户不能删除！”，否则提示 “ 记录已删除！”。 代码如下： USE STUMS GO CREATE TRIGGER xibu_delete_trigger ON 系部 FOR DELETE AS IF (SELECT COUNT(*) FROM 学生基本信息 INNER JOIN DELETED ON 学生基本信息.系部代码 = DELETED.系部代码 ) > 0 BEGIN PRINT('该系部代码被引用,用户不能删除！') ROLLBACK TRANSACTION END ELSE PRINT('记录已删除!') GO 4. 多表级联修改触发器在 STUMS 数据库的系部表上创建一个名称为 xibu_update_trigger1 触发器，当修改系部表中的系部代码时，如果学生基本信息表中引用了该系部代码，则提示 “ 用户不能修改！”，否则提示 “ 记录已修改！” 。 代码如下： CREATE TRIGGER xibu_update_trigger1 ON 系部 FOR UPDATE AS IF UPDATE(系部代码) BEGIN DECLARE @XBDM CHAR(2) SELECT @XBDM = DELETED.系部代码 FROM DELETED IF EXISTS (SELECT 系部代码 FROM 学生基本信息 WHERE 系部代码 = @XBDM) BEGIN PRINT('该系部代码被引用,用户不能修改！') ROLLBACK TRANSACTION END ELSE PRINT('记录已修改!') END GO例如，当用户修改系部表中的系部代码时，就激发 xibu_update_trigger1 触发器，在学生基本信息表中没有引用系部表中的 “ 02 ” 系部代码， 记录就被修改 ；在学生基本信息表中引用了系部表中的 “ 02 ” 系部代码，就禁止修改。 在 STUMS 数据库的系部表上创建一个名称为 xibu_update_trigger2 触发器，当修改系部表中的系部代码时，如果学生基本信息表中引用了该系部代码，则作同样地修改，并提示 “ 记录已修改！”。 代码如下： CREATE TRIGGER xibu_update_trigger2 ON 系部 FOR UPDATE AS IF UPDATE(系部代码) BEGIN DECLARE @XBDM1 CHAR(2),@XBDM2 CHAR(2) SELECT @XBDM1 = DELETED.系部代码, @XBDM2 = INSERTED.系部代码 FROM DELETED, INSERTED UPDATE 学生基本信息 SET 系部代码 = @XBDM2 WHERE 系部代码 = @XBDM1 PRINT('记录已修改!') END例如，当用户将系部表中 “ 07 ” 系部代码改为 “ 12 ” 时，激发了 xibu_update_trigger2 触发器，学生基本信息表中有若干条记录引用了 “ 07 ” 系部代码，记录都作了同样地修改。 5. DDL 触发器为 STUMS 数据库创建一个名为 STUMS_DDL_TRG 触发器，当在 STUMS 数据库中创建、修改或删除表时，显示警告信息 “ 禁止在当前数据库中操作数据表！”，并取消这些 DDL 操作。 代码如下： USE STUMS GO CREATE TRIGGER STUMS_DDL_TRG ON DATABASE FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE /*指定事件类型*/ AS BEGIN RAISERROR('禁止在当前数据库中操作数据表！',16,1) /*错误提示信息*/ ROLLBACK TRANSACTION /*取消DDL操作*/ END GO 6. 使用系统存储过程查看触发器① 可使用 sp_help 查看触发器的一般信息； EXEC sp_help xibu_delete_trigger② 可使用 sp_depends 查看触发器的相关性； EXEC sp_depends xibu_delete_trigger③ 可使用 sp_helptext 查看触发器的定义信息； EXEC sp_helptext xibu_delete_trigger④ 可使用 sp_helptrigger 查看指定表上存在的触发器类型。 EXEC sp_helptrigger 系部 7. 修改触发器 ① 改名利用 sp_rename 系统存储过程 将 “ xibu_delete_trigger ” 触发器改名为 “ xibu_delete_DMLTRG ”。 代码如下： EXEC sp_rename xibu_delete_trigger, xibu_delete_DMLTRG GO ② 改质修改 STUMS 数据库教师表上的 js_delete_trigger 触发器，使得用户执行删除、插入、修改操作时，该触发器被触发，自动给出提示报警信息，并撤销此次操作。 代码如下： USE STUMS GO ALTER TRIGGER js_delete_trigger ON 教师 FOR DELETE,INSERT,UPDATE AS BEGIN PRINT('你不能删除、插入、修改记录！') ROLLBACK TANSACTION END GO 8. 禁用启用触发器DDL /* 禁用 DDL 触发器 */ DISABLE TRIGGER STUMS_DDL_TRG ON DATABASE GO /* 启用 DDL 触发器 */ ENABLE TRIGGER STUMS_DDL_TRG ON DATABASE GODML -- 禁用 DML 触发器 ALTER TABLE 教师 DISABLE TRIGGER js_delete_trigger GO -- 启用 DML 触发器 ALTER TABLE 教师 ENABLE TRIGGER js_delete_trigger GO 9. 删除触发器 DROP TRIGGER js_delete_trigger |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |