oracle触发器

来源：互联网发布：vb 类模块编辑：程序博客网时间：2024/06/06 20:57

21-1:建立before触发器
CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN ('SAT','SUN') THEN
raise_application_error(-20001,
'不能在休息日改变雇员信息');
END IF;
END;
/
21-2:建立after触发器
CREATE TABLE aud_upd_table(
host VARCHAR2(30),statement VARCHAR2(100),exectime DATE);
CREATE OR REPLACE TRIGGER tr_upd_emp
AFTER UPDATE ON emp
DECLARE
sql_txt ora_name_list_t;
v_stmt VARCHAR2(100);
n BINARY_INTEGER;
BEGIN
n:=ora_sql_txt(sql_txt);
FOR i IN 1..n LOOP
v_stmt:=v_stmt||sql_txt(i);
END LOOP;
INSERT INTO aud_upd_table VALUES(
sys_context('userenv','host'),v_stmt,SYSDATE);
END;
/
21-3:使用条件谓词
CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN ('SAT','SUN') THEN
CASE
WHEN INSERTING THEN
raise_application_error(-20001,
'不能在休息日执行INSERT');
WHEN UPDATING THEN
raise_application_error(-20002,
'不能在休息日执行UPDATE');
WHEN DELETING THEN
raise_application_error(-20003,
'不能在休息日执行DELETE');
END CASE;
END IF;
END;
/
21-4:建立before行触发器
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
IF :new.sal<:old.sal THEN
raise_application_error(-20010,'工资只涨不降');
END IF;
END;
/
21-5:建立after行触发器
CREATE TABLE audit_emp_change (
name VARCHAR2(10),oldsal NUMBER(6,2),
newsal NUMBER(6,2),time DATE);
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp
FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM audit_emp_change
WHERE name=:old.ename;
IF v_temp=0 THEN
INSERT INTO audit_emp_change
VALUES(:old.ename,:old.sal,:new.sal,SYSDATE);
ELSE
UPDATE audit_emp_change
SET oldsal=:old.sal,newsal=:new.sal,time=SYSDATE
WHERE name=:old.ename;
END IF;
END;
/
21-6:限制行触发器
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp
FOR EACH ROW WHEN (old.job='SALESMAN')
DECLARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM audit_emp_change
WHERE name=:old.ename;
IF v_temp=0 THEN
INSERT INTO audit_emp_change
VALUES(:old.ename,:old.sal,:new.sal,SYSDATE);
ELSE
UPDATE audit_emp_change
SET oldsal=:old.sal,newsal=:new.sal,time=SYSDATE
WHERE name=:old.ename;
END IF;
END;
/
21-7:组合触发器
CREATE OR REPLACE TRIGGER tr_update_sal
FOR UPDATE OF sal ON emp COMPOUND TRIGGER
msg1 VARCHAR2(50):='工资必须在1000到5000之间!';
msg2 VARCHAR2(50):='不能在休息日更新!';
BEFORE STATEMENT IS
BEGIN
IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN ('SAT','SUN') THEN
raise_application_error(-20000,msg2);
END IF;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :new.sal NOT BETWEEN 1000 AND 5000 THEN
raise_application_error(-20001,msg1);
END IF;
END AFTER EACH ROW;
END;
/
21-8:控制数据安全
CREATE OR REPLACE TRIGGER tr_emp_time
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF to_char(SYSDATE,'HH24') NOT BETWEEN
'9' AND '17' THEN
raise_application_error(-20101,'非工作时间');
END IF;
END;
/
UPDATE emp SET sal=3200 WHERE empno=7788;
21-9:实现数据审计
CREATE TABLE audit_delete_emp(name VARCHAR2(10),time DATE);
CREATE OR REPLACE TRIGGER tr_delete_emp
AFTER DELETE ON emp FOR EACH ROW
BEGIN
INSERT INTO audit_delete_emp VALUES(:old.ename,SYSDATE);
END;
/
DELETE FROM emp WHERE empno=7788;
SELECT * FROM audit_delete_emp;
21-10:实现数据完整性
CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal ON emp FOR EACH ROW
WHEN (new.sal<old.sal OR new.sal>1.2*old.sal)
BEGIN
raise_application_error(-20931,
'工资只升不降，并且升幅不能超过20%');
END;
/
UPDATE emp SET sal=sal*1.25 WHERE empno=7788;
21-11:实现参照完整性
CREATE OR REPLACE TRIGGER tr_update_cascade
AFTER UPDATE OF deptno ON dept FOR EACH ROW
BEGIN
UPDATE emp SET deptno=:new.deptno
WHERE deptno=:old.deptno;
END;
/
UPDATE dept SET deptno=50 WHERE deptno=10;
SELECT ename FROM emp WHERE deptno=50;
21-12:instead of 触发器
CREATE OR REPLACE VIEW dept_emp AS
SELECT a.deptno,a.dname,b.empno,b.ename FROM dept a,emp b
WHERE a.deptno=b.deptno;
CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM dept WHERE deptno=:new.deptno;
IF v_temp=0 THEN
INSERT INTO dept (deptno,dname) VALUES(:new.deptno,:new.dname);
END IF;
SELECT count(*) INTO v_temp FROM emp WHERE empno=:new.empno;
IF v_temp=0 THEN
INSERT INTO emp (empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);
END IF;
END;
/
INSERT INTO dept_emp VALUES(50,'ADMIN',1223,'MARY');
SELECT dname FROM dept WHERE deptno=50;
SELECT ename FROM emp WHERE empno=1223;
21-13:系统事件触发器
conn sys/oracle as sysdba
create table event_table(event varchar2(30),time date);
CREATE OR REPLACE TRIGGER tr_startup
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
SHUTDOWN
STARTUP
SELECT * FROM event_table;
21-14:建立登录触发器
conn sys/oracle as sysdba
CREATE TABLE aud_logon_tab(
username VARCHAR2(20),time DATE,addr VARCHAR2(20));
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO aud_logon_tab VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;
/
conn scott/tiger@test
SELECT * FROM sys.aud_logon_tab;
21-15:建立DDL触发器
conn sys/oracle as sysdba
CREATE TABLE aud_ddl_tab(
event VARCHAR2(20),username VARCHAR2(10),
owner VARCHAR2(10),objname VARCHAR2(20),
objtype VARCHAR2(10),time DATE);
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON scott.schema
BEGIN
INSERT INTO aud_ddl_tab VALUES(
ora_sysevent,ora_login_user,ora_dict_obj_owner,
ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
END;
/
conn scott/tiger@test
CREATE TABLE temp(cola INT);
DROP TABLE temp;
SELECT username,event,objtype,objname FROM sys.aud_ddl_tab;
21-16:显示触发器信息
set long 800
set pagesize 400
SELECT trigger_body FROM user_triggers WHERE trigger_name='TR_UPD_EMP';
21-17:禁止触发器
ALTER TRIGGER tr_upd_emp DISABLE;
21-18:激活触发器
ALTER TRIGGER tr_upd_emp ENABLE;
21-19:重新编译触发器
ALTER TRIGGER tr_upd_emp COMPILE;
21-20:删除触发器
DROP TRIGGER tr_check_sal;