下载文档原格式
SQL Server数据库触发器是一种特殊类型的存储过程，它可以在数据库中的特定事件发生时自动执行。
触发器可以用于监视数据的变化并采取相应的操作，例如插入、更新或删除数据时触发某些业务逻辑。
本文将深入探讨SQL Server数据库触发器的工作原理，包括触发器的类型、创建和使用方法，以及一些最佳实践。
一、触发器的类型SQL Server中有两种类型的触发器：DML触发器和DDL触发器。
1. DML触发器DML触发器（Data Manipulation Language Trigger）是针对数据操作事件的触发器，包括INSERT、UPDATE和DELETE。
当这些事件发生时，DML触发器可以在受影响的表上自动执行相应的逻辑。
DML 触发器可以分为AFTER触发器和INSTEAD OF触发器。
- AFTER触发器：AFTER触发器在数据操作事件完成后触发，可以用于记录日志、更新其他相关表等操作。
- INSTEAD OF触发器：INSTEAD OF触发器可以代替原始的数据操作事件，允许用户在数据操作前执行自定义的逻辑，常用于数据验证和转换。
2. DDL触发器DDL触发器（Data Definition Language Trigger）用于监视数据库结构的变化，包括CREATE、ALTER和DROP等DDL语句的执行。
DDL触发器可以在这些数据库结构变化发生时执行相应的逻辑，如记录变更、阻止某些操作等。
二、触发器的创建和使用要创建触发器，首先需要使用CREATE TRIGGER语句定义并命名一个新触发器，然后指定触发器在哪些事件上触发，以及触发时执行的逻辑。
触发器逻辑通常是一段T-SQL代码，可以包含查询、条件判断、事务控制等操作。
1. 创建DML触发器要创建DML触发器，可以使用如下语法：```CREATE TRIGGER trigger_nameON table_nameAFTER/INSTEAD OF INSERT/UPDATE/DELETEASBEGIN-- trigger logicEND```在这个语法中，trigger_name是触发器的名称，table_name是触发器所在的表，AFTER/INSTEAD OF INSERT/UPDATE/DELETE指定触发的事件，BEGIN和END之间是触发器的逻辑代码。
Mysql中DDL,DML,DCL,TCL是什么意思？
在⼀些公司中提交给测试团队的SQL脚本会划分为DDL、DML等，但这些概念到底是如何定义的呢？
SQL(Structure Query Language)是数据库操作的的核⼼语⾔，接下来我们通过⼀张图来进⾏分析：
DDL（Data Definition Languages）语句：即数据库定义语句，⽤来创建数据库中的表、索引、视图、存储过程、触发器等，常⽤的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。
DML（Data Manipulation Language）语句：即数据操纵语句，⽤来查询、添加、更新、删除等，常⽤的语句关键字有：
SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通⽤性的增删改查。
DCL（Data Control Language）语句：即数据控制语句，⽤于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。
常⽤的语句关键字有：GRANT,REVOKE。
TCL（Transaction Control Language）语句：事务控制语句，⽤于控制事务，常⽤的语句关键字有：
COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。
一、实验背景随着信息技术的飞速发展，数据库技术在各行各业中的应用越来越广泛。
数据库触发器作为数据库管理系统的重要组成部分，具有强大的功能，能够帮助用户实现复杂的数据操作和业务逻辑。
为了提高学生对数据库触发器的理解与应用能力，我们开展了数据库触发器实验实训。
二、实验目的1. 掌握触发器的概念、作用及分类；2. 熟悉触发器的创建、修改和删除操作；3. 学会使用触发器实现业务规则和数据完整性；4. 提高数据库编程能力，为以后的实际工作打下基础。
三、实验环境1. 操作系统：Windows 102. 数据库：MySQL 5.73. 编程语言：Python四、实验内容1. 触发器的基本概念触发器是一种特殊的存储过程，它在特定事件发生时自动执行。
触发器可以分为两大类：DML触发器和DDL触发器。
DML触发器包括INSERT、UPDATE和DELETE触发器，用于在数据操作时执行特定的逻辑；DDL触发器用于在数据定义语言操作时执行特定的逻辑。
2. 触发器的创建以MySQL为例，创建触发器的语法如下：DELIMITER //CREATE TRIGGER 触发器名称BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名BEGIN-- 触发器逻辑END;//DELIMITER ;例如，创建一个名为before_insert_trigger的触发器，在向bookinfo表插入数据之前执行：DELIMITER //CREATE TRIGGER before_insert_triggerBEFORE INSERT ON bookinfoFOR EACH ROWBEGINIF NEW.price > 100 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Book price should not exceed 100';END IF;END;//DELIMITER ;3. 触发器的修改修改触发器的语法与创建触发器类似，只需要使用ALTER TRIGGER语句：ALTER TRIGGER 触发器名称BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名BEGIN-- 触发器逻辑END;例如，修改上述触发器，限制书籍价格不超过50元：DELIMITER //ALTER TRIGGER before_insert_triggerBEFORE INSERT ON bookinfoFOR EACH ROWBEGINIF NEW.price > 50 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Book price should not exceed 50';END IF;END;//DELIMITER ;4. 触发器的删除删除触发器的语法如下：DROP TRIGGER IF EXISTS 触发器名称;例如，删除上述触发器：DROP TRIGGER IF EXISTS before_insert_trigger;5. 触发器与存储过程的区别触发器和存储过程都是数据库编程的重要工具，但它们之间存在一些区别：（1）触发器在特定事件发生时自动执行，而存储过程需要手动调用；（2）触发器只能包含一个语句块，而存储过程可以包含多个语句块；（3）触发器不能返回结果集，而存储过程可以返回结果集。
DDL和DML触发器基本操作-------------------------------------------------------------DDL数据⾃定义语⾔触发器------针对数据库创建触发器create trigger safetyon databasefor drop_table, alter_table ----create_table创建表asprint 'you must disable trigger "safety" to drop or alter tables!'rollback ;------删除触发器if exists (select * from sys.triggers where name = N'safety' and parent_class=0)drop trigger [safety] on database;go------禁⽤触发器disable trigger safety on database ;go-----启⽤触发器enable trigger safety on database;------查看当前系统数据库触发器select type, name, parent_class_desc from sys.triggersunion------查看服务器触发器select type, name, parent_class_desc from sys.server_triggers ;--------创建服务器触发器create trigger ddl_trig_loginon all serverfor ddl_login_eventsasprint 'login event issued.'select eventdata().value('(/event_instance/tsqlcommand/commandtext)[1]','nvarchar(max)') go----删除服务器触发器drop trigger ddl_trig_login on all servergo;create trigger safety1on databasefor create_tableasprint 'create table issued.'select eventdata().value('(/event_instance/tsqlcommand/commandtext)[1]','nvarchar(max)') raiserror ('new tables cannot be created in this database.', 16, 1)rollback;-------------------------------------------------------------DML数据操作语⾔触发器USE AdventureWorksIF OBJECT_ID ('reminder1', 'TR') IS NOT NULLDROP TRIGGER reminder1GOCREATE TRIGGER reminder1ON CustomerAFTER INSERT, UPDATEAS RAISERROR ('Notify Customer Relations', 16, 10)GO--禁⽤触发器DISABLE TRIGGER reminder1 ON Customer;GO---启⽤触发器enable trigger reminder1 on customer-----触发器重命名EXEC sp_rename 'reminder1', 'reminder';。
存储过程与触发器概念及应用考试（答案见尾页）一、选择题1. 存储过程是什么？A. 一种数据库对象，用于存储逻辑操作B. 一种数据库对象，用于存储查询语句C. 一种数据库对象，用于存储流程控制语句D. 一种数据库对象，用于存储数据2. 触发器的作用是什么？A. 在数据库中插入、更新或删除数据前自动执行的程序B. 在数据库中创建、修改或删除表C. 用于数据完整性约束D. 用于权限管理3. 存储过程和触发器都存放在以下哪个对象中？A. 数据库B. 表C. 索引D. 视图4. 存储过程可以通过哪种方式调用？A. SQL语句B. 外部程序调用C. 内部程序调用D. 以上都是5. 触发器的类型有哪几种？A. INSERT触发器B. UPDATE触发器C. DELETE触发器D. ALL触发器6. 触发器的工作原理是在哪个事件发生时执行？A. 数据库打开时B. 数据库关闭时C. 数据被插入、更新或删除时D. 用户登录时7. 如何创建一个存储过程？A. 使用CREATE PROCEDURE语句B. 使用CREATE TABLE语句C. 使用ALTER TABLE语句D. 使用CREATE INDEX语句8. 触发器中的IF语句用于做什么？A. 进行条件判断B. 控制触发器的执行流程C. 计算数据D. 存储数据9. 在触发器中，哪个关键字表示不执行任何操作？A. ALLB. EXCEPTIONC. THEND. ELSE10. 触发器的执行顺序是怎样的？A. 从内到外，从上到下B. 从内到外，从下到上C. 从外到内，从上到下D. 从外到内，从下到上11. 触发器的功能是什么？A. 处理数据库中的数据完整性问题B. 执行数据库中的批量操作C. 监控数据库中的数据变化，并在特定事件发生时自动执行操作D. 管理数据库中的用户权限12. 存储过程与触发器都是数据库对象，它们的主要区别是什么？A. 存储过程用于存储查询结果，而触发器用于执行操作B. 存储过程可以有输入参数，而触发器不能C. 存储过程是预编译的，可以提高数据库性能，而触发器是运行时执行的D. 触发器只能由用户触发，而存储过程可以由任何具有权限的用户调用13. 下列哪个不是存储过程的特点？A. 可以接收参数B. 可以有多个输出参数C. 只能在数据库内部执行D. 可以直接修改数据库中的数据14. 触发器通常与哪个对象相关联？A. 数据库表B. 数据库视图C. 数据库索引D. 数据库存储过程15. 在MySQL中，触发器的类型有哪些？A. INSERT触发器B. UPDATE触发器C. DELETE触发器D. SELECT触发器16. 触发器的工作原理是什么？A. 当对触发器关联的数据表进行指定类型的操作时，触发器自动执行预定义的操作B. 当数据库服务器启动时，触发器自动执行C. 当有新的连接连接到数据库时，触发器自动执行D. 当有用户登录到数据库时，触发器自动执行17. 如何在MySQL中创建一个存储过程？A. 使用CREATE PROCEDURE语句B. 使用CREATE FUNCTION语句C. 使用ALTER PROCEDURE语句D. 使用ALTER FUNCTION语句18. 触发器中可以使用哪些类型的条件判断？A. IF...ELSE语句B. CASE语句C. THEN...ELSE语句D. ALL...IN语句19. 触发器可以分为几种类型？（多选）A. INSERT触发器B. UPDATE触发器C. DELETE触发器D. SELECT触发器20. 存储过程和触发器都存放在哪种类型的数据库对象中？A. 表B. 序列C. 索引D. 视图21. 存储过程的类型有哪几种？A. 标准存储过程B. 用户定义存储过程C. 扩展存储过程D. 内置存储过程22. 下列哪个不是存储过程中的控制结构？A. IF...ELSEB. WHILEC. CASED. GOTO23. 触发器在什么情况下会被触发？A. 当表被添加或修改时B. 当数据库连接打开时C. 当用户登录时D. 当执行特定SQL语句时24. 如何使用存储过程？A. 使用CREATE PROCEDURE语句创建存储过程B. 使用ALTER PROCEDURE语句修改存储过程C. 使用DROP PROCEDURE语句删除存储过程D. 以上都是25. 触发器的主要优点是什么？A. 提高数据库性能B. 减少数据库维护成本C. 增加数据库安全性D. 以上都是26. 触发器的主要缺点是什么？A. 可能导致数据库性能下降B. 可能导致数据库死锁C. 可能导致数据库崩溃D. 可能导致数据库锁定二、问答题1. 什么是存储过程？请简述其特点。
oracle 表结构变动日志Oracle表结构变动日志在Oracle数据库中，表结构的变动是非常常见的操作，包括新增、修改或删除表的列、索引或约束等。
对于这些表结构的变动，我们需要及时记录并进行日志管理，以便后续的查询、跟踪和恢复。
本文将介绍如何使用Oracle数据库的特性来记录和管理表结构的变动日志。
1. 表结构变动日志的需求表结构变动日志记录的主要目的有：- 追踪表结构变动的历史记录，方便进行历史查询和回滚操作；- 提供给开发人员、DBA等相关人员查看和分析表结构的变动情况；- 保留对表结构变动的审计跟踪，便于监控和追责。
2. 使用DDL触发器记录表结构变动Oracle数据库可以使用DDL触发器（DDL trigger）来捕捉表结构的变动，并记录到指定的日志表中。
DDL触发器可以在表创建、修改或删除时触发，我们可以通过定义触发器来实现表结构变动的日志记录。
以下是一个DDL触发器的示例：```CREATE OR REPLACE TRIGGER log_table_structure_changes AFTER CREATE OR ALTER OR DROP ON SCHEMADECLAREv_sql_text VARCHAR2(4000);BEGINv_sql_text := ORA_SQL_TXT;INSERT INTO table_structure_log(table_name, operation_type, ddl_sql_text, change_timestamp)VALUES(ora_describe(table_name), ora_sysevent, v_sql_text, SYSDATE);END;/```上述触发器在表结构变动时会将变动信息记录到名为table_structure_log的日志表中，其中包含表名、操作类型、DDL语句文本和变动时间戳等信息。
3. 日志表结构设计为了存储表结构变动的日志信息，我们需要设计合适的日志表结构。
只要注意到DDL触发器和DML触发器的区别，设计DDL触发器与设计DML触发器也很类似，下面详细讲述一下要怎么去设计一个DDL触发器。
建立DDL触发器的语句建立DDL触发器的语法代码如下：CREATE TRIGGER trigger_nameON { ALL SERVER | DATABASE }[ WITH <ddl_trigger_option> [ ,...n ] ]{ FOR | AFTER } { event_type | event_group } [ ,...n ]AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < methodspecifier > [ ; ] }用中文取代一下英文可以看得更明白：CREATE TRIGGER 触发器名ON ALL SERVER或DATABASEFOR 或AFTER激活DDL触发器的事件请在此输入内容...AS要执行的SQL语句其中：l ON后面的All Server是将DDL触发器作用到整个当前的服务器上。
如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。
l ON后面的Database是将DDL触发器作用到当前数据库，只能在这个数据库上激活该触发器。
l For或After是同一个意思，指定的是After触发器，DDL触发器不能指定的Stead Of触发器。
l 激活DDL触发器的事件包括两种，在DDL触发器作用在当前数据库情况下可以使用以下事件：CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLEDROP_APPLICATION_ROLECREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY ALTER_AUTHORIZATION_DATABASECREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE CREATE_CONTRACT DROP_CONTRACTGRANT_DATABASE DENY_DATABASE REVOKE_DATABASECREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATIONCREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION CREATE_INDEX ALTER_INDEX DROP_INDEX CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPECREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTIONDROP_PARTITION_FUNCTIONCREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEMEDROP_PARTITION_SCHEMECREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE CREATE_QUEUE ALTER_QUEUE DROP_QUEUECREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDINGDROP_REMOTE_SERVICE_BINDINGCREATE_ROLE ALTER_ROLE DROP_ROLE CREATE_ROUTE ALTER_ROUTE DROP_ROUTE CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA CREATE_SERVICE ALTER_SERVICE DROP_SERVICE CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS CREATE_SYNONYM DROP_SYNONYM CREATE_TABLE ALTER_TABLE DROP_TABLECREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER CREATE_TYPE DROP_TYPECREATE_USER ALTER_USER DROP_USERCREATE_VIEW ALTER_VIEW DROP_VIEWCREATE_XML_SCHEMA _COLLECTION ALTER_XML_SCHEMA_COLLECTIONDROP_XML_SCHEMA_COLLECTION在DDL触发器作用在当前服务器情况下，可以使用以下事件：例三，建立一个DDL触发器，用于保护数据库中的数据表不被修改，不被删除。
具体操作步骤如下：（1）启动Management Studio，登录到指定的服务器上。
（2）在如图11.1所示界面的【对象资源管理器】下选择【数据库】，定位到【Northwind】数据库上。
（3）单击【新建查询】按钮，在弹出的【查询编辑器】的编辑区里输入以下代码：CREATE TRIGGER 禁止对数据表操作ON DATABASEFOR DROP_TABLE, ALTER_TABLEASPRINT '对不起，您不能对数据表进行操作'ROLLBACK ;（4）单击【执行】按钮，生成触发器。
例四，建立一个DDL触发器，用于保护当前SQL Server服务器里所有数据库不能被删除。
具体代码如下：CREATE TRIGGER 不允许删除数据库ON all serverFOR DROP_DATABASEASPRINT '对不起，您不能删除数据库'ROLLBACK ;GO例五，建立一个DDL触发器，用来记录数据库修改状态。
具体操作步骤如下：（1）建立一个用于记录数据库修改状态的表：CREATE TABLE 日志记录表(编号 int IDENTITY(1,1) NOT NULL,事件 varchar(5000) NULL,所用语句 varchar(5000) NULL,操作者 varchar(50) NULL,发生时间 datetime NULL,CONSTRAINT PK_日志记录表 PRIMARY KEY CLUSTERED(编号 ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO（2）建立DDL触发器：CREATE TRIGGER 记录日志ON DATABASEFOR DDL_DATABASE_LEVEL_EVENTSASDECLARE @log XMLSET @log = EVENTDATA()INSERT 日志记录表(事件, 所用语句,操作者, 发生时间)VALUES(@log.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),@log.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),CONVERT(nvarchar(100), CURRENT_USER),GETDATE()) ;GO其中Eventdata是个数据库函数，它的作用是以XML格式返回有关服务器或数据库事件的信息。
@log.value是返回log这个XML结点的值，结点的位置是括号里的第一个参数。
11.15.2 测试触发器功能现在测试一下在上一章节中建立好的三个触发器的功能。
下面所有的测试都是在【查询编辑器】对话框里进行的，要打开【查询编辑器】对话框，只要单击Management Studio里【新建查询】按钮即可。
测试例三：例三是保证【Northwind】数据库里不能删除表和修改表，在【查询编辑器】对话框里输入一个删除表的SQL语句：Drop table 操作记录表运行结果如图11.17所示：请在此输入内容...请在此输入内容...图11.17 不允许删除表格测试例四：例四是保证当前服务器里的所有数据库不能被删除，在【查询编辑器】对话框里输入一个删除数据库的SQL语句：Drop DataBase test运行结果如图11.18所示：请在此输入内容...图11.18 不允许删除数据库测试例五：例五是记录对【Northwind】所进行的操作，在【查询编辑器】对话框里输入一条添加数据表和一条删除数据表的SQL语句，然后再用Select 语句查看【目志记录表】数据表里所有的记录：CREATE TABLE 测试表(编号int IDENTITY(1,1) NOT NULL,测试内容varchar(50) NOT NULL)GODrop table 测试表GOselect * from 日志记录表GO运行时不要忘了，前面曾经建立过一个不能删除数据表的触发器，要先把它禁用或删除。
运行结果如图11.19所示：请在此输入内容...图11.19 记录对数据库的操作DDL触发器有两种，一种是作用在当前SQL Server服务器上的，一种是作用在当前数据库中的。
这两种DDL触发器在Management Studio中所在的位置是不同的。
l 作用在当前SQL Server服务器上的DDL触发器所在位置是：【对象资源管理器】，选择所在SQL Server服务器，定位到【服务器对象】à【触发器】，在【摘要】对话框里就可以看到所有的作用在当前SQL Server服务器上的DDL触发器。
l 作用在当前数据库中的DDL触发器所在位置是：【对象资源管理器】，选择所在SQL Server服务器，【数据库】，所在数据库，定位到【可编程性】à【数据库触发器】，在摘要对话框里就可以看到所有的当前数据库中的DDL触发器。
右击触发器，在弹出的快捷菜单中选择【编写数据库触发器脚本为】à【CREATE 到】à【新查询编辑器对话框】，然后在新打开的【查询编辑器】对话框里可以看到该触发器的内容。
在Management Studio如果要修改DDL触发器内容，就只能先删除该触发器，再重新建立一个DDL触发器。
虽然在Management Studio中没有直接提供修改DDL触发器的对话框，但在【查询编辑器】对话框里依然可以用SQL语句来进行修改。
下面给出几个对DDL 触发器操作常用的SQL代码，由于对DDL触发器的操作和对DML触发器的操作类似，因此不再详细说明用法。
l 创建DDL触发器CREATE TRIGGER (Transact-SQL)l 删除DDL触发器DROP TRIGGER (Transact-SQL)l 修改DDL触发器ALTER TRIGGER (Transact-SQL)l 重命名DDL触发器sp_rename (Transact-SQL)l 禁用DDL触发器DISABLE TRIGGER (Transact-SQL)l 启用DDL触发器ENABLE TRIGGER (Transact-SQL)l 删除DDL触发器。
