Oracle——15触发器

触发器可实现表间数据的一致性和完整性。当一个基表被修改时，相应的触发器会自动执行。对表数据的操作有插入、修改和删除，相应的维护数据的触发器也大致有insert、update和delete三种。

触发器的类型有三种：

（1）DML触发器：在对表进行DML操作的时候触发。

（2）替代触发器：由于在Oracle中不能直接对有两个以上表建立的视图进行DML操作，所以给出了替代触发器，它是Oracle专门为进行视图操作的一种处理方法。

（3）系统触发器：它可以在Oracle数据库系统的事件中进行触发，如数据库的开启和关闭。

触发器中的关键词：

触发器中有两个非常重要的关键词，一个是old，一个是new，old用于修饰操作完成前的值，new用于修饰完成后的值，可以代表一行。此外，只有行级的触发器才能使用这两个关键字。

 

一、利用SQL语句创建触发器

1、利用SQL创建触发器的语法格式：

 

		create [or replace] trigger [schema.]trigger_name
		{before | after | instead of}
		{delete [or insert][or update [of column,...n]]}
		on [schema.]table_name | view_name
		[for each row [when(condition)]]
		sql_statement[,...n]

 

其中，before：触发器在操作前执行；after：触发器在操作后执行；instead of：指定创建替代触发器。

delte、insert、update：指定触发事件，多个事件间用or连接。

of Column：指定在哪些列上执行update触发。

for each row：表示该触发器是行级的，只有行级的触发器才能使用old和new关键字。

示例代码：

 

			create or replace trigger moduleInsertTrigger after insert
			on t_module
			begin
				dbms_output.put_line('The Name is: ' || :new.name);
			end;

 

2、创建触发器的限制

（1）触发器代码大小必须小于32KB

（2）触发器中有效语句可以包括DML语句，但不能包括DDL语句；rollback、commit、savepoint也不能使用。但是，对于系统触发器可以使用create、alter、drop table和alter...compile语句。

（3）long、long raw和lob的限制：

a.不能插入数据到long或long raw

b.来自long或long raw的数据可以转换成字符型，但是不能超过32KB

c.使用long或long raw不能声明变量

d.在long或long raw列中不能使用:new和:parent

e.在lob中的:new变量不能修改

（4）引用包变量的限制：如果update或delete语句检测到当前的update冲突，则Oracle会执行rollback到savepoint上并重新启动更新。

3、触发器触发顺序

（1）执行before语句的触发器

（2）执行before语句的行级触发器

（3）执行DML语句

（4）执行after语句的行级触发器

（5）执行after语句的触发器

4、创建DML触发器

触发器有单独的名字空间，所以触发器名可以与表名和过程名相同，但在同一个schema中的触发器不能重名。

假设现在这样一个表t_module(id,name)，然后这里面的数据非常重要，我们希望能够保留它的删除记录，这样我们就可以给该表建立如下这样一个触发器：

 

	create or replace trigger module_delete_trigger after delete
	on t_module for each row
	begin
		insert into t_module_deleted values(:old.id, :old.name, current_timestamp);
	end;
	

 

5、创建替代触发器

由于视图有可能是由多个表进行关联而成，这种情况下，直接通过更新视图来更新基表是不可行的，这个时候我们就可以通过替代触发器来工作了。

示例代码：

 

	/*创建视图*/
		create or replace view t1_t2_view
		as
		select t1.name t1_name, t2.name t2_name, t2.id t2_id from table1 t1 join table2 t2 on t1.id=t2.t1id;
	/*创建对应的更新触发器*/
		create or replace trigger t1_t2_view_trigger instead of update
		on t1_t2_view for each row
		begin
		  update table2 t2 set t2.name = :new.t2_name where id = :new.t2_id; 
		end;

 

6、创建系统触发器

系统触发器是在进行数据库系统事件时进行触发，主要包括DDL语句，其语法格式如下：

 

		create or replace trigger [schema.]trigger_name
		{before | after}
		{DDL_EVENT_LIST | database_event_list}
		on {database | [schema.]SCHEMA}
		[when_clause]
		trigger_body

 

其中，DDL_EVENT_LIST表示一个或多个DDL事件，多个事件可以用or分开；

database_event_list表示一个说多个数据库事件，多个事件中间用or分开；

database表示数据库级触发器；schema表示用户级触发器。

示例代码如下：

 

		create or replace trigger create_trigger
		before create on schema
		begin
		  insert into table10(id, name) values (100, 'HELLO');
		end;

 

二、触发器的修改

和过程、视图一样，Oracle也提供了alter trigger语句，但是该语句只是用于重新编译现有触发器的，需要修改触发器需要使用create or replace trigger...语句。

三、触发器的删除

语法格式：

 

		drop trigger [schema.]trigger_name.