触发器的功能主要有:
1. 完成表的变更校验(当表的数据发生增、删、改时,提供验证逻辑)
2. 自动数据库维护
3.控制数据库管理活动
通过调试代码,可以增强对各知识点的理解。
--代码15.14 在触发器中使用自治事务
CREATE OR REPLACE TRIGGER t_emp_comm
BEFORE UPDATE ON emp --触发器作用的表对象以及触发的条件和触发的动作
FOR EACH ROW --行级别的触发器
WHEN(NEW.comm>OLD.comm) --触发器条件
DECLARE
v_comm NUMBER; --语句块的声明区
PRAGMA AUTONOMOUS_TRANSACTION; --自治事务
BEGIN
IF UPDATING ('comm') THEN --使用条件谓词判断是否是comm列被更新
v_comm := :NEW.comm - :OLD.comm; --记录工资的差异
DELETE FROM emp_history
WHERE empno = :OLD.empno; --删除emp_history中旧表记录
INSERT INTO emp_history --向表中插入新的记录
VALUES (:OLD.empno, :OLD.ename, :OLD.job, :OLD.mgr, :OLD.hiredate,
:OLD.sal, :OLD.comm, :OLD.deptno);
UPDATE emp_history --更新薪资值
SET comm = v_comm
WHERE empno = :NEW.empno;
END IF;
COMMIT; --提交结束自治事务
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; --发生任何意外回滚自治事务
END;
select * from emp_history;
truncate table emp_history;
update emp set comm=comm*1.2 where deptno=20;
rollback;
SELECT * FROM emp WHERE deptno=20;
--15.3.2 定义替代触发器
--创建视图emp_dept视图
CREATE OR REPLACE VIEW scott.emp_dept (empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
dname,
loc
)
AS
SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,
emp.comm, emp.deptno, dept.dname, dept.loc
FROM dept, emp
WHERE ((dept.deptno = emp.deptno));
--实现替代触发器(发现的问题:触发器执行插入操作时,主键重复时,未报错)
CREATE OR REPLACE TRIGGER t_dept_emp
INSTEAD OF INSERT ON emp_dept --在视图emp_dept上创建INSTEAD OF触发器
REFERENCING NEW AS n --指定谓词别名
FOR EACH ROW --行级触发器
DECLARE
v_counter INT; --计数器统计变量
BEGIN
SELECT COUNT (*)
INTO v_counter
FROM dept
WHERE deptno = :n.deptno; --判断在dept表中是否存在相应的记录
IF v_counter = 0 --如果不存在该dept记录
THEN
INSERT INTO dept VALUES (:n.deptno, :n.dname, :n.loc); --向dept表中插入新的部门记录
END IF;
SELECT COUNT (*) --判断emp表中是否存在员工记录
INTO v_counter
FROM emp
WHERE empno = :n.empno;
IF v_counter = 0 --如果不存在,则向emp表中插入员工记录
THEN
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal,
comm, deptno
)
VALUES (:n.empno, :n.ename, :n.job, :n.mgr, :n.hiredate, :n.sal,
:n.comm, :n.deptno
);
END IF;
END;
SELECT * FROM dept;
SELECT * FROM emp_dept;
INSERT INTO emp_dept
(empno, ename, job, mgr, hiredate, sal, comm, deptno,
dname, loc
)
VALUES (8001, '龙太郎', '神职', NULL, TRUNC (SYSDATE), 5000, 300, 82,
'神庙', '龙山'
);
--代码15.16 Update替代触发器
CREATE OR REPLACE TRIGGER t_dept_emp_update
INSTEAD OF UPDATE ON emp_dept --在视图emp_dept上创建INSTEAD OF触发器
REFERENCING NEW AS n OLD AS o --指定谓词别名
FOR EACH ROW --行级触发器
DECLARE
v_counter INT; --计数器统计变量
BEGIN
SELECT COUNT (*)
INTO v_counter
FROM dept
WHERE deptno = :o.deptno; --判断在dept表中是否存在相应的记录
IF v_counter >0 --如果存在,则更新dept表
THEN
UPDATE dept SET dname=:n.dname,loc=:n.loc WHERE deptno=:o.deptno;
END IF;
SELECT COUNT (*) --判断emp表中是否存在员工记录
INTO v_counter
FROM emp
WHERE empno = :n.empno;
IF v_counter > 0 --如果存在,则更新emp表
THEN
UPDATE emp SET ename=:n.ename,job=:n.job,mgr=:n.mgr, hiredate=:n.hiredate,sal=:n.sal,
comm=:n.comm, deptno=:n.deptno WHERE empno=:o.empno;
END IF;
END;
--代码15.17 Delete替代触发器
CREATE OR REPLACE TRIGGER t_dept_emp_delete
INSTEAD OF DELETE ON emp_dept --在视图emp_dept上创建INSTEAD OF触发器
REFERENCING OLD AS o --指定谓词别名
FOR EACH ROW --行级触发器
BEGIN
DELETE FROM emp WHERE empno=:o.empno; --删除emp表
DELETE FROM dept WHERE deptno=:o.deptno; --删除dept表
END;
SELECT * FROM emp_dept WHERE empno=8000;
DELETE FROM emp_dept WHERE empno=8000;
--代码15.18 t_emp_dept替代触发器完整示例
CREATE OR REPLACE TRIGGER t_emp_dept
INSTEAD OF UPDATE OR INSERT OR DELETE ON emp_dept
REFERENCING NEW AS n OLD AS o --指定谓词别名
FOR EACH ROW --行级触发器
DECLARE
v_counter INT; --计数器统计变量
BEGIN
SELECT COUNT (*)
INTO v_counter
FROM dept
WHERE deptno = :o.deptno; --判断在dept表中是否存在相应的记录
IF v_counter >0 --如果存在,则更新dept表
THEN
CASE
WHEN UPDATING THEN
UPDATE dept SET dname=:n.dname,loc=:n.loc WHERE deptno=:o.deptno;
WHEN INSERTING THEN
INSERT INTO dept VALUES (:n.deptno, :n.dname, :n.loc);
WHEN DELETING THEN
DELETE FROM dept WHERE deptno=:o.deptno; --删除dept表
END CASE;
END IF;
SELECT COUNT (*) --判断emp表中是否存在员工记录
INTO v_counter
FROM emp
WHERE empno = :n.empno;
IF v_counter > 0 --如果存在,则更新emp表
THEN
CASE
WHEN UPDATING THEN
UPDATE emp SET ename=:n.ename,job=:n.job,mgr=:n.mgr, hiredate=:n.hiredate,sal=:n.sal,
comm=:n.comm, deptno=:n.deptno WHERE empno=:o.empno;
WHEN INSERTING THEN
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal,
comm, deptno
)
VALUES (:n.empno, :n.ename, :n.job, :n.mgr, :n.hiredate, :n.sal,
:n.comm, :n.deptno
);
WHEN DELETING THEN
DELETE FROM emp WHERE empno=:o.empno;
END CASE;
END IF;
END;
SELECT * FROM emp;
--代码15.19 创建嵌套表视图
--创建用于嵌套表的对象类型
CREATE OR REPLACE TYPE emp_obj AS OBJECT(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(10),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(11,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
--创建嵌套表类型
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_obj;
--创建嵌套表视图,MULTISET必须与CAST一起使用
CREATE OR REPLACE VIEW dept_emp_view AS
SELECT deptno,dname,loc,
CAST(MULTISET(SELECT * FROM emp WHERE deptno=dept.deptno) AS emp_tab_type) emplst
FROM dept;
drop view dept_emp_view;
drop type emp_tab_type;
drop type emp_obj;
SELECT * FROM dept_emp_view where deptno=10;
select * from emp;
--不能在嵌套表视图列中执行DML
INSERT INTO TABLE (SELECT emplst
FROM dept_emp_view
WHERE deptno = 10)
VALUES (8003, '四爷', '皇上', NULL, SYSDATE, 5000, 500, 10);
-- 代码15.20 创建嵌套表替代触发器
CREATE OR REPLACE TRIGGER dept_emp_innerview
INSTEAD OF INSERT
ON NESTED TABLE emplst OF dept_emp_view --创建嵌套表替代触发器
BEGIN
INSERT INTO emp --插入子表记录
(deptno, empno, ename, job, mgr,
hiredate, sal, comm
)
VALUES (:PARENT.deptno, :NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr,
:NEW.hiredate, :NEW.sal, :NEW.comm
);
END;
--利用替代触发器可在嵌套表视图列中执行DML
INSERT INTO TABLE (SELECT emplst
FROM dept_emp_view
WHERE deptno = 10)
VALUES (8003, '四爷', '皇上', NULL, SYSDATE, 5000, 500, 10);
--15.4 系统事件触发器
DROP TABLE created_log
--代码15.21 系统触发器创建示例
CREATE TABLE created_log
(
obj_owner VARCHAR2(30), --所有者
obj_name VARCHAR2(30), --对象名称
obj_type VARCHAR2(20), --对象类型
obj_user VARCHAR2(30), --创建用户
created_date DATE --创建日期
)
--使用system会话
CREATE OR REPLACE TRIGGER t_created_log
AFTER CREATE ON scott.SCHEMA --在soctt方案下创建对象后触发
BEGIN
--插入日志记录
INSERT INTO scott.created_log(obj_owner, obj_name,
obj_type, obj_user, created_date
)
VALUES (SYS.dictionary_obj_owner, SYS.dictionary_obj_name,
SYS.dictionary_obj_type, SYS.login_user, SYSDATE
);
END;
create table test (id int, name varchar2(100));
drop table test;
select * from created_log;
truncate table created_log;
DROP TRIGGER t_created_log;
--代码15.22 Logon系统触发器创建示例
--以DBA身份登录,创建下面的登录记录表
CREATE TABLE log_db_table
(
username VARCHAR2(20),
logon_time DATE,
logoff_time DATE,
address VARCHAR2(20)
);
--以soctt身份登录,创建下面的登录记录表
CREATE TABLE log_user_table
(
username VARCHAR2(20),
logon_time DATE,
logoff_time DATE,
address VARCHAR2(20)
);
--以DBA身份登录,创建DATABASE级别的LOGON事件触发器
CREATE OR REPLACE TRIGGER t_db_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO log_db_table(username,logon_time,address)
VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;
--以scott身份登录,创建如下的SCHEMA级别的LOGON事件触发器
CREATE OR REPLACE TRIGGER t_user_logon
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO log_user_table(username,logon_time,address)
VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;
select * from log_user_table;
--代码15.23 Startup和Shutdown触发器
--以DBA身份进入系统,创建临时表
CREATE TABLE event_table(
sys_event VARCHAR2(30),
event_time DATE
);
--在DBA级别创建如下的2个触发器
CREATE OR REPLACE TRIGGER t_startup
AFTER STARTUP ON DATABASE --STARTUP只能是AFTER
BEGIN
INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER t_startup
BEFORE SHUTDOWN ON DATABASE --SHUTDOWN只能是BEFORE
BEGIN
INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
--代码15.24 使用ora_alter_column禁止非法更改列
CREATE OR REPLACE TRIGGER preserve_app_cols
AFTER ALTER ON SCHEMA
DECLARE
--获取一个表中所有列的游标
CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)
IS
SELECT column_name
FROM all_tab_columns
WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
-- 如果正使用的是ALTER TABLE语句修改表
IF ora_dict_obj_type = 'TABLE'
THEN
-- 循环表中的每一列
FOR v_column_rec IN curs_get_columns (
ora_dict_obj_owner,
ora_dict_obj_name
)
LOOP
--判断当前的列名正在被修改
IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name)
THEN
IF v_column_rec.column_name='EMPNO' THEN
RAISE_APPLICATION_ERROR (
-20003,
'不能对empno字段进行修改'
);
END IF;
END IF;
END LOOP;
END IF;
END;
ALTER TABLE scott.emp MODIFY(empno NUMBER(8))
ALTER TABLE scott.emp MODIFY(ename varchar2(22))
--代码15.25 使用After servererror触发器记录错误日志
--错误日志记录表
CREATE TABLE servererror_log(
error_time DATE,
username VARCHAR2(30),
instance NUMBER,
db_name VARCHAR2(50),
error_stack VARCHAR2(2000)
);
--创建错误触发器,在出现数据库错误时触发。
CREATE OR REPLACE TRIGGER t_logerrors
AFTER SERVERERROR ON DATABASE
BEGIN
INSERT INTO servererror_log
VALUES (SYSDATE, login_user, instance_num, database_name,
DBMS_UTILITY.format_error_stack);
END;
--查询用户权限(使用sysdba权限)
select privilege from dba_sys_privs where grantee='SCOTT'
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' )