Oracle's trigger for sql Development
Oracle's trigger for sql Development
Create an emp_log table to record changes to the emp table
Create sequence emp_seq increment by 1 start with 1 maxvalue 9999999 nocycle nocache
CREATE TABLE emp_log (
Log_id NUMBER,-- log self-growing field
Log_action VARCHAR2,-- table change behavior, such as add or delete or change
Log_date DATE,-- Log date
Empno NUMBER (4)-- employee number
Ename VARCHAR2 (10)-- employee name
Job VARCHAR2 (18)-- Professional grades
Mgr NUMBER (4)-Manager
Hiredate DATE,-date of employment
Sal NUMBER (7Pol 2)-- salary
Comm NUMBER (7Jol 2),-commission or dividend
Deptno NUMBER (2)-Department number
);
Create an AFTER row trigger
CREATE OR REPLACE TRIGGER t_emp_log
AFTER INSERT OR DELETE OR UPDATE ON emp-the table object that the trigger acts as well as the conditions and actions triggered
FOR EACH ROW-triggers at the row level
BEGIN
IF INSERTING THEN-determines whether it is triggered by an INSERT statement
INSERT INTO emp_log-inserts log records into the emp_ log table
VALUES (
Emp_seq.NEXTVAL
'INSERT',SYSDATE
: new.empno,:new.ename,:new.job
: new.mgr,:new.hiredate,:new.sal
: new.comm,:new.deptno)
ELSIF UPDATING THEN-determines whether it is triggered by a UPDATE statement
INSERT INTO emp_log-insert the old record first
VALUES (
Emp_seq.NEXTVAL
'UPDATE_NEW',SYSDATE
: new.empno,:new.ename,:new.job
: new.mgr,:new.hiredate,:new.sal
: new.comm,:new.deptno)
INSERT INTO emp_log-then insert a new record
VALUES (
Emp_seq.CURRVAL
'UPDATE_OLD',SYSDATE
: old.empno,:old.ename,:old.job
: old.mgr,:old.hiredate,:old.sal
: old.comm,:old.deptno)
ELSIF DELETING THEN-if you are deleting a record
INSERT INTO emp_log
VALUES (
Emp_seq.NEXTVAL
'DELETE',SYSDATE
: old.empno,:old.ename,:old.job
: old.mgr,:old.hiredate,:old.sal
: old.comm,:old.deptno)
END IF
END
Update emp set sal=sal*1.12 where deptno=10
Col log_action format A10 domestic-formatted output log_action
Select * from emp_log
