SQL触发器

Nov 24, 2016 | Language | 阅读

文章目录

简介
前提
DML触发器
INSTEAD OF触发器
1. 样例1
系统触发器
1. 语法
2. 样例1
其它语法

简介

触发器是一种特殊的存储过程，与表的关系密切，其特殊性主要体现在不需要用户调用，而是在对特定表（或列）进行特定类型的数据修改时激发。触发器与存储过程的差别如下：

触发器是自动执行，而存储过程需要显式调用才能执行。

触发器是建立在表或视图之上的，而存储过程是建立在数据库之上的。触发器用于实现数据库的完整性，触发器具有以下优点：

可以提供比CHECK 约束、FOREIGN KEY约束更灵活、更复杂、更强大的约束。

可对数据库中的相关表实现级联更改。

可以评估数据修改前后表的状态，并根据该差异采取措施。

强制表的修改要合乎业务规则。

前提

我有这么一张残念的表。我屮艸芔茻，截屏的时候把鼠标截进来了。

DML触发器

当数据库中发生数据操纵语言(DML)事件时将调用 DML 触发器。
DML 事件包括在指定表或视图中修改数据的 INSERT 语句、 UPDATE语句和DELETE 语句，DML 触发器可分为INSERT触发器、UPDATE 触发器和 DELETE触发器三类。

语法

语法格式：
CREATE [OR REPLACE] TRIGGER [<用户方案名>.] <触发器名> /触发器定义/
{ BEFORE∣AFTER∣INSTEAD OF } /指定触发时间/
{ DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]} /指定触发事件/
[OR { DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]}] ON {<表名>∣<视图名>} /指定表触发对象/
[ FOR EACH ROW [ WHEN(<条件表达式>) ] ] /指定触发级别/
PL/SQL语句块 /触发体 /

在行级触发器执行过程中，PL/SQL语句可以访问受触发器语句影响的每行的列值。“:OLD.列名”表示变化前的值，“:NEW.列名”表示变化后的值。有关DML触发器的语法说明，补充以下两点：
（1）创建触发器的限制
● 代码大小：触发器代码大小必须小于32 kB。
● 触发器中有效语句可以包括DML语句，但不能包括DDL语句。 ROLLBACK、COMMIT、SAVEPOINT也不能使用。
（2）触发器触发次序
● 执行BEFORE语句级触发器。
● 对于受语句影响的每一行，执行顺序为：执行BEFORE行级触发器−执行 DML语句−执行AFTER行级触发器。 ● 执行AFTER语句级触发器。
综上所述，可得创建DML 触发器的语法结构包括触发器定义和触发体两部分。触发器定义包含指定触发器名称、指定触发时间、指定触发事件、指定触发对象、指定触发级别等。触发体由PL/SQL语句块组成，它是触发器的执行部分。

样例1

一个简单的、无条件的、插入之后触发的触发器。

CREATE OR REPLACE TRIGGER trigInsertCourse
AFTER INSERT ON Course 
DECLARE 
	v_str varchar(20):='正在插入记录';
BEGIN DBMS_OUTPUT.PUT_LINE(v_str);
END;

触发一下：

1
2
3

set serveroutput on
insert into course
values('女装','7');

穿女装才是正义

样例2

我修改了数据表，以适应这个样例
1
2
alter table course
add cgrade int;

残念的表2

CREATE OR REPLACE TRIGGER trigInsertCourseGrade
BEFORE UPDATE ON course FOR EACH ROW 
DECLARE 
BEGIN 
IF :NEW.cid=7 THEN 
RAISE_APPLICATION_ERROR(-20001, '不能给变态加成绩'); 
END IF; 
END;

触发一下：

set serveroutput on
update course
set cgrade=99
where cid=7;

变态很伤心

样例3

CREATE OR REPLACE TRIGGER trigDELETECourseGrade
BEFORE DELETE ON course FOR EACH ROW 
DECLARE 
BEGIN 
IF :OLD.cid=7 THEN 
RAISE_APPLICATION_ERROR(-20003, '就算没成绩也不能没女装'); 
END IF; 
END;

