您的位置:知识库 » 数据库

ORACLE PL/SQL编程之把触发器说透

作者: huyong  来源: 博客园  发布时间: 2011-05-02 15:46  阅读: 6715 次  推荐: 1   原文链接   [收藏]  
[1] ORACLE PL/SQL编程之把触发器说透
[2] ORACLE PL/SQL编程之把触发器说透
[3] ORACLE PL/SQL编程之把触发器说透

8.6   数据库触发器的应用实例

  用户可以使用数据库触发器实现各种功能:

  复杂的审计功能;

  例:将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。

CREATE TABLE audit_table(
    Audit_id     
NUMBER,
    
User_name VARCHAR2(20),
    Now_time DATE,
    Terminal_name 
VARCHAR2(10),
    Table_name 
VARCHAR2(10),
    Action_name 
VARCHAR2(10),
    Emp_id 
NUMBER(4));

CREATE TABLE audit_table_val(
    Audit_id 
NUMBER,
    Column_name 
VARCHAR2(10),
    Old_val 
NUMBER(7,2),
    New_val 
NUMBER(7,2));

CREATE SEQUENCE audit_seq
    START 
WITH 1000
    INCREMENT 
BY 1
    NOMAXVALUE
    NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER audit_emp
    AFTER 
INSERT OR UPDATE OR DELETE ON emp
    
FOR EACH ROW
DECLARE
    Time_now DATE;
    Terminal 
CHAR(10);
BEGIN 
    Time_now:
=sysdate;
    Terminal:
=USERENV('TERMINAL');
    
IF INSERTING THEN
        
INSERT INTO audit_table
    
VALUES(audit_seq.NEXTVAL, user, time_now, 
           terminal, 
'EMP''INSERT', :new.empno);
    ELSIF DELETING 
THEN
        
INSERT INTO audit_table
    
VALUES(audit_seq.NEXTVAL, user, time_now, 
           terminal, 
'EMP''DELETE', :old.empno);
    
ELSE
        
INSERT INTO audit_table
    
VALUES(audit_seq.NEXTVAL, user, time_now, 
           terminal, 
'EMP''UPDATE', :old.empno);
        
IF UPDATING('SAL'THEN
            
INSERT INTO audit_table_val
                
VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal);
        
ELSE UPDATING('DEPTNO'
            
INSERT INTO audit_table_val
                
VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno);
        
END IF;
    
END IF;
END;

  增强数据的完整性管理;

  例:修改DEPT表的DEPTNO列时,同时把EMP表中相应的DEPTNO也作相应的修改;

CREATE SEQUENCE update_sequence 
    INCREMENT 
BY 1
    START 
WITH 1000
    MAXVALUE 
5000 CYCLE;

ALTER TABLE emp
    
ADD update_id NUMBER;

CREATE OR REPLACE PACKAGE integritypackage AS
    Updateseq 
NUMBER;
END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS
END integritypackage;

CREATE OR REPLACE TRIGGER dept_cascade1
    BEFORE 
UPDATE OF deptno ON dept
DECLARE 
    
Dummy NUMBER;
BEGIN 
    
SELECT update_sequence.NEXTVAL INTO dummy FROM dual;
    Integritypackage.updateseq:
=dummy;
END;

CREATE OR REPLACE TRIGGER dept_cascade2
    AFTER 
DELETE OR UPDATE OF deptno ON dept
    
FOR EACH ROW
BEGIN
    
IF UPDATING THEN
        
UPDATE emp SET deptno=:new.deptno, 
     update_id
=integritypackage.updateseq
        
WHERE emp.deptno=:old.deptno AND update_id IS NULL;
    
END IF;
    
IF DELETING THEN
        
DELETE FROM emp
            
WHERE emp.deptno=:old.deptno;
    
END IF;
END;

CREATE OR REPLACE TRIGGER dept_cascade3
    AFTER 
UPDATE OF deptno ON dept 
BEGIN
    
UPDATE emp SET update_id=NULL
        
WHERE update_id=integritypackage.updateseq;
END;

SELECT * FROM EMP ORDER BY DEPTNO;
UPDATE dept SET deptno=25 WHERE deptno=20;

  帮助实现安全控制;

  例:保证对EMP表的修改仅在工作日的工作时间;

CREATE TABLE company_holidays(day DATE);

INSERT INTO company_holidays 
    
VALUES(sysdate);
INSERT INTO company_holidays 
VALUES(TO_DATE('21-10月-01''DD-MON-YY'));

CREATE OR REPLACE TRIGGER emp_permit_change
    BEFORE 
INSERT OR DELETE OR UPDATE ON emp
DECLARE
    
Dummy NUMBER;
    Not_on_weekends EXCEPTION;
    Not_on_holidays EXCEPTION;
    Not_working_hours EXCEPTION;
BEGIN
    
/* check for weekends */
IF TO_CHAR(SYSDATE, 'DAY'IN ('星期六''星期日'THEN
    RAISE not_on_weekends;
END IF;
    
/* check for company holidays */
SELECT COUNT(*INTO dummy FROM company_holidays
    
WHERE TRUNC(day)=TRUNC(SYSDATE);
IF dummy >0 THEN
    RAISE not_on_holidays;
END IF;
    
/* check for work hours(8:00 AM to 18:00 PM */
IF (TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE, 'HH24')>18THEN
  RAISE not_working_hours;
END IF;
EXCEPTION
  
WHEN not_on_weekends THEN
    RAISE_APPLICATION_ERROR(
-20324
'May not change employee table during the weekends'); 
  
WHEN not_on_holidays THEN
    RAISE_APPLICATION_ERROR(
-20325
'May not change employee table during a holiday'); 
  
WHEN not_working_hours THEN
    RAISE_APPLICATION_ERROR(
-20326
'May not change employee table during no_working hours'); 
END;
1
0

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