Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.