Trigger 7 (compound trigger) (Learning notes), compound trigger learning notes

Trigger 7 (compound trigger) (Learning notes), compound trigger learning notes
Compound triggerA composite trigger is a new structure trigger introduced after Oracle 11g. Composite trigger is both a table-Level Trigger and a row-Level Trigger. For triggers of different levels, if you want to complete table-level triggering (BEFORE and AFTER) and row-level triggering (BEFORE and AFTER) on a data table) you need to write four triggers to complete the process. With a composite trigger, you only need one trigger to define the four functions that are completely unique.The composite trigger can capture four operation events:Composite trigger creation syntax

-- Composite TRIGGER creation syntax CREATE [or replace] TRIGGER name FOR [INSERT | update of column name [, column name,...] | DELETE] ON table name compound trigger [before statement is -- triggers before statement execution (Table Level) [Declaration part;] in program entity part; end before statement;] [before each row is -- triggered BEFORE statement execution (ROW-level) [Declaration part;] in program entity part; end before each row;] [after statement is -- triggered AFTER the statement is executed (Table-level) [Declaration part;] in program subject part; end after statement;] [after each row is -- triggered AFTER the statement IS executed (ROW-level) [Declaration part;] in program subject part; end after each row;] END ;/

Example 1,Create a composite trigger

-- Create a composite trigger create or replace TRIGGER compound_trigger for insert or update or delete on dept compound trigger before statement is -- triggers (Table-level) BEGIN DBMS_OUTPUT.put_line ('1, before statement. '); end before statement; before each row is -- triggers (ROW-level) BEGIN DBMS_OUTPUT.put_line ('2, before each row. '); end before each row; after statement is -- triggered AFTER the statement is executed (Table Level) BEGIN DBMS_OUTPUT.put_line ('3, after statement. '); end after statement; after each row is -- triggered AFTER the statement is executed (ROW-level) BEGIN DBMS_OUTPUT.put_line ('4, after each row. '); end after each row; end compound_trigger;

-- Test insert into dept (deptno, Dname, loc) VALUES (97, 'RD ', 'shenzhen'); SELECT * FROM dept; -- result 1: before statement. 2. before each row. 4. after each row. 3. after statement.

Example 2,

Define a trigger. This trigger can complete the following functions and cannot update emp table data during the weekend. When updating data, it is required that all the added data be automatically converted to uppercase; after the update is complete, the salary of new employees cannot be higher than the company's average salary

-- Create a composite trigger create or replace TRIGGER emp_compound_trigger for insert or update or delete on emp compound trigger before statement is -- table-level, v_curweek VARCHAR2 (20) IS triggered BEFORE the statement is executed; -- Declaration, -- On weekends, you cannot update begin select to_char (SYSDATE, 'day') INTO v_curweek FROM dual; if trim (v_curweek) IN ('saturday', 'sunday') THEN raise_application_error (-20004, 'employee table update not allowed on weekends '); end if; exception when others then dbms_output.put_line (SQLCODE | SQ LERRM); end before statement; before each row is -- ROW-level. v_avgsal emp IS triggered BEFORE the statement is executed. sal % TYPE; -- declare the average wage variable begin if inserting OR updating THEN: new. ename: = upper (: new. ename);: new. job: = upper (: new. job); end if; IF inserting then select avg (sal) INTO v_avgsal FROM emp; IF: new. sal> v_avgsal THEN raise_application_error (-20005, 'the new employee's salary is not much higher than the company's average salary! '); End if; exception when others then dbms_output.put_line (SQLCODE | SQLERRM); end before each row; end emp_compound_trigger;

-- Test insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) VALUES (8888, 'test', 'cler', 7369, SYSDATE, 10 ); -- change the time to insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) VALUES (9999, 'test1', 'cler', 7369, SYSDATE, );