触发器

 

范例： 只有在每个月的10日才允许办理新雇员入职与离职，其他时间不允许增加新雇员数据

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT OR DELETE

ON emp

DECLARE

v_currentdate VARCHAR(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'dd') INTO v_currentdate FROM dual ;

IF TRIM(v_currentdate)!='10' THEN

RAISE_APPLICATION_ERROR(-20008,'在每月的10号才允许办理入职手续！') ;

END IF ;

END ;

/

范例：向emp表之中增加新雇员信息，当前不是当月10日

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;

 

范例：在星期一、周末以及每天下班时间（每天9:00以前、18:00以后）后不允许更新emp数据表

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT OR DELETE OR UPDATE

ON emp

DECLARE

v_currentweak VARCHAR(20) ;

v_currenthour VARCHAR(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh24') INTO v_currentweak, v_currenthour FROM dual ;

IF TRIM(v_currentweak)='星期一' OR TRIM(v_currentweak)='星期六' OR TRIM(v_currentweak)='星期日' THEN

RAISE_APPLICATION_ERROR(-20008,'在周末及周一不允许更新emp数据表！') ;

ELSIF TRIM(v_currenthour)<'9' OR TRIM(v_currenthour)>'18' THEN

RAISE_APPLICATION_ERROR(-20009,'在下班时间不能够修改emp表数据！') ;

END IF ;

END ;

/

范例：假设当前日期时间为周日，发出以下增加数据操作

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;

 

范例：在每天12点以后，不允许修改雇员工资和佣金

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE UPDATE OF sal,comm

ON emp

DECLARE

v_currenthour VARCHAR(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'hh24') INTO v_currenthour FROM dual ;

IF TRIM(v_currenthour)>'12' THEN

RAISE_APPLICATION_ERROR(-20009,'每天12点以后不允许更新雇员工资、佣金。') ;

END IF ;

END ;

/

范例：当前时间已超过中午12点，更新emp表中的sal和comm字段

UPDATE emp SET sal=9000,comm=5000 WHERE empno=7369 ;

 

范例：每一位雇员都要根据其收入上缴所得税，假设所得税的上缴原则为：2000以下上缴3%、2000 ~ 5000上缴8%、5000以上上缴10%，现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、佣金、上缴所得税数据，并且在每次修改雇员表中sal和comm字段后可以自动更新记录。

DROP TABLE emp_tax PURGE ;

CREATE TABLE emp_tax (

empno NUMBER(4) ,

ename VARCHAR2(10) ,

sal NUMBER(7,2) ,

comm NUMBER(7,2) ,

tax NUMBER(7,2) ,

CONSTRAINT pk_empno PRIMARY KEY (empno) ,

CONSTRAINT fk_empno FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE CASCADE

) ;

CREATE OR REPLACE TRIGGER forbid_emp_trigger

AFTER UPDATE OR INSERT OF ename , sal , comm

ON emp

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION ; -- 触发器自治事务

CURSOR cur_emp IS SELECT * FROM emp ; -- 定义游标，找到每行纪录

v_empRow emp%ROWTYPE ; -- 保存emp的每行纪录

v_salary emp.sal%TYPE ; -- 计算总收入

v_empTax emp_tax.tax%TYPE ; -- 保存税收的数值

BEGIN

DELETE FROM emp_tax ; -- 清空emp_tax表的纪录

FOR v_empRow IN cur_emp LOOP

v_salary := v_empRow.sal + NVL(v_empRow.comm , 0) ; -- 计算总工资

IF v_salary < 2000 THEN

v_empTax := v_salary * 0.03 ; -- 上缴3%的税

ELSIF v_salary BETWEEN 2000 AND 5000 THEN

v_empTax := v_salary * 0.08 ; -- 上缴8%的税

ELSIF v_salary > 5000 THEN

v_empTax := v_salary * 0.1 ; -- 上缴10%的税

END IF ;

INSERT INTO emp_tax(empno,ename,sal,comm,tax) VALUES

(v_empRow.empno , v_empRow.ename , v_empRow.sal , v_empRow.comm , v_empTax) ;

END LOOP ;

COMMIT ; -- 提交事务

END ;

/

范例：向emp表中增加一条新的记录，而后查询emp_tax表记录

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (9898,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;

范例：增加完成后查询emp_tax表记录

SELECT * FROM emp_tax ;

 

范例：增加雇员信息时，其职位必须在已有职位之内选择，并且工资不能超过5000

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT

ON EMP

FOR EACH ROW

DECLARE

v_jobCount NUMBER ;

BEGIN

SELECT COUNT(empno) INTO v_jobCount FROM emp WHERE :new.job IN (

SELECT DISTINCT job FROM emp) ;

IF v_jobCount = 0 THEN -- 没有此职位信息

RAISE_APPLICATION_ERROR(-20008,'增加雇员的职位信息名称错误！') ;

ELSE

IF :new.sal > 5000 THEN

RAISE_APPLICATION_ERROR(-20008,'增加雇员的工资不得超过5000！') ;

END IF ;

END IF ;

END ;

/

范例：插入错误的数据

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','经理',SYSDATE,9000,500,7369,40) ;

 

范例：修改emp表的基本工资涨幅不能超过10%

CREATE OR REPLACE TRIGGER emp_update_trigger

BEFORE UPDATE OF sal

ON emp

FOR EACH ROW

BEGIN

IF ABS((:new.sal-:old.sal)/:old.sal) > 0.1 THEN

RAISE_APPLICATION_ERROR(-20008,'雇员工资修改幅度太大！') ;

END IF ;

END;

/

范例：将雇员编号是7369的雇员工资增长为5000

UPDATE emp SET sal=5000 WHERE empno=7369 ;

 

范例：不能删除所有10部门的雇员

CREATE OR REPLACE TRIGGER emp_delete_trigger

BEFORE DELETE

ON emp

FOR EACH ROW

BEGIN

IF :old.deptno=10 THEN

RAISE_APPLICATION_ERROR(-20008,:old.empno || '为10部门雇员，无法删除此部门雇员！') ;

END IF ;

END;

/

范例：删除雇员编号是7839的雇员信息（此雇员在10部门）

DELETE FROM emp WHERE empno=7839 ;

 

范例：错误的使用标识符“:new”和“:old”

CREATE OR REPLACE TRIGGER emp_error_trigger

BEFORE UPDATE

ON emp

FOR EACH ROW

DECLARE

v_empRow emp%ROWTYPE ;

BEGIN

v_empRow := :old ; -- 错误

END;

/

 

范例：错误的程序 —— 在触发器之中无法修改old数据

CREATE OR REPLACE TRIGGER emp_update_old_trigger

BEFORE UPDATE OF sal

ON emp

FOR EACH ROW

BEGIN

:old.sal := 100 ;  -- 错误，无法修改old数据

END;

/

 

范例：数据库创建脚本

DROP SEQUENCE member_sequence ;

DROP TABLE member PURGE ;

DROP TABLE membertemp PURGE ;

CREATE SEQUENCE member_sequence ;

CREATE TABLE member(

mid NUMBER ,

name VARCHAR2(30) ,

address VARCHAR2(50) ,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

CREATE TABLE membertemp AS SELECT * FROM member WHERE 1=2 ;

范例：在触发器之中修改new数据

CREATE OR REPLACE TRIGGER member_insert_trigger

BEFORE INSERT

ON membertemp  

FOR EACH ROW

BEGIN

DELETE FROM membertemp ;

INSERT INTO member(mid,name,address) VALUES (member_sequence.NEXTVAL,:new.name ,:new.address ) ;

END;

/

 

范例：向membertemp表中执行增加数据操作

INSERT INTO membertemp(name,address) VALUES ('李兴华','中国') ;

INSERT INTO membertemp(name,address) VALUES ('董  楠','MLDN') ;

COMMIT ;

 

范例：查询member表数据

SELECT * FROM member ;

 

范例：换种方式实现本程序的触发器

CREATE OR REPLACE TRIGGER member_insert_trigger

BEFORE INSERT

ON membertemp  

FOR EACH ROW

DECLARE

v_memberRow member%ROWTYPE ;

BEGIN

DELETE FROM membertemp ;

SELECT member_sequence.NEXTVAL INTO :new.mid FROM dual ;

v_memberRow.mid := :new.mid ;

v_memberRow.name := :new.name ;

v_memberRow.address := :new.address ;

INSERT INTO member VALUES v_memberRow ;

END;

/

 

范例：通过REFERENCING子句设置别名（修改雇员工资涨副触发器）

CREATE OR REPLACE TRIGGER emp_insert_trigger

BEFORE UPDATE OF sal

ON emp

REFERENCING old AS emp_old new AS emp_new

FOR EACH ROW

BEGIN

IF ABS((:emp_new.sal-:emp_old.sal)/:emp_old.sal) > 0.1 THEN

RAISE_APPLICATION_ERROR(-20008,'雇员工资涨副太大！') ;

END IF ;

END;

/

 

范例：在增加雇员时，判断雇员工资是否存在，如果工资为0则报错

CREATE OR REPLACE TRIGGER emp_insert_trigger

BEFORE INSERT

ON emp  

FOR EACH ROW

WHEN (new.sal = 0)

BEGIN

RAISE_APPLICATION_ERROR(-20008,:new.empno || '的工资为0，不符合工资规定！') ;

END;

/

范例：增加新雇员，工资为0 —— 不符合操作要求

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','经理',SYSDATE,0,500,7369,40) ;

 

范例：要求工资只能上涨，不能降低

CREATE OR REPLACE TRIGGER emp_sal_update_trigger

BEFORE UPDATE

ON emp

FOR EACH ROW

WHEN (new.sal<old.sal)

BEGIN

RAISE_APPLICATION_ERROR(-20008,:old.empno || '的工资少于其原本工资，无法更新！') ;

END;

/

范例：将7369的工资修改为300（原本为800，现在修改为300，属于降低工资，满足触发条件）

UPDATE emp SET sal=300 WHERE empno=7369 ;

 

范例：定义DEPT_LOG表

DROP TABLE dept_log PURGE ;

DROP SEQUENCE dept_log_seq ;

CREATE SEQUENCE dept_log_seq ;

CREATE TABLE dept_log (

logid NUMBER ,

type VARCHAR2(20) NOT NULL ,

deptno NUMBER(2) ,

logdate DATE ,

dname VARCHAR2(14) NOT NULL ,

loc VARCHAR2(13) NOT NULL ,

CONSTRAINT pk_logid PRIMARY KEY (logid)

) ;

 

范例：定义触发器，针对于不同的DML操作进行日志记录

CREATE OR REPLACE TRIGGER dept_update_trigger

BEFORE INSERT OR UPDATE OR DELETE

ON dept

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc)

VALUES (dept_log_seq.nextval,'INSERT',SYSDATE,:new.deptno,:new.dname,:new.loc) ;

ELSIF UPDATING THEN

INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc)

VALUES (dept_log_seq.nextval,'UPDATE',SYSDATE,:new.deptno,:new.dname,:new.loc) ;

ELSE -- 相当于DELETING

INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc)

