Oracle Trigger 實踐

--[6]// Oracle Trigger 
---------------------------------------------------------------------------------------------// 
--实例1------------------------ 
--创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表 
--创建测试表 
CREATE TABLE test 
( 
t_id NUMBER(4), 
t_name VARCHAR2(20), 
t_age NUMBER(2), 
t_sex CHAR 
); 
--创建记录测试表 
CREATE TABLE test_log 
( 
l_user VARCHAR2(15), 
l_type VARCHAR2(15), 
l_date VARCHAR2(30) 
); 
--创建触发器 
CREATE OR REPLACE TRIGGER test_trigger 
AFTER DELETE OR INSERT OR UPDATE ON test 
DECLARE 
v_type test_log.l_type%TYPE; 
BEGIN 
IF INSERTING THEN --INSERT触发 
v_type := 'INSERT'; 
DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志'); 
ELSIF UPDATING THEN --UPDATE触发 
v_type := 'UPDATE'; 
DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志'); 
ELSIF DELETING THEN 
v_type := 'DELETE'; 
DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志'); 
END IF; 
INSERT INTO test_log VALUES(user,v_type, 
TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')); 
END; 
/ 
--下面我们来分别执行DML语句 
INSERT INTO test VALUES(101,'zhao',22,'M'); 
UPDATE test SET t_age = 30 WHERE t_id = 101; 
DELETE test WHERE t_id = 101; 
--然后查看效果 
SELECT * FROM test; 
SELECT * FROM test_log; 

--实例2------------------------ 
--创建触发器，它将映射emp表中每个部门的总人数和总工资 
--创建映射表 
CREATE TABLE dept_sal 
AS 
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; 
DESC dept_sal; 
--创建触发器 
CREATE OR REPLACE TRIGGER emp_info 
AFTER INSERT OR UPDATE OR DELETE ON emp 
DECLARE 
CURSOR cur_emp IS 
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; 
BEGIN 
DELETE dept_sal; --触发时首先删除映射表信息 
FOR v_emp IN cur_emp LOOP 
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal); 
--插入数据 
INSERT INTO dept_sal 
VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal); 
END LOOP; 
END; 
/ 
--对emp表进行DML操作 
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000); 
SELECT * FROM dept_sal; 
DELETE EMP WHERE empno=123; 
SELECT * FROM dept_sal; 

--实例3------------------------ 
--创建触发器，它记录表的删除数据 
--创建表 
CREATE TABLE employee 
( 
id VARCHAR2(4) NOT NULL, 
name VARCHAR2(15) NOT NULL, 
age NUMBER(2) NOT NULL, 
sex CHAR NOT NULL 
); 
DESC employee; 
--插入数据 
INSERT INTO employee VALUES('e101','zhao',23,'M'); 
INSERT INTO employee VALUES('e102','jian',21,'F'); 
--创建记录表 
CREATE TABLE old_employee AS 
SELECT * FROM employee; 
DESC old_employee; 
--创建触发器 
CREATE OR REPLACE TRIGGER tig_old_emp 
AFTER DELETE ON employee -- 
FOR EACH ROW --语句级触发，即每一行触发一次 
BEGIN 
INSERT INTO old_employee 
VALUES(:old.id,:old.name,:old.age,:old.sex); --:old代表旧值 
END; 
/ 
--下面进行测试 
DELETE employee; 
SELECT * FROM old_employee; 

--实例4------------------------ 
--创建触发器，利用视图插入数据 
--创建表 
CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2)); 
CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30)); 
--插入数据 
INSERT INTO tab1 VALUES(101,'zhao',22); 
INSERT INTO tab1 VALUES(102,'yang',20); 
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou'); 
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou'); 
--创建视图连接两张表 
CREATE VIEW tab_view AS 
SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 
WHERE tab1.tid = tab2.tid; 
--创建触发器 
CREATE OR REPLACE TRIGGER tab_trigger 
INSTEAD OF INSERT ON tab_view 
BEGIN 
INSERT INTO tab1(tid,tname) VALUES(:new.tid,:new.tname); 
INSERT INTO tab2(ttel,tadr) VALUES(:new.ttel,:new.tadr); 
END; 
/ 
--现在就可以利用视图插入数据 
INSERT INTO tab_view VALUES(105,'zhaoyang','13886681288','beijing'); 
--查看效果 
SELECT * FROM tab_view; 

--实例5------------------------ 
--创建触发器，比较emp表中更新的工资 
CREATE OR REPLACE TRIGGER sal_emp 
BEFORE UPDATE ON emp 
FOR EACH ROW 
BEGIN 

IF :OLD.sal > :NEW.sal THEN 
DBMS_OUTPUT.PUT_LINE('工资减少'); 
ELSIF :OLD.sal < :NEW.sal THEN 
DBMS_OUTPUT.PUT_LINE('工资增加'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); 
END IF; 
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.sal); 
DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.sal); 
END; 
/ 
--执行UPDATE查看效果 
UPDATE emp SET sal = 3000 WHERE empno = '7788'; 

--实例6------------------------ 
--创建触发器，将操作CREATE、DROP存储在log_info表 
--创建表 
CREATE TABLE log_info 
( 
manager_user VARCHAR2(15), 
manager_date VARCHAR2(15), 
manager_type VARCHAR2(15), 
obj_name VARCHAR2(15), 
obj_type VARCHAR2(15) 
); 
--创建触发器 
CREATE OR REPLACE TRIGGER trig_log_info 
AFTER CREATE OR DROP ON SCHEMA 
BEGIN 
INSERT INTO log_info 
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, 
SYS.DICTIONARY_OBJ_TYPE); 
END; 
/ 
--测试语句 
CREATE TABLE a(id NUMBER); 
CREATE TYPE aa AS OBJECT(id NUMBER); 
/ 
DROP TABLE a; 
DROP TYPE aa; 
--查看效果 
SELECT * FROM log_info; 

--相关数据字典-----------------------------------------------------// 
SELECT * FROM USER_TRIGGERS; 
SELECT * FROM ALL_TRIGGERS; 
SELECT * FROM DBA_TRIGGERS; --必须以DBA身份登陆才能使用此数据字典 

--启用和禁用 
ALTER TRIGGER trigger_name DISABLE; 
ALTER TRIGGER trigger_name ENABLE; 
------------------------------------------------------------------------------------------End//