Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI PL/SQL Triggers Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Triggers • Trigger Overview • A trigger is a procedure which is executed implicitly whenever the triggering event happens. • Executing a trigger is to “fire” the trigger • Triggering Events are: • INSERT • UPDATE • DELETE • DDL Commands – ALTER TABLE, etc. • Database Events – logins, startups, shutdowns, etc. • Uses for triggers: • Maintain complex integrity constraints. • Record auditing information about database changes. • Send a signal to a program that processing needs to be performed when a table changes.

3. Triggers – When To Use Them • First, use declarative referential integrity (RI) in DDL instead of triggers. While triggers can be used to implement RI, it is better practice to declare primary keys, references and constraints. • Declarative RI exposes RI in data dictionary and makes Oracle responsible for enforcing RI instead of you writing code. • Use BEFORE ROW triggers to derive columns. • Use AFTER ROW triggers to call stored procedures once you know the row is successfully applied.

4. Triggers • Trigger Syntax:CREATE OR REPLACE TRIGGER trigger_nameBEFORE | AFTERDELETE | INSERT | UPDATE OF column ON table_name FOR EACH ROW WHEN conditionPL/SQL block;.

5. Triggers • Types of Triggers: • Category Values CommentsDML Event Insert, Type of DML whichUpdate, makes the trigger fire.DeleteTiming Before, When the trigger firesAfter, • Instead ofLevel Row, Row level triggers fire Statement for each affected row. Identified by keywordsFOR EACH ROW. Stmt. level triggers fire once per DML stmt.

6. Triggers • Trigger Firing Order: • 1. Before statement triggers fire • 2. For Each Row: • A.) Before row triggers fire • B.) Execute the Insert/Update/Delete • C.) After row triggers fire • 3. After statement triggers fire • Old and New Data • When row-triggers fire, there are 2 pseudo-records created called new and old. new table_name%ROWTYPE; old table_name%ROWTYPE; • old and new are of datatype ROWTYPE from the affected table. Use dot notation to reference columns from old and new. • old is undefined for insert statements. • new is undefined for delete statements. • Example:INSERT INTO AUDIT_TABLE (mod_by, mod_date, prev_value)VALUES (user, sysdate, :old.value);

7. Data Dictionary • PL/SQL in the Data Dictionary: • USER_SOURCE, view which contains source code of Procedures, Package Spec., Package Body, or Functions in the database. • Name name of procedure, function or package • Type PROCEDURE, PACKAGE, PACKAGE BODY, or FUNCTION • Line line of source code • Text PL/SQL source code • USER_TRIGGERS, view which contains information about triggers in the database. • Trigger_name • Trigger_type BEFORE STATEMENT, or BEFORE EACH ROW, etc. • Triggering_event Insert, Update, or Delete • Table_owner • Table_name Table trigger is defined on • Referencing_names Usually OLD and NEW • When_clause • Status Enabled or Disabled • Description • Trigger_body PL/SQL source code of trigger

8. Data Dictionary • PL/SQL in the Data Dictionary: • USER_ERRORS, view which shows errors in your last program compilation. • Name name of procedure, function or package • Type PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TYPE, or TYPE BODY • Sequence line seq. no. -- use in order by • Line line number in source code of the error • Position position of the error • Text text of the error message • Use the sqlplus command SHOW ERROR to display the line, position, and error message of the last compile. • USER_OBJECT_SIZE, shows the code size of the procedural object. • Name • Type • Source_size • Code_size • Parsed_size • Error_size • Total object size = source_size + code_size + parsed_size + error_size

9. Acknowledgements • Loney, Oracle Database 10g The Complete Reference