Oracle PL/SQL Tutorial/Trigger/Trigger
Версия от 13:45, 26 мая 2010; (обсуждение)
Содержание
- 1 Call raise_application_error to report an error in a trigger
- 2 Check the status of the trigger
- 3 Creating a Trigger
- 4 DML Trigger Example
- 5 Firing a Trigger
- 6 INSERTING, DELETING and UPDATING Predicates
- 7 Reference current user name in trigger
- 8 Show errors for a trigger
- 9 System triggers
- 10 The use of a trigger
- 11 Trigger Blocks
- 12 Triggers
- 13 Trigger that output old value
- 14 Use cursor in trigger
Call raise_application_error to report an error in a trigger
SQL>
SQL>
SQL> CREATE TABLE employee (
2 id number,
3 name varchar(100),
4 birth_date date,
5 gender varchar2(30) );
Table created.
SQL>
SQL> CREATE TRIGGER employee_bir
2 BEFORE INSERT ON employee
3 FOR EACH ROW
4 begin
5 if upper(:new.name) = "J" then
6 raise_application_error(20000, "Sorry, that genius is not allowed.");
7 end if;
8 end;
9 /
Trigger created.
SQL>
SQL> INSERT INTO employee (id,name,birth_date,gender )VALUES (100,"J",to_date("19230823", "YYYYMMDD"),"MALE" );
INSERT INTO employee (id,name,birth_date,gender )VALUES (100,"J",to_date("19230823", "YYYYMMDD"),"MALE" )
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of 20000
is out of range
ORA-06512: at "sqle.EMPLOYEE_BIR", line 3
ORA-04088: error during execution of trigger "sqle.EMPLOYEE_BIR"
SQL>
SQL> drop table employee;
Table dropped.
Check the status of the trigger
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER emp_trig
2 BEFORE UPDATE OF fname
3 ON emp
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("First Name "||:OLD.fname||" has change to "||:NEW.fname);
7 END;
8 /
Trigger created.
SQL>
SQL>
SQL> SELECT trigger_name, status FROM user_triggers;
TRIGGER_NAME STATUS
------------------------------ --------
EMP_TRIG ENABLED
INVENTORY_TRG DISABLED
DEMO_BIFER DISABLED
DEPT_SAL_TRG DISABLED
SYSTEMALTERUSER DISABLED
USAGE_START DISABLED
USAGE_STOP DISABLED
USER_LOGON_MODULE_CHECK DISABLED
8 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Creating a Trigger
You create a trigger using the CREATE TRIGGER statement.
The simplified syntax for the CREATE TRIGGER statement is as follows:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} trigger_event
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
BEGIN
trigger_body
END trigger_name;
DML Trigger Example
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> -- display data in the table
SQL> select * from Employee
2 /
no rows selected
SQL>
SQL> create or replace trigger emp_biu
2 BEFORE INSERT OR UPDATE
3 of salary
4 on employee
5 for each row
6 declare
7 v_error VARCHAR2(2000);
8 begin
9 if :new.salary > 10000
10 then
11 v_error:=:old.first_name||" cannot have that much!";
12 raise_application_error(-20999,v_error);
13 end if;
14
15
16 end;
17 /
Trigger 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"), 11232.78,"Vancouver", "Tester")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1:
ORA-20999: cannot have that much!
ORA-06512: at "sqle.EMP_BIU", line 7
ORA-04088: error during execution of trigger "sqle.EMP_BIU"
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Firing a Trigger
SQL>
SQL>
SQL>
SQL> create table myaudit(
2 id VARCHAR2(4 BYTE) NOT NULL,
3 old_value VARCHAR2(40 BYTE),
4 new_value VARCHAR2(40 BYTE)
5 );
Table created.
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 TRIGGER before_employee_salary_update
2 BEFORE UPDATE OF salary
3 ON employee
4 FOR EACH ROW WHEN (new.salary < old.salary * 0.75)
5 BEGIN
6 dbms_output.put_line("id = " || :old.id);
7 dbms_output.put_line("Old salary = " || :old.salary);
8 dbms_output.put_line("New salary = " || :new.salary);
9 dbms_output.put_line("The salary reduction is more than 25%");
10
11 INSERT INTO Myaudit (
12 id, old_value, new_value
13 ) VALUES (
14 :old.id, :old.salary, :new.salary
15 );
16 END before_employee_salary_update;
17 /
Trigger created.
SQL>
SQL> update employee set salary = 0;
8 rows updated.
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 0 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 0 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 0 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 0 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 0 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 0 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 0 New York Manager
08 James Cat 17-SEP-96 15-APR-02 0 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from myaudit;
ID OLD_VALUE NEW_VALUE
---- ---------------------------------------- ----------------------------------------
01 1234.56 0
02 6661.78 0
03 6544.78 0
04 2344.78 0
05 2334.78 0
06 4322.78 0
07 7897.78 0
08 1232.78 0
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL> drop table myaudit;
Table dropped.
SQL>
SQL>
INSERTING, DELETING and UPDATING Predicates
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> CREATE OR REPLACE TRIGGER LogRSChanges
2 BEFORE INSERT OR DELETE OR UPDATE ON employee
3 FOR EACH ROW
4 DECLARE
5 v_ChangeType CHAR(1);
6 BEGIN
7 /* Use "I" for an INSERT, "D" for DELETE, and "U" for UPDATE. */
8 IF INSERTING THEN
9 v_ChangeType := "I";
10 ELSIF UPDATING THEN
11 v_ChangeType := "U";
12 ELSE
13 v_ChangeType := "D";
14 END IF;
15
16 DBMS_OUTPUT.put_line(v_ChangeType ||" "|| USER ||" " ||SYSDATE);
17 END LogRSChanges;
18 /
Trigger created.
SQL>
SQL> update employee set id = "00";
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
8 rows updated.
SQL>
SQL> delete from employee;
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
8 rows deleted.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Reference current user name in trigger
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> CREATE OR REPLACE TRIGGER LogRSChanges
2 BEFORE INSERT OR DELETE OR UPDATE ON employee
3 FOR EACH ROW
4 DECLARE
5 v_ChangeType CHAR(1);
6 BEGIN
7 /* Use "I" for an INSERT, "D" for DELETE, and "U" for UPDATE. */
8 IF INSERTING THEN
9 v_ChangeType := "I";
10 ELSIF UPDATING THEN
11 v_ChangeType := "U";
12 ELSE
13 v_ChangeType := "D";
14 END IF;
15
16 DBMS_OUTPUT.put_line(v_ChangeType ||" "|| USER ||" " ||SYSDATE);
17 END LogRSChanges;
18 /
Trigger created.
SQL>
SQL> update employee set id = "00";
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
U sqle 07-JUN-07
8 rows updated.
SQL>
SQL> delete from employee;
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
D sqle 07-JUN-07
8 rows deleted.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Show errors for a trigger
SQL>
SQL> CREATE TABLE gifts (
2 gift_id INTEGER CONSTRAINT gifts_pk PRIMARY KEY,
3 gift_type_id INTEGER ,
4 name VARCHAR2(30) NOT NULL,
5 description VARCHAR2(50),
6 price NUMBER(5, 2)
7 );
Table created.
SQL>
SQL>
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, "Flower", "Birthday", 19.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, "Computer", "New Year", 30.00);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, "iPod", "Birthday", 25.99);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, "iPhone", "New Year", 13.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, "Book", "Birthday", 49.99);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER before_gift_price_update
2 BEFORE UPDATE OF price
3 ON gifts
4 FOR EACH ROW WHEN (new.price < old.price * 0.75)
5 BEGIN
6 dbms_output.put_line("gift_id = " || :old.gift_id);
7 dbms_output.put_line("Old price = " || :old.price);
8 dbms_output.put_line("New price = " || :new.price);
9 dbms_output.put_line("The price reduction is more than 25%");
10
11 INSERT INTO gift_price_audit (gift_id, old_price, new_price)VALUES(:old.gift_id, :old.price, :new.price);
12 END before_gift_price_update;
13 /
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER BEFORE_GIFT_PRICE_UPDATE:
LINE/COL
---------------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------------------------------------------------------------------------------------
7/3
PL/SQL: SQL Statement ignored
7/15
PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL>
SQL> drop table gifts;
Table dropped.
System triggers
There are a number of events where you can set system triggers such as
ON LOGON, ON LOGOFF, ON STARTUP, ON DROP, ON TRUNCATE, and so on.
You can even track when any DDL command (CREATE, DROP, ALTER, and so on) was executed in the database.
You may place system triggers at the database level or schema level.
At the database level, triggers fire for each event for all users.
At the schema level, triggers fire for each event for a specific user.
28. 2. Trigger 28. 2. 1. <A href="/Tutorial/Oracle/0560__Trigger/Triggers.htm">Triggers</a> 28. 2. 2. System triggers 28. 2. 3. <A href="/Tutorial/Oracle/0560__Trigger/CreatingaTrigger.htm">Creating a Trigger</a> 28. 2. 4. <A href="/Tutorial/Oracle/0560__Trigger/TriggerBlocks.htm">Trigger Blocks</a> 28. 2. 5. <A href="/Tutorial/Oracle/0560__Trigger/Triggerthatoutputoldvalue.htm">Trigger that output old value</a> 28. 2. 6. <A href="/Tutorial/Oracle/0560__Trigger/DMLTriggerExample.htm">DML Trigger Example</a> 28. 2. 7. <A href="/Tutorial/Oracle/0560__Trigger/FiringaTrigger.htm">Firing a Trigger</a> 28. 2. 8. <A href="/Tutorial/Oracle/0560__Trigger/Theuseofatrigger.htm">The use of a trigger</a> 28. 2. 9. <A href="/Tutorial/Oracle/0560__Trigger/INSERTINGDELETINGandUPDATINGPredicates.htm">INSERTING, DELETING and UPDATING Predicates</a> 28. 2. 10. <A href="/Tutorial/Oracle/0560__Trigger/Referencecurrentusernameintrigger.htm">Reference current user name in trigger</a> 28. 2. 11. <A href="/Tutorial/Oracle/0560__Trigger/Callraiseapplicationerrortoreportanerrorinatrigger.htm">Call raise_application_error to report an error in a trigger</a> 28. 2. 12. <A href="/Tutorial/Oracle/0560__Trigger/Usecursorintrigger.htm">Use cursor in trigger</a> 28. 2. 13. <A href="/Tutorial/Oracle/0560__Trigger/Checkthestatusofthetrigger.htm">Check the status of the trigger</a> 28. 2. 14. <A href="/Tutorial/Oracle/0560__Trigger/Showerrorsforatrigger.htm">Show errors for a trigger</a>
The use of a trigger
SQL>
SQL> set feedback on
SQL> set pages 9999 serveroutput on size 1000000
SQL>
SQL> CREATE TABLE authors (
2 id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (1, "Marlene", "Theriault");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (2, "Rachel", "Carmichael");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (3, "James", "Viscusi");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (4, "Michael", "Abbey");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (5, "Michael", "Corey");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (6, "Scott", "Urman");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (7, "Ron", "Hardman");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (8, "Mike", "McLaughlin");
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL>
SQL> SELECT id, first_name, last_name
2 FROM authors
3 WHERE last_name = "HARDMAN";
no rows selected
SQL>
SQL> CREATE OR REPLACE TRIGGER author_trig
2 AFTER UPDATE OF first_name
3 ON authors
4 FOR EACH ROW
5 WHEN (OLD.first_name != NEW.first_name)
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE("First Name "
8 ||:OLD.first_name
9 ||" has change to "
10 ||:NEW.first_name);
11 END;
12 /
Trigger created.
SQL>
SQL> UPDATE authors
2 SET first_name = "Ronald"
3 WHERE first_name = "Ron";
First Name Ron has change to Ronald
1 row updated.
SQL>
SQL>
SQL>
SQL> drop table authors;
Table dropped.
Trigger Blocks
Triggers are used to define code that is executed when certain actions or events occur.
The Syntax for Creating a Database Trigger
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} verb_list ON table_name
[[REFERENCING correlation_names] FOR EACH ROW [WHEN (condition)]]
DECLARE
declarations
BEGIN
pl/sql_code
END;
Triggers
A trigger is a procedure that is run automatically by the database when a specified SQL DML INSERT, UPDATE, or DELETE statement is run against a table.
Triggers are useful for doing things like advanced auditing of changes made to column values in a table.
When a Trigger Runs
- A trigger can fire before or after the SQL statement runs.
- A trigger can may be run once for every row affected. Such a trigger is known as a row-level trigger.
- A trigger can may be run for all the rows. Such trigger is known as a statement-level trigger.
- A row-level trigger has access to the old and new column values when the trigger fires as a result of an UPDATE statement on that column.
- The firing of a trigger may also be limited using a trigger condition.
Different events may fire a trigger, but these events are always divided into three groups:
- DML triggers,
- INSTEAD OF triggers, and
- system event triggers.
DML triggers are the triggers on INSERT/UPDATE/DELETE operations in any table.
SQL> CREATE TABLE employee_history (
2 name VARCHAR2(100),
3 description VARCHAR2(255),
4 occurred_on DATE);
Table created.
SQL>
SQL>
SQL> CREATE TABLE employee_compensation (
2 company VARCHAR2(100),
3 name VARCHAR2(100),
4 compensation NUMBER,
5 layoffs NUMBER);
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE employee_audit (
2 name IN VARCHAR2,
3 description IN VARCHAR2,
4 occurred_on IN DATE
5 )
6 IS
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 INSERT INTO employee_history VALUES (
10 employee_audit.name,
11 employee_audit.description,
12 employee_audit.occurred_on
13 );
14 COMMIT;
15 END;
16 /
Procedure created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
2 BEFORE INSERT ON employee_compensation FOR EACH ROW
3 DECLARE
4 ok BOOLEAN := TRUE;
5 BEGIN
6 IF ok
7 THEN
8 employee_audit (
9 :new.name, "BEFORE INSERT", SYSDATE);
10 END IF;
11 END;
12 /
Trigger created.
SQL>
SQL>
SQL>
SQL> SELECT name,
2 description,
3 TO_CHAR (occurred_on, "MM/DD/YYYY HH:MI:SS") occurred_on
4 FROM employee_history;
no rows selected
SQL>
SQL> BEGIN
2 INSERT INTO employee_compensation VALUES ("M", "J", 9100000, 2700);
3
4 INSERT INTO employee_compensation VALUES ("A", "H", 33200000, 3300);
5
6 INSERT INTO employee_compensation VALUES ("E", "G", 10700000, 20100);
7
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name,
2 description,
3 TO_CHAR (occurred_on, "MM/DD/YYYY HH:MI:SS") occurred_on
4 FROM employee_history;
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------
J
BEFORE INSERT
07/24/2008 08:03:08
H
BEFORE INSERT
07/24/2008 08:03:08
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------
G
BEFORE INSERT
07/24/2008 08:03:08
SQL>
SQL>
SQL> DROP TABLE employee_compensation;
Table dropped.
SQL>
SQL> DROP TABLE employee_history;
Table dropped.
Trigger that output old value
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> SET SERVEROUTPUT ON
SQL>
SQL> CREATE OR REPLACE TRIGGER emp_dept_del
2 AFTER DELETE ON employee
3 FOR EACH ROW
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE (":OLD.id" || :OLD.id);
6
7
8 END;
9 /
Trigger created.
SQL>
SQL> delete from employee;
:OLD.id01
:OLD.id02
:OLD.id03
:OLD.id04
:OLD.id05
:OLD.id06
:OLD.id07
:OLD.id08
8 rows deleted.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Use cursor in trigger
SQL>
SQL> CREATE TABLE customer
2 (customer_id NUMBER(7),
3 customer_name VARCHAR2(50),
4 phone VARCHAR2(15),
5 address VARCHAR2(400),
6 city VARCHAR2(35),
7 state VARCHAR2(30),
8 country VARCHAR2(30),
9 zip_code VARCHAR2(10),
10 credit_rating VARCHAR2(9),
11 sales_rep_id NUMBER(7),
12 region_id NUMBER(7),
13 comments VARCHAR2(255),
14 preferred_customer VARCHAR2(1) DEFAULT "N" NOT NULL,
15 shipping_method VARCHAR2(1) DEFAULT "M" NOT NULL);
Table created.
SQL>
SQL> INSERT INTO customer VALUES (201, "Jane", "111-1111", "7 AVE","SAO", NULL, "BRAZIL", NULL, "EXCELLENT",12, 2, "A", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (202, "Todd", "222-2222", "6 BLVD.","OSAKA", NULL, "JAPAN", NULL, "POOR", 14, 4, "B", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (203, "Sharon", "333-3333", "1 STREET", "NEW DELHI", NULL, "INDIA", NULL, "GOOD", 14, 4,"C", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (204, "Hong", "444-4444", "2 STREET","SEATTLE", "WASHINGTON", "USA", "98101", "EXCELLENT",11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (205, "Anderson","555-5555", "5 ROAD", "HONG KONG", NULL, NULL,NULL, "EXCELLENT", 15, 4, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (206, "Bob", "666-6666", "1 ROAD","CANNES", NULL, "FRANCE", NULL, "EXCELLENT", 15, 5,"D", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (207, "Cat", "777-7777", "6 STREET","LAGOS", NULL, "NIGERIA", NULL, "GOOD", NULL, 3, NULL,"N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (208, "Doge", "888-8888", "4 RASSE", "STUTTGART", NULL, "GERMANY", NULL, "GOOD", 15, 5,"E", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (209, "Black", "999-9999", "2 MAR","SAN PEDRO DE MACON""S", NULL, "DOMINICAN REPUBLIC",NULL, "EXCELLENT", 11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (210, "Red", "000-0000", "3 ARO","NOGALES", NULL, "MEXICO", NULL, "EXCELLENT", 12, 2,"Customer is difficult to reach by phone. Try mail.","N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (211, "Ted", "123-1231", "7 MOD", "PRAGUE",NULL, "CZECHOSLOVAKIA", NULL, "EXCELLENT", 15, 5, NULL,"N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (212, "Homas", "124-1234", "5 COR","ALEXANDRIA", NULL, "EGYPT", NULL, "EXCELLENT", 13, 3,"F", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (213, "Look", "555-6281", "4 STREET", "SAN FRANCISCO", "CA", "USA", "94117","EXCELLENT", 11, 1, "G", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (214, "Yellow", "555-7171", "4 STREET","BUFFALO", "NY", "USA", "14202", "POOR", 11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (215, "White", "337-3892", "6 YEK","SAINT PETERSBURG", NULL, "RUSSIA", NULL, "POOR",15, 5, "T", "N", "M");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE ord
2 (order_id NUMBER(7),
3 customer_id NUMBER(7),
4 date_ordered DATE,
5 date_shipped DATE,
6 sales_rep_id NUMBER(7),
7 total NUMBER(11, 2),
8 payment_type VARCHAR2(6),
9 order_filled VARCHAR2(1)
10 );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO ord VALUES (100, 204, "31-AUG-92", "10-SEP-92", 11, 601100, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (101, 205, "31-AUG-92", "15-SEP-92", 14, 8056.6, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (102, 206, "01-SEP-92", "08-SEP-92", 15, 8335, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (103, 208, "02-SEP-92", "22-SEP-92", 15, 377, "CASH", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (104, 208, "03-SEP-92", "23-SEP-92", 15, 32430, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (105, 209, "04-SEP-92", "18-SEP-92", 11, 2722.24, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (106, 210, "07-SEP-92", "15-SEP-92", 12, 15634, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (107, 211, "07-SEP-92", "21-SEP-92", 15, 142171, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (108, 212, "07-SEP-92", "10-SEP-92", 13, 149570, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (109, 213, "08-SEP-92", "28-SEP-92", 11, 1020935, "CREDIT", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (110, 214, "09-SEP-92", "21-SEP-92", 11, 1539.13, "CASH", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (111, 204, "09-SEP-92", "21-SEP-92", 11, 2770, "CASH", "Y");
1 row created.
SQL> INSERT INTO ord VALUES (112, 210, "31-AUG-92", "10-SEP-92", 12, 550, "CREDIT", "Y");
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER bi_order
2 BEFORE INSERT
3 ON ord
4 REFERENCING OLD AS OLD NEW AS NEW
5 FOR EACH ROW
6 WHEN (NEW.payment_type = "CREDIT")
7 DECLARE
8 CURSOR cur_check_customer IS
9 SELECT "x"
10 FROM customer
11 WHERE customer_id = :NEW.customer_id
12 AND credit_rating = "POOR";
13 lv_temp_txt VARCHAR2(1);
14 lv_poor_credit_excep EXCEPTION;
15 BEGIN
16 OPEN cur_check_customer;
17 FETCH cur_check_customer INTO lv_temp_txt;
18 IF (cur_check_customer%FOUND) THEN
19 CLOSE cur_check_customer;
20 RAISE lv_poor_credit_excep;
21 ELSE
22 CLOSE cur_check_customer;
23 END IF;
24 EXCEPTION
25 WHEN lv_poor_credit_excep THEN
26 RAISE_APPLICATION_ERROR(-20111, "Cannot process CREDIT " ||
27 "order for a customer with a POOR credit rating.");
28 WHEN OTHERS THEN
29 RAISE_APPLICATION_ERROR(-20122, "Unhandled error occurred in" ||
30 " BI_ORDER trigger for order#:" || TO_CHAR(:NEW.ORDER_ID));
31 END bi_order;
32 /
Trigger created.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table customer;
Table dropped.
SQL>
SQL> drop table ord;
Table dropped.
SQL>