第13章 触发器
在创建了表、视图和存储过程等对象之后,需要给这些对象配置一些对应的操作。如使用EXEC命令执行存储过程、执行INSERT语句向数据表中插入数据等。在SQL中也支持自动执行的对象,实现对数据的增加、删除及修改等操作。触发器就可以被理解为SQL中自动执行的对象,本章将向读者讲解有关触发器方面的相关知识。
13.1 理解触发器
触发器也是一种存储过程,它是一种特殊类型的存储过程。触发器只要满足一定的条件,它就可以触发完成各种简单和复杂的任务,可以帮助用户更好地维护数据库中数据的完整性。
13.1.1 触发器概述
触发器是一种与数据表紧密关联的特殊的存储过程。 与存储过程一样,触发器在数据库里以独立的对象存储。但是与存储过程不同的是,存储过程通过其他程序来启动运行,而触发器不能直接被调用,只能通过事件来启动运行。即当某个事件发生(触发器表内容被更改)时,触发器自动地隐式运行,并且,触发器不能传递或接受参数。
触发器可以查询其他表,而且可以包含复杂的 SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如:您可以根据客户当前的帐户状态,控制是否允许插入新订单。 触发器也可用于强制引用完整性,以便在多个表中添加、更新或删除行时,保留在这些表之间所定义的关系。然而,强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图,则可以在表之间创建关系以自动创建外键约束。
触发器由触发器语句、触发器主体和触发器限制3部分组成。
- 通过触发器语句能够触发触发器主体和指定触发器的关联表,触发器语句主要指的是如UPDATE、DELETE和INSERT之类的DML语句。
- 触发器主体是指在触发触发语句时执行的PL/SQL块。
- 触发器的限制是指通过使用WHEN子句限制触发器。
触发动作实际上就是一系列的SQL语句,主要可以分为行级触发器和语句级触发器两种方式。行级触发器是指对被事件影响的每一行每一元组执行触发过程。语句级触发器方式是触发器的默认方式,是指对整个事件只执行一次触发过程。
触发事件包括执行INSERT、DELETE和UPDATE语句,从而实现对数据表中的数据进行插入、删除和修改操作。事件的触发有Before和After两个相关的事件。Before触发器是在事件发生之前触发,After触发器是在事件发生之后被触发。
13.1.2 触发器的优点
触发器主要有如下几个方面的优点。
- 当发生了对数据的更新维护时,与之相关的触发器就会立即被激活,触发器会被自动调用。
- 触发器可以对需要存储的数据加以限制。这些限制比用CHECK约束所定义的限制更复杂。与CHECK约束不同的是,触发器可以引用其他表中的列。
- 触发器可以完成数据库中相关表之间的级联修改。级联修改是指为了保证数据之间的逻辑性及依赖关系,在对一张表进行修改的同时,能够自动实现其他表中需要进行的修改。
注意:在使用触发器时一定要谨慎,因为每次访问表时都可能激活某个触发器,这样会给数据库带来负担,所以,尽量用触发器执行别的方法不能执行的操作,例如,如果可以使用约束处理某个触发器的事件,就使用约束而不要使用触发器。
13.2 SQL Server中的触发器应用
创建触发器使用CREATE TRIGGER命令来实现。本节中将结合具体的示例来讲解与触发器创建方面相关的知识。
13.2.1 创建INSERT触发器
当对数据表进行插入数据操作时触发INSERT触发器。当针对某一数据表创建INSERT触发器之后,在向该数据表中插入数据之后,可以对其他数据表执行插入数据、更新数据和删除数据等操作。
【语法说明】
创建INSERT触发器的语法结构如下所示。
CREATE TRIGGER trigger_name
ON {table_name | view_name}
[WITH ENCRYPTION]
{
{{FOR | AFTER }
{INSERT}
[NOT FOR REPLICATION]
AS
[{IF UPDATE(column)
[{AND | OR} UPDATE(column)]
[…n]
| IF (COLUMNS_UPDATED() { bitwise_operator} updated_bitmask)
{comparison_operator}
Column_bitmask […n]
}]
Sql_statement […n]
}
}
代码中的主要参数说明如下所示:
- trigger_name:要创建触发器的名字,触发器的名字在当前数据库中必须是惟一,并且命名必须符合SQL的命名规则。
- table_name、view_name:分别指的是与触发器相关联的表或视图的名称。
- WITH ENCRYPTION:表示为了防止用户通过查询syscomments表获取触发器的代码,而对含有CREATE
TRIGGER文本的syscomments表进行加密。 - AFTER:表示在执行了指定的INSERT操作之后,才能够激活触发器,执行触发器中的SQL语句。
- FOR:表示仅能在表上创建的AFTER触发器。
- INSERT:表示要执行的操作是插入数据的操作。
- NOT FOR REPLICATION:表示当复制表时,触发器不能被执行。
- AS:其后面列出触发器将要执行的动作。
- IF UPDATE(column):用来测定对某一确定列是INSERT操作还是UPDATE操作。
- IF COLUMNS_UPDATED:用于检查列是被更新还是被插入,该操作仅在INSERT和UPDATE类型的触发器中被使用。
- bitwise_operator:代表位逻辑运算符,即“&”运算符。
- updated_bitmask:表示列的整位掩码。
- comparison_operator:表示比较操作符。可以是“=”、“>”或者“<”。
- Column_bitmask:表示在IF COLUMNS_UPDATED子句中,要测试是否被更新列的序号的掩码。
- Sql_statement:表示包含在触发器中的处理语句。
【上机实战】
创建触发器trig_insert,当向员工信息表(tb_ygxx)中添加数据时,DBMS将输出字符串“数据成功添加!”,其具体实现的代码如下所示。
CREATE TRIGGER trig_insert
ON tb_ygxx
AFTER INSERT
AS
PRINT '数据成功添加!'
代码执行以后,触发器trig_insert创建完成。此时通过INSERT INTO语句向员工信息表(tb_ygxx)中添加数据信息。
INSERT INTO tb_ygxx
VALUES('1007','赵刚','男',36,'1976-6-10','吉林省四平市','136********','吉林省长春市')
代码执行以后,数据添加成功,并且输出“数据成功添加!”的提示信息,如图13.1所示。这说明,在向员工信息表(tb_ygxx)中添加数据信息之后,触发了触发器trig_insert,提示信息是通过该触发器输出的。
图13.1 INSERT触发器的简单应用
前面讲解的INSERT触发器应用示例比较简单,下面再讲解一下复杂一些的INSERT触发器应用。
创建触发器insert_ygxx,当向员工信息表(tb_ygxx)中插入员工信息时,同时通过触发器insert_ygxx将员工的工资信息添加到员工工资表(tb_yggz)当中。为了便于读者进行比较,首先将员工信息表(tb_ygxx)和员工工资表(tb_yggz)中的数据信息显示出来。
显示员工信息表(tb_ygxx)中数据信息的实现代码如下所示。
select * from tb_ygxx
代码执行以后,运行结果如图13.2所示。
图13.2 员工信息表(tb_ygxx)中的数据信息
显示员工工资表(tb_yggz)中数据信息的实现代码如下所示。
select * from tb_yggz
代码执行以后,运行结果如图13.3所示。
图13.3 员工工资表(tb_yggz)中的数据信息
通过创建INSERT触发器向另一个数据表中插入数据的实现代码如下所示。
CREATE TRIGGER insert_ygxx
ON tb_ygxx
AFTER INSERT
AS
INSERT INTO tb_yggz
values('1008','赵立',1800,100)
代码执行以后,触发器被创建。此时通过下面的语句向员工信息表(tb_ygxx)中插入数据。
INSERT INTO tb_ygxx
VALUES('1008','赵立','男',36,'1976-6-10','吉林省四平市','139********','吉林省长春市')
代码执行以后,分别查看员工信息表(tb_ygxx)和员工工资表(tb_yggz)中的数据信息。
查看员工信息表(tb_ygxx)中数据信息的实现代码如下所示。
select * from tb_ygxx order by 编号
代码执行以后,其执行结果如图13.4所示。
图13.4 应用存储过程之后员工信息表(tb_ygxx)中的数据信息
查看员工工资表(tb_yggz)中数据信息的实现代码如下所示。
select * from tb_yggz order by ygid
代码执行以后,其执行结果如图13.5所示。
图13.5 应用存储过程之后员工工资表(tb_yggz)中的数据信息
从上面的执行结果中可以看出,在创建了insert_ygxx触发器之后,当向员工信息表(tb_ygxx)中插入数据之后,出发了insert_ygxx触发器,同时将员工的工资信息添加到员工工资表(tb_yggz)当中。
说明:应用INSERT触发器,除了可以向另一个数据表中插入数据之外,还可以更新或删除另一个数据表中的数据信息。
12.2.2 UPDATE触发器
当对数据表进行数据更新操作时触发UPDATE触发器。当针对某一数据表创建UPDATE触发器之后,在更新该数据表中的数据信息之后,可以对其他数据表执行插入数据、更新数据和删除数据等操作。
【语法说明】
创建UPDATE触发器的语法结构如下所示。
CREATE TRIGGER trigger_name
ON {table_name | view_name}
[WITH ENCRYPTION]
{
{{FOR | AFTER}
{UPDATE}
[NOT FOR REPLICATION]
AS
[{IF UPDATE(column)
[{AND | OR} UPDATE(column)]
[…n]
| IF (COLUMNS_UPDATED() { bitwise_operator} updated_bitmask)
{comparison_operator}
Column_bitmask […n]
}]
Sql_statement […n]
}
}
代码中的主要参数说明如下所示:
- trigger_name:要创建触发器的名字,触发器的名字在当前数据库中必须是惟一,并且命名必须符合SQL的命名规则。
- table_name、view_name:分别指的是与触发器相关联的表或视图的名称。
- WITH ENCRYPTION:表示为了防止用户通过查询syscomments表获取触发器的代码,而对含有CREATE
TRIGGER文本的syscomments表进行加密。 - AFTER:表示在执行了UPDATE操作之后,才能够激活触发器,执行触发器中的SQL语句。
- FOR:表示仅能在表上创建的AFTER触发器。
- UPDATE:表示选择执行的是数据更新的操作。
- NOT FOR REPLICATION:表示当复制表时,触发器不能被执行。
- AS:其后面列出触发器将要执行的动作。
- IF UPDATE(column):用来测定对某一确定列是INSERT操作还是UPDATE操作。
- IF COLUMNS_UPDATED:用于检查列是被更新还是被插入,该操作仅在INSERT和UPDATE类型的触发器中被使用。
- bitwise_operator:代表位逻辑运算符,即“&”运算符。
- updated_bitmask:表示列的整位掩码。
- comparison_operator:表示比较操作符。可以是“=”、“>”或者“<”。
- Column_bitmask:表示在IF COLUMNS_UPDATED子句中,要测试是否被更新列的序号的掩码。
- Sql_statement:表示包含在触发器中的处理语句。
【上机实战】
创建触发器trig_update,当更改员工信息表(tb_ygxx)中的数据信息时,DBMS将输出字符串“数据成功修改!”,其具体实现的代码如下所示。
CREATE TRIGGER trig_update
ON tb_ygxx
AFTER UPDATE
AS
PRINT '数据成功修改!'
代码执行以后,触发器trig_update创建完成。此时通过UPDATE语句修改员工信息表(tb_ygxx)中的数据信息。
UPDATE tb_ygxx
SET 籍贯='吉林省辽源市'
WHERE 编号='1002'
代码执行以后,数据添加修改,并且输出“数据成功修改!”的提示信息,如图13.6所示。这说明,在更新员工信息表(tb_ygxx)中的数据信息之后,触发了触发器trig_update,提示信息是通过该触发器输出的。
图13.6 UPDATE触发器的简单应用
前面讲解的UPDATE触发器应用示例比较简单,下面再讲解一下复杂一些的UPDATE触发器应用。
创建触发器update_ygxx,当修改员工信息表(tb_ygxx)中的员工信息时,同时通过触发器update_ygxx将员工工资表(tb_yggz)中该员工的工资信息进行更新。为了便于读者进行比较,首先将员工信息表(tb_ygxx)和员工工资表(tb_yggz)中的数据信息显示出来。
显示员工信息表(tb_ygxx)中数据信息的实现代码如下所示。
select * from tb_ygxx
代码执行以后,运行结果如图13.7所示。
图13.7 员工信息表(tb_ygxx)中的数据信息
显示员工工资表(tb_yggz)中数据信息的实现代码如下所示。
select * from tb_yggz
代码执行以后,运行结果如图13.8所示。
图13.8 员工工资表(tb_yggz)中的数据信息
通过创建UPDATE触发器修改另一个数据表中数据信息的实现代码如下所示。
CREATE TRIGGER update_ygxx
ON tb_ygxx
AFTER UPDATE
AS
UPDATE tb_yggz
SET yggz=2200
WHERE ygid='1008'
代码执行以后,触发器被创建。此时通过下面的语句修改员工信息表(tb_ygxx)中的数据信息。
UPDATE tb_ygxx
SET 籍贯='吉林省辽源市'
WHERE 编号='1008'
代码执行以后,分别查看员工信息表(tb_ygxx)和员工工资表(tb_yggz)中的数据信息。
查看员工信息表(tb_ygxx)中数据信息的实现代码如下所示。
select * from tb_ygxx order by 编号
代码执行以后,其执行结果如图13.9所示。
图13.9 应用存储过程之后员工信息表(tb_ygxx)中的数据信息
查看员工工资表(tb_yggz)中数据信息的实现代码如下所示。
select * from tb_yggz order by ygid
代码执行以后,其执行结果如图13.10所示。
图13.10 应用存储过程之后员工工资表(tb_yggz)中的数据信息
从上面的执行结果中可以看出,在创建了update_ygxx触发器之后,当修改员工信息表(tb_ygxx)中的数据信息之后,触发了update_ygxx触发器,同时修改员工工资表(tb_yggz)中该员工的工资信息。
说明:应用UPDATE触发器,除了可以修改另一个数据表中的数据之外,还可以向另一个数据表中插入数据,或者删除另一个数据表中的数据信息。
12.2.3 DELETE触发器
当对数据表进行删除数据操作时触发DELETE触发器。当针对某一数据表创建DELETE触发器之后,在删除该数据表中的数据信息之后,可以对其他数据表执行插入数据、更新数据和删除数据等操作。
【语法说明】
创建DELETE触发器的语法结构如下所示。
CREATE TRIGGER trigger_name
ON {table_name | view_name}
[WITH ENCRYPTION]
{
{{FOR | AFTER }
{DELETE}
[NOT FOR REPLICATION]
AS
[{IF UPDATE(column)
[{AND | OR} UPDATE(column)]
[…n]
| IF (COLUMNS_UPDATED() { bitwise_operator} updated_bitmask)
{comparison_operator}
Column_bitmask […n]
}]
Sql_statement […n]
}
}
代码中的主要参数说明如下所示:
- trigger_name:要创建触发器的名字,触发器的名字在当前数据库中必须是惟一,并且命名必须符合SQL的命名规则。
- table_name、view_name:分别指的是与触发器相关联的表或视图的名称。
- WITH ENCRYPTION:表示为了防止用户通过查询syscomments表获取触发器的代码,而对含有CREATE
TRIGGER文本的syscomments表进行加密。 - AFTER:表示在执行了指定的DELETE操作之后,才能够激活触发器,执行触发器中的SQL语句。
- FOR:表示仅能在表上创建的AFTER触发器。
- DELETE:表示选择执行的是删除数据的操作。
- NOT FOR REPLICATION:表示当复制表时,触发器不能被执行。
- AS:其后面列出触发器将要执行的动作。
- IF UPDATE(column):用来测定对某一确定列是INSERT操作还是UPDATE操作。
- IF COLUMNS_UPDATED:用于检查列是被更新还是被插入,该操作仅在INSERT和UPDATE类型的触发器中被使用。
- bitwise_operator:代表位逻辑运算符,即“&”运算符。
- updated_bitmask:表示列的整位掩码。
- comparison_operator:表示比较操作符。可以是“=”、“>”或者“<”。
- Column_bitmask:表示在IF COLUMNS_UPDATED子句中,要测试是否被更新列的序号的掩码。
- Sql_statement:表示包含在触发器中的处理语句。
【上机实战】
创建触发器trig_delete,当删除员工信息表(tb_ygxx)中的数据信息时,DBMS将输出字符串“数据成功删除!”,其具体实现的代码如下所示。
CREATE TRIGGER trig_delete
ON tb_ygxx
AFTER DELETE
AS
PRINT '数据成功删除!'
代码执行以后,触发器trig_delete创建完成。此时通过DELETE语句删除员工信息表(tb_ygxx)中的数据信息。
DELETE tb_ygxx
FROM tb_ygxx
WHERE 编号='1002'
代码执行以后,数据添加删除,并且输出“数据成功删除!”的提示信息,如图13.11所示。这说明,在删除员工信息表(tb_ygxx)中的数据信息之后,触发了触发器trig_delete,提示信息是通过该触发器输出的。
图13.11 DELETE触发器的简单应用
前面讲解的DELETE触发器应用示例比较简单,下面再讲解一下复杂一些的DELETE触发器应用。
创建触发器delete_ygxx,当删除员工信息表(tb_ygxx)中的员工信息时,同时通过触发器delete_ygxx将员工工资表(tb_yggz)中该员工的工资信息删除。为了便于读者进行比较,首先将员工信息表(tb_ygxx)和员工工资表(tb_yggz)中的数据信息显示出来。
显示员工信息表(tb_ygxx)中数据信息的实现代码如下所示。
select * from tb_ygxx
代码执行以后,运行结果如图13.12所示。
图13.12 员工信息表(tb_ygxx)中的数据信息
显示员工工资表(tb_yggz)中数据信息的实现代码如下所示。
select * from tb_yggz
代码执行以后,运行结果如图13.13所示。
图13.13 员工工资表(tb_yggz)中的数据信息
通过创建DELETE触发器删除另一个数据表中数据信息的实现代码如下所示。
CREATE TRIGGER delete_ygxx
ON tb_ygxx
AFTER DELETE
AS
DELETE tb_yggz
FROM tb_yggz
WHERE ygid='1008'
代码执行以后,触发器被创建。此时通过下面的语句删除员工信息表(tb_ygxx)中的数据信息。
DELETE tb_ygxx
FROM tb_ygxx
WHERE 编号='1008'
代码执行以后,分别查看员工信息表(tb_ygxx)和员工工资表(tb_yggz)中的数据信息。
查看员工信息表(tb_ygxx)中数据信息的实现代码如下所示。
select * from tb_ygxx order by 编号
代码执行以后,其执行结果如图13.14所示。
图13.14 应用存储过程之后员工信息表(tb_ygxx)中的数据信息
查看员工工资表(tb_yggz)中数据信息的实现代码如下所示。
select * from tb_yggz order by ygid
代码执行以后,其执行结果如图13.15所示。
图13.15 应用存储过程之后员工工资表(tb_yggz)中的数据信息
从上面的执行结果中可以看出,在创建了delete_ygxx触发器之后,当删除员工信息表(tb_ygxx)中的数据信息之后,触发了delete_ygxx触发器,同时将员工工资表(tb_yggz)中该员工的工资信息删除。
说明:应用DELETE触发器,除了可以删除另一个数据表中的数据之外,还可以向另一个数据表中插入数据,或者修改另一个数据表中的数据信息。
12.3.4 INSTEAD OF触发器
使用INSTEAD OF触发器可以使不能被更新的视图支持更新操作。从前面讲解的知识可知,由多个表创建的视图不支持数据更新的操作。然而,使用INSTEAD OF触发器则能够实现这一功能。下面通过示例来具体说明INSTEAD OF触发器的应用。
【语法说明】
创建INSTEAD OF触发器的语法结构如下所示。
CREATE TRIGGER trigger_name
ON {table_name | view_name}
[WITH ENCRYPTION]
{
{INSTEAD OF}
{[DELETE][INSERT][UPDATE]}
[NOT FOR REPLICATION]
AS
[{IF UPDATE(column)
[{AND | OR} UPDATE(column)]
[…n]
| IF (COLUMNS_UPDATED() { bitwise_operator} updated_bitmask)
{comparison_operator}
Column_bitmask […n]
}]
Sql_statement […n]
}
}
代码中的主要参数说明如下所示:
- trigger_name:要创建触发器的名字,触发器的名字在当前数据库中必须是惟一,并且命名必须符合SQL的命名规则。
- table_name、view_name:分别指的是与触发器相关联的表或视图的名称。
- WITH ENCRYPTION:表示为了防止用户通过查询syscomments表获取触发器的代码,而对含有CREATE
TRIGGER文本的syscomments表进行加密。 - INSTEAD OF:指定触发器为INSTEAD OF触发器。每个表最多只能有一个INSTEAD OF触发器。
- DELETE、INSERT、UPDATE:表示要选择执行的操作。在选择操作时至少要包含3种操作类型中的任一种,当然也可以是3种操作语句的任意组合。
- NOT FOR REPLICATION:表示当复制表时,触发器不能被执行。
- AS:其后面列出触发器将要执行的动作。
- IF UPDATE(column):用来测定对某一确定列是INSERT操作还是UPDATE操作。
- IF COLUMNS_UPDATED:用于检查列是被更新还是被插入,该操作仅在INSERT和UPDATE类型的触发器中被使用。
- bitwise_operator:代表位逻辑运算符,即“&”运算符。
- updated_bitmask:表示列的整位掩码。
- comparison_operator:表示比较操作符。可以是“=”、“>”或者“<”。
- Column_bitmask:表示在IF COLUMNS_UPDATED子句中,要测试是否被更新列的序号的掩码。
- Sql_statement:表示包含在触发器中的处理语句。
【上机实战】
假设有一个学生信息表(s_temp)和学生成绩表(a_temp)。而视图view_stud则包含这两个数据表中的数据信息。现对视图view_stud创建触发器trig_s_Insert,实现直接向视图中插入数据信息的功能。
首先创建学生信息表(s_temp)和学生成绩表(a_temp)。
创建学生信息表(s_temp)的代码如下所示。
CREATE TABLE s_temp
(
sNo char(5),
sName char(10),
sSex char(2),
sClass char(20)
)
创建学生成绩表(a_temp)的代码如下所示。
CREATE TABLE a_temp
(
sNo char(5),
suName char(10),
suAchieve float,
sMark char(50),
)
创建视图view_stud,视图中包括学生编号(sNo)、姓名(sName)、科目(suName)和科目成绩(suAchieve)信息。
CREATE VIEW view_stud
AS
SELECT s_temp.sNo, s_temp.sName,a_temp.suName,
a_temp.suAchieve
FROM a_temp INNER JOIN s_temp
ON a_temp.sNo = s_temp.sNo
在创建触发器之前,通过INSERT语句向视图中插入数据。
INSERT INTO view_stud
VALUES('0607','李娜','计算机英语',85)
代码执行以后,DBMS提示“视图或函数不可更新”的提示信息,如图13.16所示。
图13.16 DBMS弹出的提示信息
因为修改会影响多个基表”的提示信息,说明在创建INSTEAD OF触发器之前,不允许向视图中插入数据。
创建INSTEAD OF触发器,实现通过视图更新数据的功能,如下面的代码所示。
CREATE TRIGGER trig_s_Insert
ON view_stud
INSTEAD OF INSERT
AS
BEGIN
BEGIN
INSERT INTO s_temp(sNo,sName)
SELECT sNo,sName
FROM INSERTED
END
BEGIN
INSERT INTO a_temp(sNo,suName,suAchieve)
SELECT sNo,suName,suAchieve
FROM INSERTED
END
END
代码执行以后,触发器被创建。通过INSERT语句向视图中插入数据信息。
INSERT INTO view_stud
VALUES('0607','李娜','计算机英语',85)
数据插入以后,查看视图中的数据信息。
SELECT * FROM view_stud
代码执行以后,运行结果如图13.17所示。
图13.17 查看视图中的数据
在INSTEAD OF触发器中也可以应用参数进行判断。
创建触发器trig_Update,实现在向视图view_stud中插入数据时,判断插入的学号(sNo)是否是06级的学生信息,如果不是06级的学生信息,则不允许插入,其实现的代码如下所示。
CREATE TRIGGER trig_Update
ON view_stud
INSTEAD OF INSERT
AS
BEGIN
DECLARE @SNO char(10)
SELECT @SNO=SUBSTRING(sNo,1,2)
FROM INSERTED
IF @SNO='06'
BEGIN
BEGIN
INSERT INTO s_temp(sNo,sName)
SELECT sNo,sName
FROM INSERTED
END
BEGIN
INSERT INTO a_temp(sNo,suName,suAchieve)
SELECT sNo,suName,suAchieve
FROM INSERTED
END
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR('插入的信息只能为06级学号的学生信息!',16,1)
END
END
代码执行以后,触发器被创建。通过INSERT语句向视图中插入数据信息。
INSERT INTO view_stud
VALUES('9708','赵立','计算机英语',90)
代码执行以后,DBMS提示“插入的信息只能为06级学号的学生信息!”的提示信息,如图13.18所示。
图13.18 DBMS弹出的提示信息
从上图中可以看出,在向视图中插入数据时,触发了触发器trig_Update,通过该触发器判断出输入的学号不是06级的学生,因此,触发器执行了数据回滚操作,数据插入不成功。
如果向视图中插入06级学号的学生信息。
INSERT INTO view_stud
VALUES('0608','赵刚','计算机英语',90)
代码执行后,数据插入成功,查看视图中的数据信息。
SELECT * FROM view_stud
代码执行以后,运行结果如图13.19所示。
图13.19 查看视图中的数据
12.3 Oracle中的触发器应用
与SQL Server一样,在Oracle数据库当中,创建触发器也是通过CREATE TRIGGER语句来实现。本节中将结合具体的示例来讲解,在Oracle数据库当中与创建触发器方面相关的知识。
12.3.1 创建INSERT触发器
当对数据表进行插入数据操作时触发INSERT触发器。
【语法说明】
在Oracle数据库当中,创建INSERT触发器的实现语句如下所示。
CREATE TRIGGER trigger_name
[BEFORE | AFTER] insert
ON table_reference
[FOR EACH ROW [WHEN trigger_condition]]
Triggerstatement
代码中的各项参数说明如下:
- trigger_name:表示触发器的名字。在Oracle当中,触发器名称具有单独的名称空间,因而触发器名称可以和表或存储过程的名称相同。
- BEFORE | AFTER:是指触发器是在数据修改前(BEFORE),还是修改后(AFTER)被调用执行。
- insert:表示触发器的事件。
- ON:表示的是子句,该子句包含了目标表的名称。
- FOR EACH ROW:表示每次插入、更新或删除数据时调用触发器。
- WHEN:是指可以定义的搜索条件,该参数是可选参数,用来限制调用触发器时的搜索范围。
- Triggerstatement:表示为触发器执行的SQL语句。
【上机实战】
创建触发器trig_insert,当向员工信息表(t_employ)中添加数据时,DBMS将输出字符串“数据成功添加”,其具体实现的代码如下所示。
create or replace trigger trig_insert
before insert on t_employ
for each row
begin
dbms_output.put_line('信息成功添加');
end trig_insert;
代码执行以后,触发器trig_insert创建完成。此时通过INSERT INTO语句向员工信息表(t_employ)中添加数据信息。
insert into t_employ
values('1011','刘娜','市场部',1600,100,300)
代码执行以后,数据添加成功,并且输出“数据成功添加”的提示信息,如图13.20所示。这说明,在向员工信息表(t_employ)中添加数据信息之后,触发了触发器trig_insert,提示信息是通过该触发器输出的。
图13.20 INSERT触发器的简单应用
前面讲解的INSERT触发器应用示例比较简单,下面再讲解一下复杂一些的INSERT触发器应用。
创建触发器insert_ygxxs,当向员工信息表(t_employ)中插入员工信息时,同时通过触发器insert_ygxxs将员工的扣款信息添加到员工扣款表(t_kq)当中。为了便于读者进行比较,首先将员工信息表(t_employ)和员工扣款表(t_kq)中的数据信息显示出来。
显示员工信息表(t_employ)中数据信息的实现代码如下所示。
select * from t_employ
代码执行以后,运行结果如图13.21所示。
图13.21 员工信息表(t_employ)中的数据信息
显示员工扣款表(t_kq)中数据信息的实现代码如下所示。
select * from t_kq
代码执行以后,运行结果如图13.22所示。
图13.22 员工扣款表(t_kq)中的数据信息
通过创建INSERT触发器向另一个数据表中插入数据的实现代码如下所示。
create or replace trigger trig_inserts
before insert on t_employ
for each row
begin
INSERT INTO t_kq
values('1007','5次','6次','无','无','2012年3月');
end trig_insert;
代码执行以后,触发器被创建。此时通过下面的语句向员工信息表(t_employ)中插入数据。
insert into t_employ
values('1007','刘宏','市场部',1800,100,300)
代码执行以后,分别查看员工信息表(t_employ)和员工扣款表(t_kq)中的数据信息。
查看员工信息表(t_employ)中数据信息的实现代码如下所示。
select * from t_employ
代码执行以后,其执行结果如图13.23所示。
图13.23 应用触发器之后员工信息表(t_employ)中的数据信息
查看员工扣款表(t_kq)中数据信息的实现代码如下所示。
select * from t_kq
代码执行以后,其执行结果如图13.24所示。
图13.24 应用触发器之后员工扣款表(t_kq)中的数据信息
从上面的执行结果中可以看出,在创建了insert_ygxxs触发器之后,当向员工信息表(t_employ)中插入数据之后,出发了insert_ygxxs触发器,同时将员工的扣款信息添加到员工扣款表(t_kq)当中。
说明:在Oracle数据库中应用INSERT触发器时,除了可以向另一个数据表中插入数据之外,还可以更新或删除另一个数据表中的数据信息。
12.3.2 创建UPDATE触发器
当对数据表进行修改数据操作时触发UPDATE触发器。
【语法说明】
在Oracle数据库当中,创建UPDATE触发器的实现语句如下所示。
CREATE TRIGGER trigger_name
[BEFORE | AFTER] update
ON table_reference
[FOR EACH ROW [WHEN trigger_condition]]
Triggerstatement
代码中的各项参数说明如下:
- trigger_name:表示触发器的名字。在Oracle当中,触发器名称具有单独的名称空间,因而触发器名称可以和表或存储过程的名称相同。
- BEFORE | AFTER:是指触发器是在数据修改前(BEFORE),还是修改后(AFTER)被调用执行。
- update:表示触发器的事件。
- ON:表示的是子句,该子句包含了目标表的名称。
- FOR EACH ROW:表示每次插入、更新或删除数据时调用触发器。
- WHEN:是指可以定义的搜索条件,该参数是可选参数,用来限制调用触发器时的搜索范围。
- Triggerstatement:表示为触发器执行的SQL语句。
【上机实战】
创建触发器trig_update,当修改员工信息表(t_employ)中的数据信息时,DBMS将输出字符串“数据成功修改”,其具体实现的代码如下所示。
create or replace trigger trig_update
before update on t_employ
for each row
begin
dbms_output.put_line('信息成功修改');
end trig_update;
代码执行以后,触发器trig_update创建完成。此时通过UPDATE语句修改员工信息表(t_employ)中的数据信息。
UPDATE t_employ
SET E_BM='采购部'
WHERE E_ID='1011'
代码执行以后,数据添加修改,并且输出“数据成功修改”的提示信息,如图13.25所示。这说明,在修改员工信息表(t_employ)中的数据信息时,触发了触发器trig_update,提示信息是通过该触发器输出的。
图13.25 UPDATE触发器的简单应用
前面讲解的UPDATE触发器应用示例比较简单,下面再讲解一下复杂一些的UPDATE触发器应用。
创建触发器update_ygxxs,当修改员工信息表(t_employ)中的数据信息时,同时通过触发器update_ygxxs在员工扣款表(t_kq)当中修改该员工的扣款信息。为了便于读者进行比较,首先将员工信息表(t_employ)和员工扣款表(t_kq)中的数据信息显示出来。
显示员工信息表(t_employ)中数据信息的实现代码如下所示。
select * from t_employ
代码执行以后,运行结果如图13.26所示。
图13.26 员工信息表(t_employ)中的数据信息
显示员工扣款表(t_kq)中数据信息的实现代码如下所示。
select * from t_kq
代码执行以后,运行结果如图13.27所示。
图13.27 员工扣款表(t_kq)中的数据信息
通过创建UPDATE触发器修改另一个数据表中数据信息的实现代码如下所示。
create or replace trigger update_ygxxs
before update on t_employ
for each row
begin
update t_kq
set e_cd_nums='3次',e_zt_nums='5次'
where e_id='1007';
end update_ygxxs;
代码执行以后,触发器被创建。此时通过下面的语句修改员工信息表(t_employ)中的数据信息。
update t_employ
set e_bm='人事部'
where e_id='1007'
代码执行以后,分别查看员工信息表(t_employ)和员工扣款表(t_kq)中的数据信息。
查看员工信息表(t_employ)中数据信息的实现代码如下所示。
select * from t_employ
代码执行以后,其执行结果如图13.28所示。
图13.28 应用触发器之后员工信息表(t_employ)中的数据信息
查看员工扣款表(t_kq)中数据信息的实现代码如下所示。
select * from t_kq
代码执行以后,其执行结果如图13.29所示。
图13.29 应用触发器之后员工扣款表(t_kq)中的数据信息
从上面的执行结果中可以看出,在创建了update_ygxxs触发器之后,当修改员工信息表(t_employ)中的数据信息之后,出发了update_ygxxs触发器,同时修改该员工的扣款信息。
说明:在Oracle数据库中应用UPDATE触发器时,除了可以修改另一个数据表中的数据之外,还可以向另一个数据表中插入数据,或者删除另一个数据表中的数据信息。
12.3.3 创建DELETE触发器
当对数据表进行删除数据操作时触发DELETE触发器。
【语法说明】
在Oracle数据库当中,创建DELETE触发器的实现语句如下所示。
CREATE TRIGGER trigger_name
[BEFORE | AFTER] delete
ON table_reference
[FOR EACH ROW [WHEN trigger_condition]]
Triggerstatement
代码中的各项参数说明如下:
- trigger_name:表示触发器的名字。在Oracle当中,触发器名称具有单独的名称空间,因而触发器名称可以和表或存储过程的名称相同。
- BEFORE | AFTER:是指触发器是在数据修改前(BEFORE),还是修改后(AFTER)被调用执行。
- delete:表示触发器的事件。
- ON:表示的是子句,该子句包含了目标表的名称。
- FOR EACH ROW:表示每次插入、更新或删除数据时调用触发器。
- WHEN:是指可以定义的搜索条件,该参数是可选参数,用来限制调用触发器时的搜索范围。
- Triggerstatement:表示为触发器执行的SQL语句。
【上机实战】
创建触发器trig_delete,当删除员工信息表(t_employ)中的数据信息时,DBMS将输出字符串“数据成功删除”,其具体实现的代码如下所示。
create or replace trigger trig_delete
before delete on t_employ
for each row
begin
dbms_output.put_line('信息成功删除');
end trig_delete;
代码执行以后,触发器trig_delete创建完成。此时通过DELETE语句删除员工信息表(t_employ)中的数据信息。
delete
from t_employ
WHERE E_ID='1011'
代码执行以后,数据添加删除,并且输出“数据成功删除”的提示信息,如图13.30所示。这说明,在删除员工信息表(t_employ)中的数据信息时,触发了触发器trig_delete,提示信息是通过该触发器输出的。
图13.30 DELETE触发器的简单应用
前面讲解的DELETE触发器应用示例比较简单,下面再讲解一下复杂一些的DELETE触发器应用。
创建触发器delete_ygxxs,当删除员工信息表(t_employ)中的数据信息时,同时通过触发器update_ygxxs在员工扣款表(t_kq)当中删除该员工的扣款信息。为了便于读者进行比较,首先将员工信息表(t_employ)和员工扣款表(t_kq)中的数据信息显示出来。
显示员工信息表(t_employ)中数据信息的实现代码如下所示。
select * from t_employ
代码执行以后,运行结果如图13.31所示。
图13.31 员工信息表(t_employ)中的数据信息
显示员工扣款表(t_kq)中数据信息的实现代码如下所示。
select * from t_kq
代码执行以后,运行结果如图13.32所示。
图13.32 员工扣款表(t_kq)中的数据信息
通过创建UPDATE触发器删除另一个数据表中数据信息的实现代码如下所示。
create or replace trigger delete_ygxxs
before delete on t_employ
for each row
begin
delete
from t_kq
where e_id='1007';
end update_ygxxs;
代码执行以后,触发器被创建。此时通过下面的语句删除员工信息表(t_employ)中的数据信息。
delete
from t_employ
where e_id='1007'
代码执行以后,分别查看员工信息表(t_employ)和员工扣款表(t_kq)中的数据信息。
查看员工信息表(t_employ)中数据信息的实现代码如下所示。
select * from t_employ
代码执行以后,其执行结果如图13.33所示。
图13.33 应用触发器之后员工信息表(t_employ)中的数据信息
查看员工扣款表(t_kq)中数据信息的实现代码如下所示。
select * from t_kq
代码执行以后,其执行结果如图13.34所示。
图13.34 应用触发器之后员工扣款表(t_kq)中的数据信息
从上面的执行结果中可以看出,在创建了delete_ygxxs触发器之后,当删除员工信息表(t_employ)中的数据信息之后,触发了delete_ygxxs触发器,同时删除该员工的扣款信息。
说明:在Oracle数据库中应用DELETE触发器时,除了可以删除另一个数据表中的数据之外,还可以向另一个数据表中插入数据,或者修改另一个数据表中的数据信息。
12.4 禁用与启用触发器
在Oracle数据库当中,可以使用ALTER语句实现禁用或者启用触发器。
【语法说明】
禁用与启用触发器的语法格式如下所示。
ALTER TABLE [user.]table {ENABLE | DISABLE} ALL TRIGGERS
代码中的ENABLE关键字表示启用触发器,DISABLE表示禁用触发器。
【上机实战】
禁用员工信息表(t_employ)中所有的触发器,其实现的代码如下所示。
ALTER TABLE t_employ DISABLE ALL TRIGGERS;
代码执行以后,员工信息表(t_employ)中所有的触发器都被禁用。
说明:触发器被禁用以后,针对数据表的触发器功能将全部失效。
下面再讲解一下如何启用触发器。
启用员工信息表(t_employ)中所有的触发器,其实现的代码如下所示。
ALTER TABLE t_employ ENABLE ALL TRIGGERS;
代码执行以后,员工信息表(t_employ)中所有的触发器都被启用。
12.5 删除触发器
在SQL中,使用DROP TRIGGER语句实现删除触发器的操作。
【语法说明】
DROP TRIGGER语句的语法格式如下所示。
DROP TRIGGER trigger_name
语句中的trigger_name表示的是要删除触发器的名称。
【上机实战】
删除触发器trig_inserts,其实现的代码如下所示。
DROP TRIGGER trig_inserts
代码执行以后,触发器trig_inserts被删除。