VALUES (dept_log_seq.nextval,'DELETE',SYSDATE,:old.deptno,:old.dname,:old.loc) ;

END IF ;

END;

/

 

范例：向dept表中执行一些DML操作

INSERT INTO dept(deptno,dname,loc) VALUES (50,'MLDN','北京') ;

INSERT INTO dept(deptno,dname,loc) VALUES (60,'教学部','天津') ;

UPDATE dept SET dname='北京' WHERE deptno=60 ;

UPDATE dept SET dname='MLDNJAVA' WHERE deptno=50 ;

DELETE FROM dept WHERE deptno=60 ;

COMMIT ;

范例：查询dept_log数据表

SELECT * FROM dept_log ;

 

范例：定义三个针对于增加操作的触发器

CREATE OR REPLACE TRIGGER emp_insert_one

BEFORE INSERT

ON emp

FOR EACH ROW

BEGIN

DBMS_OUTPUT.put_line('执行第1个触发器（emp_insert_one）') ;

END ;

/

CREATE OR REPLACE TRIGGER emp_insert_two

BEFORE INSERT

ON emp

FOR EACH ROW

BEGIN

DBMS_OUTPUT.put_line('执行第2个触发器（emp_insert_two）') ;

END ;

/

CREATE OR REPLACE TRIGGER emp_insert_three

