Use of Oracle triggers

It is associated with a table or database event. When a trigger event occurs, the trigger defined in the table is triggered and executed. The system event trigger can be

It is associated with a table or database event. When a trigger event occurs, the trigger defined in the table is triggered and executed. The system event trigger can be

1. Definition

Trigger: it is associated with a table or database event. When a trigger event occurs, the trigger defined in the table is triggered and executed.

Trigger trigger order

1) execute the BEFORE statement-Level Trigger;

2) for each row affected by the statement:

· Execute the BEFORE row-Level Trigger

· Execute DML statements

· Execute the AFTER row-Level Trigger

3) execute the AFTER statement-Level Trigger

2. DML triggers

Trigger type-row-level and table-level

Row-level: when the trigger statement processes each row, the row-Level Trigger is triggered once.

Use: old to access the original value and: new to access the modified value.: old and: new are valid only within the trigger.

Trigger time -- before and after

Create a trigger. When a record is deleted from the employee table emp, write the deleted record to the employee table deletion log table.

Create and delete a log table

Create table emp_his

As select * FROM EMP1 WHERE 1 = 2;

Create a trigger

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 enables Oracle to activate the trigger without executing the trigger event.

The instead-of trigger can only be defined on the view. You can modify a view that cannot be modified.

Changeable view: if operations on a view are performed on the base table, only one base table is allowed.

You cannot change the view.

Create a test View

Create or replace view emp_view

SELECT deptno, COUNT (*) total_employee, SUM (sal) total_salary

FROM emp1 group by deptno;

-- SELECT * FROM user_views;

View operation Error

Delete from emp_view WHERE deptno = 10;

Create an alternative 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

,