Use of Oracle Triggers

1. Definition

Triggers: associated with a table or database event, a trigger defined on a table triggers execution when a trigger event occurs.

Trigger Trigger Order

1 execute BEFORE statement level trigger;

2 for each row affected by the statement:

· Executing before row-level triggers

· To execute a DML statement

· Execute after row-level triggers

3 execution after statement level triggers

2. DML triggers

Trigger Type--row level and table level

Row level: The row-level triggers are fired once for each row of the triggering statement.

Through: Old access to the original value,: New access to the modified value,: Old and: New is only valid within the trigger.

Trigger time--before and after

Create a trigger to write the deleted record to the Employee table delete log table when the Employee table EMP table is deleted a record

Create a delete log table

CREATE TABLE Emp_his

As SELECT * from EMP1 WHERE 1=2;

Creating triggers

CREATE OR REPLACE TRIGGER del_emp

Before DELETE OR UPDATE on SCOTT.EMP1 for each ROW

BEGIN

INSERT into Emp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)

VALUES (: Old.deptno,: Old.empno,: Old.ename,: Old.job,

: Old.mgr,: old.sal,: Old.comm,: old.hiredate);

End;

3, instead-of Trigger:

The INSTEAD of option causes the Oracle to activate the trigger without executing the trigger event

INSTEAD-OF triggers can be defined only on views, allowing you to modify a view that cannot be modified.

You can change the view to refer to the following: If the action of the views is a modification of the base table, there is only one base table.

Cannot change view instead.

Set up a Test view

CREATE OR REPLACE VIEW Emp_view as

SELECT Deptno,count (*) total_employee,sum (SAL) total_salary

From Emp1 GROUP by Deptno;

--select * from User_views;

Error in view action

DELETE from Emp_view WHERE deptno=10;

Create an alternate Trigger

CREATE OR REPLACE TRIGGER emp_view_delete

INSTEAD of DELETE on Emp_view for each ROW

BEGIN

DELETE from Emp1 WHERE Deptno=:old.deptno;

End;

Execute Delete again