1 / 9

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

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. Triggers. Trigger Overview A trigger is a procedure which is executed implicitly whenever the triggering event happens.

lparkhill
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related