Use of Oracle triggers

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

1 2 Next Page