触发一下：

1
2
3

set serveroutput on
delete from course
where cid=7;

女装即正义

INSTEAD OF触发器

INSTEAD OF触发器(替代触发器)，一般用于对视图的DML 触发。当视图由多个基表连接而成，则该视图不允许进行INSERT、 UPDATE和DELETE等 DML操作。在视图上编写INSTEAD OF触发器后，INSTEAD OF触发器只执行触发体中的PL/SQL语句，而不执行DML语句，这样就可以通过在INSTEAD OF触发器中编写适当的代码，进行对组成视图的各个基表的操作。

样例1

在stsys 数据库中创建视图view_TeacherCourse，包含教师名字，编号，课程名，课程编号，创建一个INSTEAD OF触发器 trigInsteadTC，当用户向course表或teacher表插入数据时，不执行激活触发器的插入语句，只执行触发器内部的插入语句。

CREATE VIEW view_TeacherCourse
AS 
SELECT a.tname,a.tid,b.cname,b.cid 
FROM teacher a, course b 
WHERE a.cid=b.cid ; 
CREATE TRIGGER trigInstead
INSTEAD OF INSERT ON view_TeacherCourse FOR EACH ROW 
DECLARE 
	v_ttitle varchar(20); v_grade int; 
BEGIN 
	v_ttitle:='讲师'; v_grade:=0; 
	INSERT INTO course VALUES(:NEW.cname, :NEW.cid, v_grade); 
	INSERT INTO teacher VALUES(:NEW.tname, :NEW.tid,:NEW.cid, v_ttitle); 
END;

触发一下：

通过INSERT语句向视图 view_TeacherCourse 插入一条记录。
1
INSERT INTO view_TeacherCourse VALUES('江阳', 5, '热力学', 8);

壮哉我大江阳

系统触发器

Oracle提供的系统触发器可以被数据定义语句DDL事件或数据库系统事件触发。DDL事件指CREATE、ALTER和DROP等。而数据库系统事件包括数据库服务器的启动（STARTUP）或关闭（SHUTDOWN），数据库服务器出错（SERVERERROR）等。

语法

语法格式：
CREATE OR REPLACE TRIGGER [<用户方案名>.] <触发器名> /触发器定义/
{ BEFORE︱AFTER } /指定触发时间/
{ ︱<数据库事件> } /指定触发事件/
ON { DATABASE︱[用户方案名.] SCHEMA }[when_clause] /指定触发对象/
PL/SQL语句块 /触发体/
● DDL事件：可以是一个或多个DDL事件，多个DDL事件之间用OR连接。DDL事件包括CREATE、ALTER、DROP、 TRUNCATE、GRANT、REVOKE、LOGON、RENAME、 COMMENT等。
●数据库事件：可以是一个或多个数据库事件，多个数据库事件之间用OR连接。数据库事件包括STARTUP、 SHUTDOWN、SERVERERROR等。
●DATABASE：数据库触发器，由数据库事件激发。
●SCHEMA：用户触发器，由DDL事件激发。其它选项与创建DML触发器语法格式相同。

样例1

CREATE TABLE DropObjects 
(
 ObjectName varchar2(30),
 ObjectType varchar2(20),
 DroppedDate date 
);
CREATE OR REPLACE TRIGGER trigDropObjects 
BEFORE DROP ON SYSTEM.SCHEMA 
BEGIN 
INSERT INTO DropObjects VALUES(ora_dict_obj_name, ora_dict_obj_type, SYSDATE);
END;

触发一下：

create table test
(
tid int primary key ,
tname varchar(20)
)
drop table test;
select * from DropObjects;

系统触发器

其它语法

删除触发器
drop trigger XXXX
禁用和启用触发器
ALTER TRIGGER XXX
DISABLE | ENABLE;

SQL