Oracle之Trigger学习

Trigger学习总结

 

Note: The size of the trigger cannot be more than32K.

 

create table emp_trigger

as

select * from emp

 

CREATE OR REPLACE TRIGGERPrint_salary_changes

  BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger

  FOR EACH ROW

  WHEN (new.EMPNO > 5000)

DECLARE

  sal_diff number;

BEGIN

  sal_diff := :NEW.SAL - :OLD.SAL;

  dbms_output.put('Old salary: ' || :old.sal);

  dbms_output.put(' New salary: ' || :new.sal);

  dbms_output.put_line(' Difference ' || sal_diff);

END;

 

 

insert intoemp_trigger(empno,sal) values(6000,20);

Oldsalary:  New salary: 20 Difference

 

 

 

ONLY the after for each row can look at a"stable" value in the :new record. So, if you doing data validation (eg: this column must be between 1 and30 when this condition is true), you should do that in an AFTER trigger becausethe BEFORE triggers may change the value on you (and since BEFORE triggers firein SOME RANDOM order -- you cannot be assured that your BEFORE trigger firesbefore or after some other BEFORE trigger)

 

So, use BEFORE FOR EACH row when you needto WRITE to the :new record use AFTER FOR EACH row triggers when you want toVALIDATE the final values in the :new record

 

In general, you use BEFORE or AFTER triggers to achieve the following results:

■ Use BEFORE row triggers tomodify the row before the row data is written to disk.

■ Use AFTER row triggers toobtain, and perform operations, using the row ID.

 

 

INSTEAD OF

 

INSTEAD OF triggers can bedefined only on views, not on tables.

 

drop table tr_emp purge;

 

create table tr_emp

(

 empno number(4) primary key,

 ename varchar2(50),

 deptno number(10)

)

 

drop table tr_dept purge;

 

create table tr_dept

(

 deptno number(4) primary key,

 dname varchar2(50)

);

 

 

insert into  tr_emp

selectempno,ename,deptno from emp;

 

insert into tr_dept

selectdeptno,dname from dept;

 

select * from tr_emp

 

 

CREATE OR REPLACE VIEW v_emp_dept AS

SELECTe.empno,e.ename, d.dname, d.deptno

FROM tr_empe, tr_dept d

WHERE e.deptno= d.deptno

 

 

Select * from tr_emp                                select * from tr_dept

EMPNO	ENAME	DEPTNO
5555	ggg	10
7369	SMITH	20
7499	ALLEN	30
7521	WARD	30
7566	JONES	20
7654	MA & RTIN	30
7698	BLAKE	30
7782	CLARK	10
7788	SCOTT	20
7839	KING	10
7844	TURNER	30
7900	JAMES	30
7902	FORD	20
7934	MILLER	10
7876	ADAMS	20

DEPTNO	DNAME
55	55
10	ACCOUNTING
20	RESEARCH
30	SALES
40	OPERATIONS

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATE OR REPLACE TRIGGER v_emp_dept_insert

INSTEAD OF INSERT ON v_emp_dept

REFERENCING NEW AS n --新数据行用n别名

FOR EACH ROW

DECLARE

  rowcnt number;

BEGIN

  SELECT COUNT(*) INTO rowcnt FROM tr_emp WHERE empno = :n.empno;

  IF rowcnt = 0 THEN --表示是新的雇员

    INSERT INTO tr_emp (empno, ename,deptno) VALUES (:n.empno,:n.ename, :n.deptno);--增加新的雇员信息

  ELSE              --否则是老雇员，则更新雇员的姓名即可

    UPDATE tr_emp

    SET tr_emp.ename = :n.ename,

        tr_emp.deptno = :n.deptno

    WHERE tr_emp.empno = :n.empno; --更新

  END IF;

 

  SELECT COUNT(*) INTO rowcnt FROM tr_dept WHERE deptno = :n.deptno;

  IF rowcnt = 0 THEN --表示是新的部门

    INSERT INTO tr_dept (deptno, dname) VALUES (:n.deptno,:n.dname);

  ELSE

    UPDATE tr_dept

       SET tr_dept.dname = :n.dname

     WHERE tr_dept.deptno = :n.deptno;

  END IF;

END;

 

 

FOR EACH ROW Option