BEFORE INSERT

ON emp

FOR EACH ROW

BEGIN

DBMS_OUTPUT.put_line('执行第3个触发器（emp_insert_three）') ;

END ;

/

范例：编写增加数据操作

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','经理',SYSDATE,0,500,7369,40) ;

 

范例：修改触发器创建语法

CREATE OR REPLACE TRIGGER emp_insert_one

BEFORE INSERT

ON emp

FOR EACH ROW

BEGIN

DBMS_OUTPUT.put_line('执行第1个触发器（emp_insert_one）') ;

END ;

/

CREATE OR REPLACE TRIGGER emp_insert_two

BEFORE INSERT

ON emp

FOR EACH ROW

FOLLOWS emp_insert_one

BEGIN

DBMS_OUTPUT.put_line('执行第2个触发器（emp_insert_two）') ;

END ;

/

CREATE OR REPLACE TRIGGER emp_insert_three

BEFORE INSERT

ON emp

FOR EACH ROW

FOLLOWS emp_insert_two

BEGIN

DBMS_OUTPUT.put_line('执行第3个触发器（emp_insert_three）') ;

END ;

/

范例：定义一张数据表

DROP TABLE info PURGE ;

CREATE TABLE info(

id NUMBER ,

title VARCHAR2(50)

) ;

