学习PL/SQL到现在，发现触发器还是最好玩的，让我大开眼界。
原来使用触发器可以实现很多功能：日志、审计记录、逻辑校验、数据备份，都是很重要的功能。
需要搞清楚的是，在实际的生产环境中，上述功能是否真的是通过触发器来实现的？
--第15章开始
--代码15.1 触发器定义示例
CREATE OR REPLACE TRIGGER t_verifysalary
BEFORE UPDATE ON emp --触发器作用的表对象以及触发的条件和触发的动作
FOR EACH ROW --行级别的触发器
WHEN(new.sal>old.sal) --触发器条件
DECLARE
v_sal NUMBER; --语句块的声明区
BEGIN
IF UPDATING ('sal') THEN --使用条件谓词判断是否是sal列被更新
v_sal := :NEW.sal - :OLD.sal; --记录工资的差异
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 sal = v_sal
WHERE empno = :NEW.empno;
END IF;
END;
DELETE FROM emp WHERE sal IS NULL;
UPDATE emp SET sal=sal*1.15 WHERE deptno=20;
SELECT * FROM emp WHERE deptno=20;
SELECT * FROM emp_history where deptno=20;
select 2076*1.15 from dual;
desc emp;
--代码15.2 记录日志触发器示例（这个示例挺实用，后台数据就这么自动生成了:)）
--创建一个emp_log表用来记录对emp表的更改
CREATE TABLE emp_log(
log_id NUMBER, --日志自增长字段
log_action VARCHAR2(100), --表更改行为，比如新增或删除或更改
log_date DATE, --日志日期
empno NUMBER(4), --员工编号
ename VARCHAR2(10), --员工名称
job VARCHAR2(18), --职别
mgr NUMBER(4), --管理者
hiredate DATE, --雇佣日期
sal NUMBER(11,2), --工资
comm NUMBER(7,2), --提成或分红
deptno NUMBER(2) --部门编号
);
drop table emp_log;
CREATE SEQUENCE emp_log_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999
NOCYCLE NOCACHE;
--创建一个AFTER行触发器
CREATE OR REPLACE TRIGGER t_emp_log
AFTER INSERT OR DELETE OR UPDATE ON emp --触发器作用的表对象以及触发的条件和触发的动作
FOR EACH ROW --行级别的触发器
BEGIN
IF INSERTING THEN --判断是否是INSERT语句触发的
INSERT INTO emp_log --向emp_log表中插入日志记录
VALUES(
emp_log_seq.NEXTVAL,
'INSERT',SYSDATE,
:new.empno,:new.ename,:new.job,
:new.mgr,:new.hiredate,:new.sal,
:new.comm,:new.deptno );
ELSIF UPDATING THEN --判断是否是UPDATE语句触发的
INSERT INTO emp_log --首先插入旧的记录
VALUES(
emp_log_seq.NEXTVAL,
'UPDATE_NEW',SYSDATE,
:new.empno,:new.ename,:new.job,
:new.mgr,:new.hiredate,:new.sal,
:new.comm,:new.deptno );
INSERT INTO emp_log --然后插入新的记录
VALUES(
emp_log_seq.CURRVAL,
'UPDATE_OLD',SYSDATE,
:old.empno,:old.ename,:old.job,
:old.mgr,:old.hiredate,:old.sal,
:old.comm,:old.deptno );
ELSIF DELETING THEN --如果是删除记录
INSERT INTO emp_log
VALUES(
emp_log_seq.NEXTVAL,
'DELETE',SYSDATE,
:old.empno,:old.ename,:old.job,
:old.mgr,:old.hiredate,:old.sal,
:old.comm,:old.deptno );
END IF;
END;
SELECT name,line, POSITION, text
FROM user_errors
WHERE NAME = 'T_EMP_LOG'
ORDER BY SEQUENCE;
UPDATE emp SET sal=sal*1.12 WHERE deptno=40;
SELECT * FROM emp_log where deptno=40;
SELECT * from emp WHERE deptno=40;
--代码15.4 在语句触发器中使用谓词
CREATE OR REPLACE TRIGGER t_verify_emptime
BEFORE INSERT OR DELETE OR UPDATE
ON emp
BEGIN
IF DELETING --使用谓词判断是否为DELETING操作，仅删除时才判断
THEN
--判断当前操作的日期
IF (TO_CHAR (SYSDATE, 'DAY') IN ('星期六', '星期日'))
OR (TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '10:30' AND '18:00')
THEN
--触发异常，将导致整个事务被回滚。
raise_application_error (-20001, '不能在非常时间段内操纵emp表');
END IF;
END IF;
END;
delete from emp where deptno=40;
DROP TABLE audit_table;
--创建审计信息表
CREATE TABLE audit_table(
table_name VARCHAR2(20), --统计表名称
ins_count INT, --INSERT语句执行次数
udp_count INT, --UPDATE语句执行次数
del_count INT, --DELETE语句执行次数
start_time DATE, --开始时间
end_time DATE --结束时间
);
--代码15.5 使用AFTGER语句触发添加审计信息（审计功能）
CREATE OR REPLACE TRIGGER t_audit_emp
AFTER INSERT OR UPDATE OR DELETE
ON emp --在emp表中定义AFTER触发器
DECLARE
v_temp INT; --定义一个临时的变量来统计记条数
BEGIN
SELECT COUNT (*) --向v_temp表中插入EMP表的记录条数
INTO v_temp
FROM audit_table
WHERE table_name = 'EMP';
IF v_temp = 0
THEN
--向audit_table表中插入一条记录，将审记记录数量保留为0。
INSERT INTO audit_table VALUES ('EMP', 0, 0, 0, SYSDATE, NULL);
END IF;
CASE --使用PL/SQL的CASE语句判断DML类型
WHEN INSERTING THEN --如果是INSERT语句执行
UPDATE audit_table --更新ins_count字段
SET ins_count = ins_count + 1,
end_time = SYSDATE
WHERE table_name = 'EMP';
WHEN UPDATING THEN --如果是UPDATE语句执行
UPDATE audit_table
SET udp_count = udp_count + 1,--更新udp_count字段
end_time = SYSDATE
WHERE table_name = 'EMP';
WHEN DELETING THEN
UPDATE audit_table --如果是DELETE语句执行
SET del_count = del_count + 1,--更新del_count字段
end_time = SYSDATE
WHERE table_name = 'EMP';
END CASE;
END;
update emp set sal=sal*2 where empno=5093;
select * from audit_table;
--代码15.6 创建测试数据库表
CREATE TABLE emp_data --保存员工记录数据的测试表
(
emp_id INT, --自增长字段
empno NUMBER, --员工编号
ename VARCHAR2(20) --员工名称
);
CREATE TABLE emp_data_his --保存员工记录数据的历史备份表
(
emp_id INT, --自增长字段
empno NUMBER, --员工编号
ename VARCHAR2(20) --员工名称
);
--代码15.7 创建t_emp_data触发器（实现数据自动备份功能）
CREATE OR REPLACE TRIGGER t_emp_data
BEFORE INSERT
ON emp_data --触发器作用的表对象以及触发的条件和触发的动作
FOR EACH ROW --行级别的触发器
DECLARE
emp_rec emp_data%ROWTYPE;
-- emp_rec_his emp_data_his%ROWTYPE;
BEGIN
SELECT emp_seq.NEXTVAL INTO :NEW.emp_id FROM DUAL; --对BEFORE触发器的NEW赋值
--emp_rec:=:new; --不能直接对谓词记录进行记录级别的操作
emp_rec.emp_id := :NEW.emp_id;
emp_rec.empno := :NEW.empno;
emp_rec.ename := :NEW.ename;
INSERT INTO emp_data_his VALUES emp_rec; --使用记录级别的操作
END;
--查看编译错误
SELECT name,line, POSITION, text
FROM user_errors
WHERE NAME = 'T_EMP_DATA'
ORDER BY SEQUENCE;
--创建序列（解决编译错误）
CREATE SEQUENCE EMP_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999
NOCYCLE NOCACHE;
INSERT INTO emp_data(empno,ename) VALUES(7369,'李强');
SELECT * FROM emp_data;
SELECT * FROM emp_data_his;
TRUNCATE TABLE emp_data;
TRUNCATE TABLE emp_data_his;
--代码15.8 使用REFERENCING子句指定别名（提高代码可读性）
CREATE OR REPLACE TRIGGER t_vsal_ref
BEFORE UPDATE ON emp --触发器作用的表对象以及触发的条件和触发的动作
REFERENCING OLD AS emp_old NEW AS emp_new
FOR EACH ROW --行级别的触发器
WHEN(emp_new.sal>emp_old.sal) --触发器条件
DECLARE
v_sal NUMBER; --语句块的声明区
BEGIN
IF UPDATING ('sal') THEN --使用条件谓词判断是否是sal列被更新
v_sal := :emp_new.sal - :emp_old.sal; --记录工资的差异
DELETE FROM emp_history
WHERE empno = :emp_old.empno; --删除emp_history中旧表记录
INSERT INTO emp_history --向表中插入新的记录
VALUES (:emp_old.empno, :emp_old.ename, :emp_old.job, :emp_old.mgr, :emp_old.hiredate,
:emp_old.sal, :emp_old.comm, :emp_old.deptno);
UPDATE emp_history --更新薪资值
SET sal = v_sal
WHERE empno = :emp_new.empno;
END IF;
END;
--代码15.9 使用WHEN子句控制触发器代码的执行
CREATE OR REPLACE TRIGGER t_emp_comm
BEFORE UPDATE ON emp --触发器作用的表对象以及触发的条件和触发的动作
FOR EACH ROW --行级别的触发器
WHEN(NEW.comm>OLD.comm) --触发器条件
DECLARE
v_comm NUMBER; --语句块的声明区
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;
END;
update emp set comm=120 where empno=5093;
update emp set comm=comm*3 where empno=5093;
select * from emp where empno=5093;
select * from emp_history where empno=5093;
--代码15.10 使用Updating谓词判断特定字段的更新（实现对DML的校验）
CREATE OR REPLACE TRIGGER t_comm_sal
BEFORE UPDATE ON emp --触发器作用的表对象以及触发的条件和触发的动作
FOR EACH ROW --行级别的触发器
BEGIN
CASE
WHEN UPDATING('comm') THEN --如果是对comm列进行更新
IF :NEW.comm<:OLD.comm or :new.comm>:old.comm*2 THEN --要求新的comm值要大于旧的comm值
RAISE_APPLICATION_ERROR(-20001,'新的comm值不能小于旧的comm值，也不能超过旧值的两倍');
END IF;
WHEN UPDATING('sal') THEN --如果是对sal列进行更新
IF :NEW.sal<:OLD.sal THEN --要求新的sal值要大于旧的sal值
RAISE_APPLICATION_ERROR(-20001,'新的sal值不能小于旧的sal值');
END IF;
END CASE;
END;
--代码15.11 多触发器定义代码
--创建一个表来测试多个触发器的执行顺序
CREATE TABLE trigger_data
(
trigger_id INT,
tirgger_name VARCHAR2(100)
)
--创建第一个触发器
CREATE OR REPLACE TRIGGER one_trigger
BEFORE INSERT
ON trigger_data
FOR EACH ROW
BEGIN
:NEW.trigger_id := :NEW.trigger_id + 1;
DBMS_OUTPUT.put_line('触发了one_trigger');
END;
--创建与第1个触发器具有相同类型相同触发时机的触发器
CREATE OR REPLACE TRIGGER two_trigger
BEFORE INSERT
ON trigger_data
FOR EACH ROW
FOLLOWS one_trigger --让该触发器在one_trigger后面触发
BEGIN
DBMS_OUTPUT.put_line('触发了two_trigger');
IF :NEW.trigger_id > 1
THEN
:NEW.trigger_id := :NEW.trigger_id + 2;
END IF;
END;
INSERT INTO trigger_data VALUES(1,'triggerdemo');
TRUNCATE TABLE trigger_data;
SELECT * FROM trigger_data;
--查看依赖关系
SELECT referenced_name, referenced_type, dependency_type
FROM user_dependencies
WHERE NAME = 'TWO_TRIGGER' AND referenced_type = 'TRIGGER';
--代码15.13 错误的触发器语句示例
CREATE OR REPLACE TRIGGER t_emp_maxsal
BEFORE UPDATE OF sal
ON emp --在UPDATE语句更新sal值之前触发
FOR EACH ROW --行级别的触发器
DECLARE
v_maxsal NUMBER; --保存最大薪资值的变量
BEGIN
SELECT MAX (sal)
INTO v_maxsal
FROM emp; --获取emp表最大薪资值
UPDATE emp
SET sal = v_maxsal - 100 --更新员工7369的薪资值
WHERE empno = 5093;
END;
--以下语句不能成功执行
UPDATE emp SET sal=sal*1.12 WHERE empno=5093;
drop trigger t_emp_maxsal;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
