Oracle PL/SQL Tutorial/Trigger/Introduction
Содержание
- 1 A trigger is an event within the DBMS that can cause some code to execute automatically.
- 2 Placing triggers on tables
- 3 Raise Exception from trigger
- 4 Solution for the Mutating Tables Problem
- 5 Trigger Which Modifies a Mutating Table
- 6 Trigger with a REFERENCING clause
- 7 Trigger with multiple triggering events
- 8 Trigger with REFERENCING and WHEN clauses
A trigger is an event within the DBMS that can cause some code to execute automatically.
There are four types of database triggers:
- Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event.
- View-level triggers defines what can be done to the view.
- Database-level triggers can be activated at startup and shutdown of a database.
- Session-level triggers can be used to store specific information.
<source lang="sql">
SQL> SQL> SQL> create table company(
2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 );
Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> create table product_audit(
2 product_id number(4) not null, 3 num_rows number(8) not null 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTrigger
2 AFTER INSERT ON company 3 FOR EACH ROW 4 BEGIN 5 UPDATE product_audit 6 SET num_rows =num_rows+1 7 WHERE product_id =:NEW.product_id; 8 IF (SQL%NOTFOUND) THEN 9 INSERT INTO product_audit VALUES (:NEW.product_id,1); 10 END IF; 11 END; 12 /
Trigger created. SQL> SQL> drop table product_audit; Table dropped. SQL> SQL> drop table company; Table dropped. SQL> SQL></source>
Placing triggers on tables
Statement-level triggers
Use statement-level triggers when you need to check business rules that are not row dependent.
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> create or replace trigger emp_bid
2 before insert or delete
3 on employee
4 referencing new as new old as old
5 begin
6 if to_char(sysdate,"Dy") in ("Sat","Sun") then
7 raise_application_error(-20999,"No create/delete employees on weekend!");
8 end if;
9 end;
10 /
Trigger created. SQL> SQL> delete from employee; 8 rows deleted. SQL> SQL> select * from employee; no rows selected SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
Raise Exception from trigger
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> create or replace trigger emp_biu
2 before insert or update on employee 3 referencing new as new old as old 4 for each row 5 begin 6 if nvl(:new.salary,0) >= 10000 then 7 raise_application_error (-20999,"Salary with 8 commissions should be less than 10000"); 9 end if; 10 end; 11 /
Trigger created. SQL> SQL> update employee set salary = 20000; update employee set salary = 20000
*
ERROR at line 1: ORA-20999: Salary with commissions should be less than 10000 ORA-06512: at "sqle.EMP_BIU", line 3 ORA-04088: error during execution of trigger "sqle.EMP_BIU"
SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Solution for the Mutating Tables Problem
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE EmployeeData AS
2 TYPE t_Salary IS TABLE OF employee.salary%TYPE INDEX BY BINARY_INTEGER; 3 TYPE t_IDs IS TABLE OF employee.ID%TYPE INDEX BY BINARY_INTEGER; 4 5 v_EmployeeSalary t_Salary; 6 v_EmployeeIDs t_IDs; 7 v_NumEntries BINARY_INTEGER := 0; 8 END EmployeeData; 9 /
Package created. SQL> SQL> CREATE OR REPLACE TRIGGER RLimitSalary
2 BEFORE INSERT OR UPDATE OF salary ON employee 3 FOR EACH ROW 4 BEGIN 5 /* Record the new data in EmployeeData. We don"t make any 6 changes to employee, to avoid the ORA-4091 error. */ 7 EmployeeData.v_NumEntries := EmployeeData.v_NumEntries + 1; 8 EmployeeData.v_EmployeeSalary(EmployeeData.v_NumEntries) := :new.salary; 9 EmployeeData.v_EmployeeIDs(EmployeeData.v_NumEntries) := :new.id; 10 END RLimitSalary; 11 /
Trigger created. SQL> SQL> CREATE OR REPLACE TRIGGER SLimitSalary
2 AFTER INSERT OR UPDATE OF salary ON employee 3 DECLARE 4 v_MaxEmployees CONSTANT NUMBER := 5; 5 v_CurrentEmployees NUMBER; 6 v_EmployeeID employee.ID%TYPE; 7 v_Salary employee.salary%TYPE; 8 BEGIN 9 /* Loop through each student inserted or updated, and verify 10 that we are still within the limit. */ 11 FOR v_LoopIndex IN 1..EmployeeData.v_NumEntries LOOP 12 v_EmployeeID := EmployeeData.v_EmployeeIDs(v_LoopIndex); 13 v_Salary := EmployeeData.v_EmployeeSalary(v_LoopIndex); 14 15 16 SELECT COUNT(*) 17 INTO v_CurrentEmployees 18 FROM employee 19 WHERE salary = v_Salary; 20 21 -- If there isn"t room, raise an error. 22 IF v_CurrentEmployees > v_MaxEmployees THEN 23 RAISE_APPLICATION_ERROR(-20000, 24 "Too much salary " || v_Salary || 25 " because of employee " || v_EmployeeID); 26 END IF; 27 END LOOP; 28 29 -- Reset the counter so the next execution will use new data. 30 EmployeeData.v_NumEntries := 0; 31 END SLimitSalary; 32 /
Trigger created. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Trigger Which Modifies a Mutating Table
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 4 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER LimitSalary
2 BEFORE INSERT OR UPDATE OF salary ON employee 3 FOR EACH ROW 4 DECLARE 5 v_MaxSalary CONSTANT NUMBER := 2000; 6 v_CurrentSalary NUMBER; 7 BEGIN 8 SELECT salary 9 INTO v_CurrentSalary 10 FROM employee 11 WHERE id = :new.id; 12 13 IF v_CurrentSalary > v_MaxSalary THEN 14 RAISE_APPLICATION_ERROR(-20000, "Too high in salary " || :new.id); 15 END IF; 16 END LimitSalary; 17 /
Trigger created. SQL> SQL> SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1: ORA-01403: no data found ORA-06512: at "sqle.LIMITSALARY", line 5 ORA-04088: error during execution of trigger "sqle.LIMITSALARY"
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1: ORA-01403: no data found ORA-06512: at "sqle.LIMITSALARY", line 5 ORA-04088: error during execution of trigger "sqle.LIMITSALARY"
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1: ORA-01403: no data found ORA-06512: at "sqle.LIMITSALARY", line 5 ORA-04088: error during execution of trigger "sqle.LIMITSALARY"
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1: ORA-01403: no data found ORA-06512: at "sqle.LIMITSALARY", line 5 ORA-04088: error during execution of trigger "sqle.LIMITSALARY"
SQL> SQL> SQL> select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 4 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Trigger with a REFERENCING clause
<source lang="sql">
SQL> SQL> SQL> create table company(
2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 );
Table created. SQL> create table product_audit(
2 product_id number(4) not null, 3 num_rows number(8) not null 4 );
Table created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTrigger
2 AFTER INSERT ON company 3 REFERENCING NEW AS new_org 4 FOR EACH ROW 5 BEGIN 6 UPDATE product_audit 7 SET num_rows =num_rows+1 8 WHERE product_id =:new_org.product_id; 9 IF (SQL%NOTFOUND) THEN 10 INSERT INTO product_audit VALUES (:new_org.product_id,1); 11 END IF; 12 END; 13 /
Trigger created. SQL> SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> SQL> drop table product_audit; Table dropped. SQL> SQL> drop table company; Table dropped. SQL></source>
Trigger with multiple triggering events
<source lang="sql">
SQL> SQL> create table company(
2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 );
Table created. SQL> create table product_audit(
2 product_id number(4) not null, 3 num_rows number(8) not null 4 );
Table created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTrigger
2 AFTER INSERT OR DELETE ON company 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 UPDATE product_audit 7 SET num_rows =num_rows+1 8 WHERE product_id =:NEW.product_id; 9 IF (SQL%NOTFOUND) THEN 10 INSERT INTO product_audit VALUES (:NEW.product_id,1); 11 END IF; 12 ELSIF DELETING THEN 13 UPDATE product_audit 14 SET num_rows =num_rows-1 15 WHERE product_id =:OLD.product_id; 16 END IF; 17 END; 18 /
Trigger created. SQL> SQL> SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> SQL> SQL> drop table product_audit; Table dropped. SQL> SQL> drop table company; Table dropped. SQL></source>
Trigger with REFERENCING and WHEN clauses
<source lang="sql">
SQL> SQL> create table company(
2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 );
Table created. SQL> create table product_audit(
2 product_id number(4) not null, 3 num_rows number(8) not null 4 );
Table created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE TRIGGER myTrigger
2 AFTER INSERT ON company 3 REFERENCING NEW AS new_org 4 FOR EACH ROW 5 WHEN (new_org.product_id <>1) 6 BEGIN 7 UPDATE product_audit 8 SET num_rows =num_rows+1 9 WHERE product_id =:new_org.product_id; 10 IF (SQL%NOTFOUND) THEN 11 INSERT INTO product_audit VALUES (:new_org.product_id,1); 12 END IF; 13 END; 14 /
Trigger created. SQL> SQL> SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> SQL> SQL> drop table product_audit; Table dropped. SQL> SQL> drop table company; Table dropped. SQL></source>
