Oracle PL/SQL Tutorial/Trigger/Trigger

Материал из SQL эксперт
Перейти к: навигация, поиск

Call raise_application_error to report an error in a trigger

   <source lang="sql">

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.</source>


Check the status of the trigger

   <source lang="sql">

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></source>


Creating a Trigger

You create a trigger using the CREATE TRIGGER statement.

The simplified syntax for the CREATE TRIGGER statement is as follows:



   <source lang="sql">

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;</source>


DML Trigger Example

   <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> -- 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></source>


Firing a Trigger

   <source lang="sql">

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></source>


INSERTING, DELETING and UPDATING Predicates

   <source lang="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.</source>


Reference current user name in trigger

   <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> 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.</source>


Show errors for a trigger

   <source lang="sql">

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.</source>


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

   <source lang="sql">

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.</source>


Trigger Blocks

Triggers are used to define code that is executed when certain actions or events occur.

The Syntax for Creating a Database Trigger



   <source lang="sql">

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;</source>


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

  1. A trigger can fire before or after the SQL statement runs.
  2. A trigger can may be run once for every row affected. Such a trigger is known as a row-level trigger.
  3. A trigger can may be run for all the rows. Such trigger is known as a statement-level trigger.
  4. 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.
  5. 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:

  1. DML triggers,
  2. INSTEAD OF triggers, and
  3. system event triggers.

DML triggers are the triggers on INSERT/UPDATE/DELETE operations in any table.



   <source lang="sql">

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.</source>


Trigger that output old value

   <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> 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.</source>


Use cursor in trigger

   <source lang="sql">

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></source>