触发器

5.7 触发器

触发器（trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，触发器将被保存在数据库服务器中。任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制。
 

5.7.1 定义触发器

触发器又叫做事件-条件-动作（event-condition-ac-）规则。当特定的系统事件（如子对一个表的增、删、改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程。

建立触发器的一般格式：

CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
 
REFERENCING NEW |OLD ROW AS <变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件> ] <触发动作体>

下面对定义触发器的各部分语法进行详细说明。

（1）只有表的拥有者，即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器。触发器的具体数量由具体的关系数据库管理系统在设计时确定。

（2）触发器名
触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯的，并且触发器名和表名必须在同一模式下。

（3）表名
触发器只能定义在基本表上，不能定义在视图上。当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器，因此该表也称为触发器的目标表。

（4）触发事件
触发事件可以是 INSERT、 DELETE或 UPDATE，也可以是这几个事件的组合，如 INSERT OR DELETE等，还可以是 UPDATE OF<触发列，…>，即进一步指明修改哪些列时激活触发器。 AFTER/BEFORE是触发的时机。 AFTER表示在触发事件的操作执行之后激活触发器; BEFORE表示在触发事件的操作执行之前激活触发器。

（5）触发器类型
触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）

例如，假设在例5.11的 TEACHER表上创建了一个 AFTER UPDATE触发器，触发事件是 UPDATE语句：

 UPDATE TEACHER SET Deptno=5;

假设表 TEACHER有1000行，如果定义的触发器为语句级触发器，那么执行完UPDATE语句后触发动作体执行一次；如果是行级触发器，触发动作体将执行100次

（6）触发条件
触发器被激活时，只有当触发条件为真时触发动作体才执行，否则触发动作体不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。

（7）触发动作体
触发动作体既可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用。如果是行级触发器，用户可以在过程体中使用NEW和OLD引用 UPDATE/INSERT事件之后的新值和 UPDATE/DELETE事件之前的旧值；如果是语句级触发器，则不能在触发动作体中使用NEW或OLD进行引用。

如果触发动作体执行失败，激活触发器的事件（即对数据库的增、删、改操作）就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。
 

例 5.22 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

CREATE TRIGGER Student_Count
ON Student  	         
AFTER
INSERT
AS 
    INSERT INTO StudentInsertLog(Numbers)
	  SELECT COUNT(*) FROM Student

DELTA是一个关系名，其模式与Student相同，包含的元组是INSERT语句增加的元组。
 

5.7.1 激活触发器

触发器的执行是由触发事件激活，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，如多个 BEFORE触发器、多个 AFTER触发器等，同一个表上的多个触发器激活时遵循如下的执行顺序：
(1)执行该表上的 BEFORE触发器。
(2)激活触发器的SQL语句。
(3)执行该表上的AFTER触发器。

对于同一个表上的多个 BEFORE（AFTER）触发器，遵循“谁先创建谁先执行”的原则，即按照触发器创建的时间先后顺序执行。有些关系数据库管理系统是按照触发器名称的字母排序顺序执行触发器。
 

5.7.1 删除触发器

删除触发器的SQL语法如下：

DROP TRIGGER<触发器名>ON<表名>;

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。
触发器是一种功能强大的工具，但在使用时要慎重，因为在每次访问一个表时都可能触发一个触发器，这样会影响系统的性能。
 

8.3存储过程和函数

8.3.1 存储过程
 
存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程。

1.存储过程的优点
（1）运行效率高
（2）降低了客户机和服务器之间的通信量
（3）方便实施企业规则
 
2.存储过程的用户接口

1）创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,…])
AS <过程化SQL块>；

 

例8.8：利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

①首先要有账户表Account，建立并插入两条数据

drop table if exists Account;

create table Account(
	accountnum char(3),-- 编号
	total float -- 余额
);

insert into Account
values('101',50),('102',100);

select * from Account;

 
②创建存储过程

if (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
    drop procedure Proc_TRANSFER
go
create procedure Proc_TRANSFER 
@inAccount int,@outAccount int,@amount float
 /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
as
begin transaction TRANS   
   	declare	/*定义变量*/
	@totalDepositOut float,
	@totalDepositIn float,
	@inAccountnum int;
	 /*检查转出账户的余额 */     
	select @totalDepositOut = total from Account where accountnum = @outAccount;
	/*如果转出账户不存在或账户中没有存款*/
	if @totalDepositOut is null               	   
		begin
			print '转出账户不存在或账户中没有存款'
			rollback transaction TRANS; 	   /*回滚事务*/
			return;
		end;
	/*如果账户存款不足*/
	if @totalDepositOut < @amount     	
		begin
			print '账户存款不足'
			rollback transaction TRANS; 				/*回滚事务*/
			return;
		end
	/*检查转入账户的状态 */  
	select @inAccountnum = accountnum  from Account	where accountnum = @inAccount;
	/*如果转入账户不存在*/ 
	if @inAccountnum is null   		                       
		begin
			print '转入账户不存在'
		    rollback transaction TRANS; 	         	 		/*回滚事务*/
			return;
		end;
	/*如果条件都没有异常，开始转账。*/ 
	begin
		update Account set total = total - @amount where accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
		update Account set total = total + @amount where accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
		print '转账完成，请取走银行卡'
		commit transaction TRANS;                       	/* 提交转账事务 */
		return;
	end

 

(2）执行存储过程

T-SQL是用EXEC,标准SQL是CALL或者PERFORM

(3）修改存储过程

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameterdata_type } [= ] ] [ ,…n ]
AS { [ BEGIN ] sql_statement [ ; ] [ ,…n ] [ END ] }

(4)删除存储过程

drop procedure 过程名;

 

8.3.2 函数

1.函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;

2.函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,…]);

3.修改函数
重命名

   ALTER FUNCTION 过程名1 RENAME TO 过程名2;

重新编译

   ALTER FUNCTION 过程名 COMPILE;