INSERT INTO info (id,title) VALUES (1,'www.mldnjava.cn') ; 

 

范例：为info表增加一个触发器

CREATE OR REPLACE TRIGGER info_trigger

BEFORE INSERT OR UPDATE OR DELETE

ON info

FOR EACH ROW

DECLARE

v_infocount NUMBER ;

BEGIN

SELECT COUNT(id) INTO v_infocount FROM info ;

END ;

/

范例：执行更新操作

UPDATE info SET id=2 ;

 

范例：验证复合触发器

CREATE OR REPLACE TRIGGER compound_trigger

FOR INSERT OR UPDATE OR DELETE ON dept

COMPOUND TRIGGER

BEFORE STATEMENT IS-- 语句执行前触发（表级）

BEGIN

DBMS_OUTPUT.put_line('1、BEFORE STATEMENT .') ;

END BEFORE STATEMENT ;

BEFORE EACH ROW IS-- 语句执行前触发（行级）

BEGIN

DBMS_OUTPUT.put_line('2、BEFORE EACH ROW .') ;

END BEFORE EACH ROW ;

AFTER STATEMENT IS-- 语句执行后触发（表级）

BEGIN

DBMS_OUTPUT.put_line('3、AFTER STATEMENT .') ;

END AFTER STATEMENT ;

AFTER EACH ROW IS-- 语句执行后触发（行级）

BEGIN

DBMS_OUTPUT.put_line('4、AFTER EACH ROW .') ;

END AFTER EACH ROW ;

END ;

/

范例：向dept表中增加一条新数据

INSERT INTO dept(deptno,dname,loc) VALUES (99,'MLDNJAVA','北京') ;

 

范例：定义复合触发器

CREATE OR REPLACE TRIGGER compound_trigger

FOR INSERT OR UPDATE OR DELETE ON dept

COMPOUND TRIGGER

BEFORE EACH ROW IS -- 语句执行前触发（行级）

BEGIN

IF INSERTING THEN

IF :new.dname IS NULL THEN

:new.dname := 'MLDNJAVA' ;

END IF ;

IF :new.loc IS NULL THEN

:new.loc := '中国' ;

END IF ;

END IF ;

END BEFORE EACH ROW ;

END ;

/

 

范例：增加一条部门信息

INSERT INTO dept(deptno) VALUES (99) ;

COMMIT ;

范例：查询更新后的dept表记录

SELECT * FROM dept ;

 

范例：定义触发器

CREATE OR REPLACE TRIGGER emp_compound_trigger

FOR INSERT OR UPDATE OR DELETE ON emp

COMPOUND TRIGGER

BEFORE STATEMENT IS -- 周末不允许更新

v_currentweak VARCHAR2(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'day') INTO v_currentweak FROM dual ;

IF TRIM(v_currentweak) IN ('星期六' , '星期日') THEN

RAISE_APPLICATION_ERROR(-20008,'在周末不允许更新emp数据表！') ;

END IF ;

END BEFORE STATEMENT ;

BEFORE EACH ROW IS

v_avgSal emp.sal%TYPE ;

BEGIN

IF INSERTING OR UPDATING THEN

:new.ename := UPPER(:new.ename) ;

:new.job := UPPER(:new.job) ;

END IF ;

IF INSERTING THEN

SELECT AVG(sal) INTO v_avgSal FROM emp ;

IF :new.sal > v_avgSal THEN

RAISE_APPLICATION_ERROR(-20009,'新进雇员工资不得高于公司平均工资！') ;

END IF ;

END IF ;

END BEFORE EACH ROW ;

END ;

/

范例：向雇员表中增加一条正确的数据

INSERT INTO emp (empno,ename,job,mgr,sal,comm,deptno,hiredate)

VALUES (9999,'mldn','manager',7566,1680,null,20,SYSDATE) ;

COMMIT ;

 

范例：查询emp表中9999雇员信息

SELECT * FROM emp WHERE empno=9999 ;

 

范例：向雇员表增加一条雇员信息，工资为5000（已经超过了平均工资）

INSERT INTO emp (empno,ename,job,mgr,sal,comm,deptno,hiredate)

