Part 14 T Oracle TriggersCopyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 2 Oracle Triggers Oracle’s triggers are used throughout the Oracle system as a means of writing event-driven code. Database triggers activate blocks of PL/SQL code whenever the triggering event occurs in the database. Likewise in Oracle Forms, triggers activate blocks of PL/SQL code whenever a triggers event occurs on the form. Triggers are an important component of managing and programming using Oracle. Triggers can be created by writing appropriate SQL statements. Triggers can also be created using the Forms Developer tool set.Copyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 3 List of Oracle Database Triggers AFTER ALTER AFTER ANALYZE AFTER ASSOCIATE STATISTICS AFTER AUDIT AFTER COMMENT AFTER CREATE AFTER DDL AFTER DELETE AFTER DISASSOCIATE STATISTICS AFTER GRANT AFTER INSERT AFTER LOGON AFTER NOAUDIT AFTER RENAME AFTER REVOKE AFTER SERVERERROR AFTER STARTUP AFTER SUSPEND AFTER TRUNCATE AFTER UPDATE BEFORE ALTER BEFORE ANALYZE BEFORE ASSOCIATE STATISTICS BEFORE AUDIT BEFORE COMMENT BEFORE CREATE BEFORE DDL BEFORE DELETE BEFORE DISASSOCIATE STATISTICS BEFORE GRANT BEFORE INSERT BEFORE LOGOFF BEFORE NOAUDIT BEFORE RENAME BEFORE REVOKE BEFORE SHUTDOWN BEFORE TRUNCATE BEFORE UPDATE INSTEAD OF DELETE INSTEAD OF INSERT INSTEAD OF UPDATECopyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 4 PL/SQL to Create a Trigger The basic components of the PL/SQL code required to create a trigger include the following: 1. CREATE TRIGGER or CREATE OR REPLACE TRIGGER 2. specify a name (or schema.name) for the trigger 3. specify the trigger type or types (see previous page) 4. specify the objects to which the triggers apply (database tables, database rows, schemas, databases) 5. specify applicable conditions to fire the trigger WHEN(condition) 6. specify the code to execute when the trigger fires (either write a block of PL/SQL code or call a procedure)Copyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 5 Trigger Examples CREATE TRIGGER trig1 BEFORE INSERT OR DELETE ON table1 <pl/sql code goes here> CREATE TRIGGER trig2 AFTER UPDATE OF table2.field1, table2.field2 ON table2 FOR EACH ROW <pl/sql code goes here> CREATE TRIGGER trig3 BEFORE UPDATE ON table3 WHEN (condition) <pl/sql code goes here> CREATE TRIGGER trig4 INSTEAD OF DELETE ON view1 <pl/sql code goes here> CREATE TRIGGER trig5 INSTEAD OF INSERT OR UPDATE ON view2 <pl/sql code goes here> CREATE TRIGGER trig6 BEFORE TRUNCATE ON table4 <pl/sql code goes here> CREATE TRIGGER trig7 AFTER CREATE ON SCHEMA <pl/sql code goes here> CREATE TRIGGER trig8 AFTER SERVERERROR ON DATABASE <pl/sql code goes here> CREATE TRIGGER trig9 BEFORE SHUTDOWN ON DATABASE <pl/sql code goes here>Copyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 6 Four Categories of Triggers 1. DML events to base tables. before or after delete or insert or update or update of one or more columns for update, specify once per command (default) or once per row affected optionally specify conditions under which trigger is fired 2. DML events to (non-materialized) views allows full range of database activity through views instead of delete or insert or update 3. DDL events before or after alter, analyze, associate statistics, audit, comment, create (but not create database or create controlfile), disassociate statistics, drop, grant, noaudit, rename, revoke, truncate, or specify DDL for any of the above 4. Database events before logoff or shutdown after servererror, logon, startup, suspendCopyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 7 Data Available to PL/SQL Code Data Access: :OLD.field – value in the specified field of the table in question before any change :NEW.field – value in the specified field of the table in question after any change Logical Values: UPDATING – true if the trigger fired by an update INSERTING – true if the trigger fired by an insert DELETING – true if the trigger fired by a delete System Variables: SYSDATE – need only use the name, do not have to put in a statement to “select sysdate from dual;” System Procedure Calls: IS_SERVERERROR(number) – true if server error number matches the specified number I_AM_A_REFRESH – true if a trigger fired on a base table as the result of a refresh of a materialized viewCopyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 8 Database Trigger ExamplesCopyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 9 Forms Developer Triggers Delete-Procedure Insert-Procedure Key-Fn Key-Others Lock-Procedure On-Check-Delete-Master On-Check-Unique On-Clear-Details On-Close On-Column-Security On-Commit On-Count On-Delete On-Dispatch-Event On-Error On-Fetch On-Insert On-Lock On-Logon On-Logout On-Message On-Populate-Details On-Rollback On-Savepoint On-Select On-Sequence-Number On-Update Post-Block Post-Change Post-Database-Commit Post-Delete Post-Form Post-Forms-Commit Post-Insert Post-Logon Post-Logout Post-Query Post-Record Post-Select Post-Text-Item Post-Update Pre-Block Pre-Commit Pre-Delete Pre-Form Pre-Insert Pre-Logon Pre-Logout Pre-Popup-Menu Pre-Query Pre-Record Pre-Select Pre-Text-Item Pre-Update Query-Procedure Update-Procedure When-Button-Pressed When-Checkbox-Changed When-Clear-Block When-Create-Record When-Custom-Item-Event When-Database-Record When-Form-Navigate When-Image-Activated When-Image-Pressed When-List-Activated When-List-Changed When-Mouse-Click When-Mouse-DoubleClick When-Mouse-Down When-Mouse-Enter When-Mouse-Leave When-Mouse-Move When-Mouse-Up When-New-Block-Instance When-New-Form-Instance When-New-Item-Instance When-New-Record-Instance When-Radio-Changed When-Remove-Record When-Tab-Page-Changed When-Timer-Expired When-Validate-Item When-Validate-Record When-Window-Activated When-Window-Closed When-Window-Deactivated When-Window-ResizedCopyright © 1971-2002 Thomas P. Sturm Oracle Triggers Part 14T, Page 10 Triggers in Forms Developer Forms developer triggers are completely independent of database triggers. Forms triggers are activated only when the forms are used. By contrast, database triggers are activated regardless of how the database changes are made – via SQL*Plus or Forms or ODBC. When forms are used, forms triggers are fired on the form in addition