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.
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>
Placing triggers on tables
Statement-level triggers
Use statement-level triggers when you need to check business rules that are not row dependent.
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.
Raise Exception from trigger
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>
Solution for the Mutating Tables Problem
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>
Trigger Which Modifies a Mutating Table
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>
Trigger with a REFERENCING clause
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>
Trigger with multiple triggering events
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>
Trigger with REFERENCING and WHEN clauses
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>