VALUES (8888,'lixinghua','manager',7566,5000,null,20,SYSDATE) ;

范例：创建一张包含20部门雇员编号、姓名、职位、基本工资、部门编号、部门名称、位置的视图

CREATE OR REPLACE VIEW v_myview AS

SELECT e.empno , e.ename , e.job , e.sal , d.deptno , d.dname , d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno AND d.deptno=20 ;

 

范例：向视图插入一条数据

INSERT INTO v_myview (empno , ename, job , sal , deptno,dname,loc)

VALUES (6688, '魔乐' , 'CLERK' , 2000, 50 , '教学' , '北京') ;

 

范例：创建一个INSERT替代触发器，用于执行视图更新操作

CREATE OR REPLACE TRIGGER view_trigger

INSTEAD OF INSERT ON v_myview

FOR EACH ROW

DECLARE

v_empCount NUMBER ;

v_deptCount NUMBER ;

BEGIN

-- 判断要增加的雇员是否存在

SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:new.empno ;

-- 判断要增加的部门是否存在

SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno ;

IF v_deptCount = 0 THEN -- 部门不存在

INSERT INTO dept(deptno,dname,loc) VALUES (:new.deptno , :new.dname , :new.loc) ;

END IF ;

IF v_empCount = 0 THEN

INSERT INTO emp(empno,ename,job,sal,deptno)

VALUES (:new.empno , :new.ename , :new.job , :new.sal , :new.deptno) ;

END IF ;

END ;

/

范例：执行视图增加操作

INSERT INTO v_myview (empno , ename, job , sal , deptno,dname,loc)

VALUES (6688, '魔乐' , 'CLERK' , 2000, 50 , '教学' , '北京') ;

COMMIT ;

范例：查询emp表中是否存在了新增的6688记录

SELECT * FROM emp WHERE empno=6688 ;

 

范例：查询dept表中是否存在了新增的50部门记录

SELECT * FROM dept WHERE deptno=50 ;

 

范例：创建一个UPDATE替代触发器

CREATE OR REPLACE TRIGGER view_trigger

INSTEAD OF UPDATE ON v_myview

FOR EACH ROW

BEGIN

UPDATE emp SET ename=:new.empno , job=:new.job , sal=:new.sal WHERE empno=:new.empno ;

UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno ;

END ;

/

 

范例：更新视图信息

UPDATE v_myview SET ename='史密思',sal=2000,dname='调研部' WHERE empno=7369 ;

COMMIT ;

 

范例：查询v_myview视图

SELECT * FROM v_myview ;

 

范例：查询emp表中7369雇员信息是否被更新

SELECT * FROM emp WHERE empno=7369 ;

 

范例：查询dept表中20部门的信息是否被更新

SELECT * FROM dept WHERE deptno=20 ;

 

范例：创建一个DELETE替代触发器

CREATE OR REPLACE TRIGGER view_trigger

INSTEAD OF DELETE ON v_myview

FOR EACH ROW

DECLARE

v_empCount NUMBER ;

BEGIN

DELETE FROM emp WHERE empno=:old.empno ;

SELECT COUNT(empno) INTO v_empCount FROM emp WHERE deptno=:old.deptno ;

IF v_empCount = 0 THEN -- 此部门没有雇员

DELETE FROM dept WHERE deptno=:old.deptno ;

END IF ;

END ;

/

范例：删除视图之中所有20部门的雇员

DELETE FROM v_myview WHERE deptno=20 ;

COMMIT ;

 

范例：将三个不同功能的替代触发器变为一个替代触发器

CREATE OR REPLACE TRIGGER view_trigger

INSTEAD OF INSERT OR UPDATE OR DELETE ON v_myview

FOR EACH ROW

DECLARE

v_empCount NUMBER ;

v_deptCount NUMBER ;

BEGIN

IF INSERTING THEN

-- 判断要增加的雇员是否存在

SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:new.empno ;

-- 判断要增加的部门是否存在

SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno ;

IF v_deptCount = 0 THEN -- 部门不存在

INSERT INTO dept(deptno,dname,loc)

VALUES (:new.deptno , :new.dname , :new.loc) ;

END IF ;

IF v_empCount = 0 THEN

INSERT INTO emp(empno,ename,job,sal,deptno)

VALUES (:new.empno , :new.ename , :new.job , :new.sal , :new.deptno) ;

END IF ;

ELSIF UPDATING THEN

