Updating clause used in instead
Table 9-1 summarizes the timing point sections of a compound trigger that can be defined on a table.
CREATE TABLE employee_salaries ( employee_id NUMBER NOT NULL, change_date DATE NOT NULL, salary NUMBER(8,2) NOT NULL, CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date), CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE) / CREATE OR REPLACE TRIGGER maintain_employee_salaries FOR UPDATE OF salary ON employees COMPOUND TRIGGER -- Declarative Part: -- Choose small threshhold value to show how example works: threshhold CONSTANT SIMPLE_INTEGER := 7; TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER; salaries salaries_t; idx SIMPLE_INTEGER := 0; PROCEDURE flush_array IS n CONSTANT SIMPLE_INTEGER := salaries.count(); BEGIN FORALL j IN 1..n INSERT INTO employee_salaries VALUES salaries(j); salaries.delete(); idx := 0; DBMS_OUTPUT.
The statements in the trigger body operate under the privilege domain of the trigger owner, not the privilege domain of the user issuing the triggering statement (this is similar to the privilege model for stored subprograms).
0) DECLARE sal_diff number; BEGIN sal_diff := : NEW. SAL; dbms_output.put('Old salary: '
A compound trigger defined on a view has an timing-point section, and no other timing-point section.Each subsequent trigger sees the changes made by the previously fired triggers. The old values are the original values, and the new values are the current values, as set by the most recently fired trigger. For example, assume that the table Emp_log was created as follows: The compound trigger makes it easier to program an approach where you want the actions you implement for the various timing points to share common data.The database executes all triggers of the same type before executing triggers of a different type. Deptno) AS Amp_list_ Emplist FROM dept d; CREATE OR REPLACE TRIGGER Dept_emplist_tr INSTEAD OF INSERT ON NESTED TABLE Emplist OF Dept_view REFERENCING NEW AS Employee PARENT AS Department FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table: INSERT INTO emp VALUES (: Employee. To achieve the same effect with simple triggers, you had to model the common state with an ancillary package.Also, if global package variables are updated within a trigger, then it is best to initialize those variables in a , because a cursor must be opened for every execution of a trigger.Although any trigger can run a sequence of operations either inline or by invoking subprograms, using multiple triggers of the same type allows the modular installation of applications that have triggers on the same tables. Deptno); END; CREATE TABLE Project_tab ( Prj_level NUMBER, Projno NUMBER, Resp_dept NUMBER); CREATE TABLE emp ( Empno NUMBER NOT NULL, Ename VARCHAR2(10), Job VARCHAR2(9), Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(7,2), Deptno NUMBER(2) NOT NULL); CREATE TABLE dept ( Deptno NUMBER(2) NOT NULL, Dname VARCHAR2(14), Loc VARCHAR2(13), Mgr_no NUMBER, Dept_type NUMBER); CREATE OR REPLACE VIEW manager_info AS SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level, p.projno FROM emp e, dept d, Project_tab p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE OR REPLACE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW DECLARE rowcnt number; BEGIN SELECT COUNT(*) INTO rowcnt FROM emp WHERE empno = :n.empno; IF rowcnt = 0 THEN INSERT INTO emp (empno,ename) VALUES (:n.empno, :n.ename); ELSE UPDATE emp SET emp.ename = :n.ename WHERE emp.empno = :n.empno; END IF; SELECT COUNT(*) INTO rowcnt FROM dept WHERE deptno = :n.deptno; IF rowcnt = 0 THEN INSERT INTO dept (deptno, dept_type) VALUES(:n.deptno, :n.dept_type); ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; SELECT COUNT(*) INTO rowcnt FROM Project_tab WHERE Project_tab.projno = :n.projno; IF rowcnt = 0 THEN INSERT INTO Project_tab (projno, prj_level) VALUES(:n.projno, :n.prj_level); ELSE UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level WHERE Project_tab.projno = :n.projno; END IF; END; option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.