Transcript PL/SQL Triggers - Department of Computer and Information Science

Department of Computer and Information Science,
School of Science, IUPUI
PL/SQL Triggers
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
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.
Dale Roberts
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.
Dale Roberts
Triggers
• Trigger Syntax:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE | AFTER
DELETE | INSERT | UPDATE OF column
ON table_name
FOR EACH ROW
WHEN condition
PL/SQL block;.
Dale Roberts
Triggers
•
Types of Triggers:
Category
Values
DML Event Insert,
Update,
Delete
Comments
Type of DML which
makes the trigger fire.
Timing
Before, When the trigger fires
After,
Instead of
Level
Row,
Row level triggers fire
Statement for each affected row.
Identified by keywords
FOR EACH ROW.
Stmt. level triggers
fire once per DML stmt.
Dale Roberts
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);
Dale Roberts
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
Dale Roberts
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
Dale Roberts
Acknowledgements
Loney, Oracle Database 10g The Complete Reference
Dale Roberts