UPDATE emp SET ename=:new.empno , job=:new.job , sal=:new.sal WHERE empno=:new.empno ;

UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno ;

ELSIF DELETING THEN

DELETE FROM emp WHERE empno=:old.empno ;

SELECT COUNT(empno) INTO v_empCount FROM emp WHERE deptno=:old.deptno ;

IF v_empCount = 0 THEN -- 此部门没有雇员

DELETE FROM dept WHERE deptno=:old.deptno ;

END IF ;

ELSE

NULL ;

END IF ;

END ;

/

 

范例：定义嵌套表

-- 1、定义复合类型

DROP TYPE project_nested ;

DROP TYPE project_type ;

CREATE OR REPLACE TYPE project_type AS OBJECT(

projectid NUMBER ,

projectname VARCHAR2(50) ,

projectfunds NUMBER ,

pubdate DATE

) ;

/

-- 2、定义嵌套表类型

CREATE OR REPLACE TYPE project_nested AS TABLE OF project_type NOT NULL ;

/

-- 3、创建嵌套表类型的数据表

DROP TABLE department PURGE ;

CREATE TABLE department (

did NUMBER ,

deptname VARCHAR2(50) NOT NULL ,

projects project_nested ,

CONSTRAINT pk_did PRIMARY KEY(did)

) NESTED TABLE projects STORE AS projects_nsted_table ;

-- 4、增加测试数据

INSERT INTO department(did,deptname,projects) VALUES (10,'魔乐科技' ,

project_nested(

project_type(1,'Java实战开发' , 8900 , TO_DATE('2004-09-27','yyyy-mm-dd')),

project_type(2,'Android实战开发' , 13900 ,TO_DATE('2010-07-19','yyyy-mm-dd'))

)) ;

INSERT INTO department(did,deptname,projects) VALUES (20,'MLDN出版部' ,

project_nested(

project_type(10,'《Java开发实战经典》' , 79.8 , TO_DATE('2008-08-13','yyyy-mm-dd')) ,

project_type(11,'《Java Web开发实战经典》' , 69.8 , TO_DATE('2010-08-27','yyyy-mm-dd')) ,

project_type(12,'《Android开发实战经典》', 88 , TO_DATE('2012-03-19','yyyy-mm-dd'))

)) ;

COMMIT ;

范例：创建一张只包含10部门信息的视图，在此视图之中存在嵌套表类型projects列。

CREATE OR REPLACE VIEW v_department10

AS

SELECT did,deptname,projects

FROM department

WHERE did=10 ;

 

范例：查看视图中的嵌套表数据

SELECT * FROM TABLE (SELECT projects FROM v_department10) ;

 

范例：对视图中的嵌套表执行增加数据操作 —— 错误

INSERT INTO TABLE (SELECT projects FROM v_department10)

VALUES (3,'Java高端人才培养',8000,TO_DATE('2013-09-19','yyyy-mm-dd')) ;

 

范例：对视图中的嵌套表执行修改数据操作 —— 错误

UPDATE TABLE (SELECT projects FROM v_department10) pro

SET VALUE(pro) = project_type(2,'Android高级应用',3000,TO_DATE('2013-06-06','yyyy-mm-dd'))

WHERE pro.projectid=2 ;

 

范例：对视图中的嵌套表执行删除数据操作 —— 错误

DELETE FROM TABLE(

SELECT projects FROM v_department10) pro

WHERE pro.projectid=2 ;

范例：定义替代触发器实现对视图中的嵌套表数据更新

CREATE OR REPLACE TRIGGER nested_trigger

INSTEAD OF INSERT OR UPDATE OR DELETE

ON NESTED TABLE projects OF v_department10

DECLARE

BEGIN

IF INSERTING THEN

INSERT INTO TABLE (SELECT projects FROM department WHERE did=:parent.did)

VALUES (:new.projectid,:new.projectname,:new.projectfunds,:new.pubdate) ;

ELSIF UPDATING THEN

UPDATE TABLE (SELECT projects FROM department WHERE did=:parent.did) pro

SET VALUE(pro) = project_type(:new.projectid,:new.projectname,:new.projectfunds,:new.pubdate)

WHERE pro.projectid=:old.projectid ;

ELSIF DELETING THEN

DELETE FROM TABLE(

SELECT projects FROM department WHERE did=:parent.did) pro

WHERE pro.projectid=:old.projectid ;

END IF ;

END ;

/

 

范例：禁止c##scott用户的所有DDL操作

CREATE OR REPLACE TRIGGER scott_forbid_trigger