CREATE TABLE tr_Emp_log (

Emp_id NUMBER,

Log_date DATE,

New_salary NUMBER(16,2),

Action VARCHAR2(20)

);

 

create table tr_emp2

(

 empno number(4),

 ename varchar2(50),

 sal   number(16,2),

 deptno number(2)

)

 

insert into tr_emp2

selectempno,ename,sal,deptno

from emp

 

CREATE OR REPLACE TRIGGER Log_salary_increase

AFTER UPDATE ON tr_emp2

FOR EACH ROW

WHEN (new.Sal> 1000)

BEGIN

INSERT INTO tr_Emp_log(Emp_id,Log_date, New_salary, Action)

VALUES(:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');

END;

 

 

UPDATE tr_emp2 SET Sal = Sal + 1000.0

WHERE Deptno =20;

 

 

select * from tr_Emp_log

 

This trigger is fired 5 times.

 

drop TRIGGERLog_salary_increase;

 

 

CREATE OR REPLACE TRIGGER Log_emp_update

AFTER UPDATE ON tr_emp2

BEGIN

INSERT INTO tr_Emp_log(Log_date, Action)

VALUES (SYSDATE, 'emp sal changed');

END;

 

 

UPDATE tr_emp2 SET Sal = Sal + 1000.0

WHERE Deptno =20;

 

 

select * from tr_Emp_log

 

drop TRIGGER Log_emp_update;

 

EMP_ID	LOG_DATE	NEW_SALARY	ACTION
7369	2008-08-28 16:36:23	1800.00	NEW SAL
7566	2008-08-28 16:36:23	3975.00	NEW SAL
7788	2008-08-28 16:36:23	4000.00	NEW SAL
7902	2008-08-28 16:36:23	4000.00	NEW SAL
7876	2008-08-28 16:36:23	2100.00	NEW SAL
2008-08-28 16:41:33	emp sal changed

 

 

WHEN

 

Droptable emp_trigger purge;

create table emp_trigger

as

select * from emp

 

CREATE OR REPLACE TRIGGERPrint_salary_changes

  BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger

  FOR EACH ROW

  WHEN (new.EMPNO*10 > 50000)

DECLARE

  sal_diff number;

BEGIN

  sal_diff := :NEW.SAL - :OLD.SAL;

  dbms_output.put('Old salary: ' || :old.sal);

  dbms_output.put(' New salary: ' || :new.sal);

  dbms_output.put_line(' Difference ' || sal_diff);

END;

 

insert intoemp_trigger(empno,sal) values(6000,20);

Old salary:  New salary: 20 Difference

 

 

CREATE OR REPLACE TRIGGERPrint_salary_changes

  BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger

  FOR EACH ROW

  WHEN (sqrt(new.EMPNO) >70.7)

DECLARE

  sal_diff number;

BEGIN

  sal_diff := :NEW.SAL - :OLD.SAL;

  dbms_output.put('Old salary: ' || :old.sal);

  dbms_output.put(' New salary: ' || :new.sal);

  dbms_output.put_line(' Difference ' || sal_diff);

END;

 

insert intoemp_trigger(empno,sal) values(6000,20);

Old salary:  New salary: 20 Difference

 

 

 

 

 

create or replace function func_test(p1 in integer) return integer is

  Result integer;

begin

  return(p1*10);

end func_test;

 

 

CREATE OR REPLACE TRIGGERPrint_salary_changes

  BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger

  FOR EACH ROW

  WHEN func_test(new.EMPNO > 50000)

DECLARE

  sal_diff number;

BEGIN

  sal_diff := :NEW.SAL - :OLD.SAL;

  dbms_output.put('Old salary: ' || :old.sal);

  dbms_output.put(' New salary: ' || :new.sal);

  dbms_output.put_line(' Difference ' || sal_diff);

END;

第 4 行出现错误:

ORA-04076: 无效的 NEW 或 OLD 说明

 

 

create or replace function func_test(p1 in integer) return integer

deterministic

is

  Result integer;

begin

  return(p1*10);

endfunc_test;

 

CREATE OR REPLACE TRIGGERPrint_salary_changes

  BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger

  FOR EACH ROW

  WHEN (func_test(new.EMPNO) >70.7)

DECLARE

  sal_diff number;

BEGIN

  sal_diff := :NEW.SAL - :OLD.SAL;

  dbms_output.put('Old salary: ' || :old.sal);

  dbms_output.put(' New salary: ' || :new.sal);

  dbms_output.put_line(' Difference ' || sal_diff);

END;

 

 

依然出现错误

第 4 行出现错误:

ORA-04076: 无效的 NEW 或 OLD 说明

 

WHEN(new.Parts_on_hand <new.Reorder_point)

 

 

复合trigger(compound trigger)  (11g new feature)

CREATE TABLE tr_Emp_log (

Emp_id NUMBER,

Log_date DATE,

New_salary NUMBER(16,2),

Action VARCHAR2(20)

);

 

create table tr_emp2

(

 empno number(4),

 ename varchar2(50),

 sal   number(16,2),

 deptno number(2)

)

 

create or replace trigger log_emp_update

  for update of sal on tr_emp2

  compound trigger

  type t_number is table of number(16, 2) index by pls_integer;

  type t_integer is table of integer index by pls_integer;

  v_sal t_number;

  v_empno t_integer;

 

 BEFORE STATEMENT IS

  BEGIN

  DBMS_OUTPUT.PUT_LINE('begin working');

 END BEFORE STATEMENT;

 

 

  BEFORE EACH ROW IS

   BEGIN

     v_empno(v_empno.count + 1) := :NEW.empno;

    --do not working

    -- v_sal(v_sal.count + 1) :=:NEW.sal;

    --dbms_output.put_line('hhhhh'||:NEW.sal);

   END BEFORE EACH ROW;

  

 AFTER EACH ROW IS

 BEGIN

     v_sal(v_sal.count + 1) := :NEW.sal;

     dbms_output.put_line('hhhhh'||:NEW.sal);

 END AFTER EACH ROW;

 

  AFTER STATEMENT IS

    BEGIN

      forall i in 1 .. v_sal.count

        insert into tr_emp_log(emp_id, log_date,new_salary, action)

        values(v_empno(i), sysdate, v_sal(i), 'new sal');

    END AFTER STATEMENT;

END;

 

UPDATE tr_emp2 SET Sal = Sal + 1000.0

WHERE Deptno =20;

 

Select * from tr_emp_log

EMP_ID	LOG_DATE	NEW_SALARY	ACTION
7369	2008-08-28 20:17:07	new sal
7566	2008-08-28 20:17:07	new sal
7788	2008-08-28 20:17:07	new sal
7902	2008-08-28 20:17:07	new sal
7876	2008-08-28 20:17:07	new sal
7369	2008-08-28 20:20:57	6800.00	new sal
7566	2008-08-28 20:20:57	8975.00	new sal
7788	2008-08-28 20:20:57	9000.00	new sal
7902	2008-08-28 20:20:57	9000.00	new sal
7876	2008-08-28 20:20:57	7100.00	new sal

 

 

Detecting the DML Operation that Fired a Trigger

 

IF INSERTING THEN... END IF;

IF UPDATING THEN ...END IF;

 

 

 

CREATE OR REPLACETRIGGER ...

... UPDATE OF Sal,Comm ON emp ...

BEGIN

... IF UPDATING('SAL') THEN ... END IF;

END;

 

 

 

Java Trigger

 

create or replace and compile java source named JT as

importjava.sql.*;

importjava.io.*;

importoracle.sql.*;

importoracle.oracore.*;

public class JavaTriggers

{

public static void beforeDelete(NUMBER old_id, CHAR old_name)

{

Connection conn = JDBCConnection.defaultConnection();

Statement stmt = conn.CreateStatement();

String sql = "insert into logtab values(" + old_id.intValue() + ", " +old_ename.toString() + ", BEFORE DELETE)";

stmt.executeUpdate (sql);

stmt.close();

//return;

}

}

 

 

CREATE OR REPLACE PROCEDUREBefore_delete (Id IN NUMBER, Ename VARCHAR2)

IS language Java

name JavaTriggers.beforeDelete (oracle.sql.NUMBER,oracle.sql.CHAR)';

CREATE OR REPLACETRIGGER Pre_del_trigger BEFORE DELETE ON Tab

FOR EACH ROW

CALL Before_delete(:old.Id, :old.Ename)

 

 

作者：田文  CSDN地址：http://blog.csdn.net/tiwen818