ary 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