BEFORE DDL

ON SCHEMA

BEGIN

RAISE_APPLICATION_ERROR(-20007,'scott用户禁止使用任何的DDL操作！') ;

END ;

/

 

范例：创建一个序列

CREATE SEQUENCE mldn_seq ;

范例：数据库对象操作日志记录表创建脚本

DROP TABLE object_log PURGE ;

DROP SEQUENCE object_log_seq ;

CREATE SEQUENCE object_log_seq ;

CREATE TABLE object_log (

oid NUMBER CONSTRAINT pk_oid PRIMARY KEY,

username VARCHAR2(50) NOT NULL ,

operatedate DATE NOT NULL ,

objecttype VARCHAR2(50) NOT NULL ,

objectowner VARCHAR2(50) NOT NULL

) ;

范例：编写触发器实现对数据库对象操作的日志记录

CREATE OR REPLACE TRIGGER object_trigger

AFTER CREATE OR DROP OR ALTER

ON DATABASE

DECLARE

BEGIN

INSERT INTO c##scott.object_log(oid,username,operatedate,objecttype,objectowner) VALUES

   (c##scott.object_log_seq.nextval,ORA_LOGIN_USER,

    SYSDATE,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_OWNER) ;

END ;

/

范例：禁止修改emp数据表的empno（主键）列和deptno（外键）列的定义结构

SELECT * FROM all_tab_columns

WHERE table_name='EMP' AND owner='C##SCOTT';

 

范例：在c##scott用户中定义参数游标

CREATE OR REPLACE TRIGGER emp_alter_trigger

BEFORE ALTER

ON SCHEMA

DECLARE

-- 操作的所有者以及操作的表名称由外部传递

CURSOR emp_column_cur(p_tableOwner all_tab_columns.owner%TYPE , p_tableName all_tab_columns.table_name%TYPE) IS

SELECT column_name FROM all_tab_columns

WHERE owner=p_tableOwner AND table_name=p_tableName ;

BEGIN

IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN -- 如果操作的是数据表

FOR empColumnRow IN emp_column_cur(ORA_DICT_OBJ_OWNER , ORA_DICT_OBJ_NAME) LOOP

IF ORA_IS_ALTER_COLUMN(empColumnRow.column_name) THEN

-- empno字段要被修改

IF empColumnRow.column_name = 'EMPNO' THEN

RAISE_APPLICATION_ERROR(-20007,'empno字段不允许修改！') ;

END IF ;

-- deptno字段要被修改

IF empColumnRow.column_name = 'DEPTNO' THEN

RAISE_APPLICATION_ERROR(-20008,'deptno字段不允许修改！') ;

END IF ;

END IF ;

IF ORA_IS_DROP_COLUMN(empColumnRow.column_name) THEN

-- empno字段要被删除

IF empColumnRow.column_name = 'EMPNO' THEN

RAISE_APPLICATION_ERROR(-20009,'empno字段不允许删除！') ;

END IF ;

-- deptno字段要被删除

IF empColumnRow.column_name = 'DEPTNO' THEN

RAISE_APPLICATION_ERROR(-20010,'deptno字段不允许删除！') ;

END IF ;

END IF ;

END LOOP ;

END IF ;

END ;

/

范例：修改empno字段

ALTER TABLE emp MODIFY (empno NUMBER(6)) ;

 

范例：删除deptno字段

ALTER TABLE emp DROP COLUMN deptno ;

 

范例：编写user_log数据表创建脚本

DROP SEQUENCE user_log_seq ;

DROP TABLE user_log PURGE ;

CREATE SEQUENCE user_log_seq ;

CREATE TABLE user_log (

logid NUMBER CONSTRAINT pk_logid PRIMARY KEY ,

username VARCHAR2(50) NOT NULL ,

logondate DATE ,

logoffdate DATE ,

ip VARCHAR2(20) ,

logtype VARCHAR2(20)

) ;

 

范例：监控用户登录触发器 —— logon_trigger

CREATE OR REPLACE TRIGGER logon_trigger

AFTER LOGON

ON DATABASE

BEGIN

INSERT INTO user_log(logid,username,logondate,ip,logtype)

VALUES (user_log_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_CLIENT_IP_ADDRESS,'LOGON')  ;

END ;

/

 

范例：监控用户注销触发器 —— logoff_trigger

CREATE OR REPLACE TRIGGER logoff_trigger

BEFORE LOGOFF

ON DATABASE

BEGIN

INSERT INTO user_log(logid,username,logoffdate,ip,logtype)

VALUES (user_log_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_CLIENT_IP_ADDRESS,'LOGOFF')  ;

END ;

/

 

范例：回到sys用户下，查询user_log数据表

CONN sys/change_on_install AS SYSDBA ;

SELECT * FROM user_log ;

 

范例：定义数据库事件记录表

DROP TABLE db_event_log PURGE ;

DROP SEQUENCE db_event_log_seq ;

CREATE SEQUENCE db_event_log_seq ;

CREATE TABLE db_event_log (

eventId NUMBER CONSTRAINT pk_eventid PRIMARY KEY ,

eventType VARCHAR2(50) NOT NULL ,

eventDate DATE NOT NULL ,

eventUser VARCHAR2(50) NOT NULL

) ;

 

范例：编写触发器 —— 启动之后触发

CREATE OR REPLACE TRIGGER startup_trigger

AFTER STARTUP

ON DATABASE

BEGIN

INSERT INTO db_event_log(eventId,eventType,eventDate,eventUser)

VALUES (db_event_log_seq.nextval,'STARTUP',SYSDATE,ORA_LOGIN_USER) ;

COMMIT ;

END ;

/

 

范例：编写触发器 —— 关闭之前触发

CREATE OR REPLACE TRIGGER shutdown_trigger

BEFORE SHUTDOWN

ON DATABASE

BEGIN

INSERT INTO db_event_log(eventId,eventType,eventDate,eventUser)

VALUES (db_event_log_seq.nextval,'SHUTDOWN',SYSDATE,ORA_LOGIN_USER) ;

COMMIT ;

END ;

/

范例：查询db_event_log数据表记录

SHUTDOWN ABORT

STARTUP

SELECT * FROM db_event_log ;

 

范例：创建一张记录错误信息的数据表 —— db_error

DROP SEQUENCE db_error_seq ;

DROP TABLE db_error PURGE ;

CREATE SEQUENCE db_error_seq ;

CREATE TABLE db_error (

eid NUMBER CONSTRAINT pk_eid PRIMARY KEY ,

username VARCHAR2(50) ,

errorDate DATE ,

dbname VARCHAR2(50) ,

content CLOB

) ;

 

范例：定义数据库错误触发器

CREATE OR REPLACE TRIGGER error_trigger

AFTER SERVERERROR ON DATABASE

BEGIN

INSERT INTO db_error(eid,username,errorDate,dbname,content)

VALUES (db_error_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_DATABASE_NAME,

DBMS_UTILITY.format_error_stack) ;

END ;

/

范例：使用c##scott用户登录，查看user_triggers数据字典

SELECT trigger_name,status,trigger_type,status,table_name,triggering_event,trigger_body FROM user_triggers ;

 

范例：将emp_alter_trigger触发器修改为禁用状态

ALTER TRIGGER emp_alter_trigger DISABLE ;

范例：查询数据字典表，确定触发器状态

SELECT trigger_name,status FROM user_triggers ;

 

范例：禁用触发器代码结构

CREATE OR REPLACE TRIGGER emp_update_trigger

BEFORE INSERT OR UPDATE OR DELETE

ON dept

DISABLE

FOR EACH ROW

BEGIN

NULL ;

END;

/

 

范例：启用emp表的全部触发器

ALTER TABLE emp ENABLE ALL TRIGGERS;

 

范例：删除forbid_emp_trigger触发器

DROP TRIGGER emp_alter_trigger ;

范例：在每月10号允许办理新近人员入职，同时入职的新雇员工资不能够超过公司的平均工资

CREATE OR REPLACE PROCEDURE emp_update_date_proc

AS

v_currentdate VARCHAR2(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'dd') INTO v_currentdate FROM dual ;

IF TRIM(v_currentdate)!='10' THEN

RAISE_APPLICATION_ERROR(-20008,'在每月的10号才允许办理入职手续！') ;

END IF ;

END;

/

CREATE OR REPLACE FUNCTION emp_avg_sal

RETURN NUMBER

AS

v_avg_salary emp.sal%TYPE ;

BEGIN

SELECT AVG(sal) INTO v_avg_salary FROM emp ;

RETURN v_avg_salary ;

END;

/

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT

ON emp

FOR EACH ROW

BEGIN

emp_update_date_proc ; -- 调用过程

IF :new.sal>emp_avg_sal() THEN -- 调用函数

RAISE_APPLICATION_ERROR(-20009,'新进雇员工资不得高于公司平均工资！') ;

END IF ;

END ;

/