Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Trigger Structure
Oracle Tips by Burleson
 

The basic structure of a database trigger consists of the following several distinct components:

  • Trigger declaration—Defines the name of the trigger.

  • Triggering event—Defines the types of DML statements that cause a trigger to fire.

  • Associated table—Defines the table with which the trigger is associated.

  • Trigger level—Defines whether the trigger fires at the row or statement level.

  • WHEN clause—Specifies a boolean condition that is evaluated before the trigger is executed.

  • Trigger body—Specifies to execute a standard block of PL/SQL when the trigger fires.

Each of these components is explained and illustrated in the following text.

Trigger Declaration

The trigger declaration is the portion of the trigger that defines the name of the trigger. The trigger declaration is highlighted in Listing 7.11.

Listing 7.11 A trigger declaration.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number
 
DECLARE
   xLOCATION_CONFLICT   EXCEPTION;
 
BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
 
   RAISE xLOCATION_CONFLICT;
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/

Triggering Event

The triggering event of a trigger specifies which DML statements ( DELETE, INSERT, and/or UPDATE statements) will cause the trigger to be executed. The triggering event is highlighted in Listing 7.12.

Listing 7.12 A triggering event.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number
 
DECLARE
   xLOCATION_CONFLICT   EXCEPTION;
 
BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
 
   RAISE xLOCATION_CONFLICT;
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/

Associated Table

Each database trigger, whether statement-level or row-level, is associated with a table. This table is often called the trigger’s associated table or base table. A database trigger is fired when a DELETE, INSERT, and/or UPDATE statement modifies data contained in the trigger’s associated table. Listing 7.13 illustrates how a trigger’s associated table is defined.

Listing 7.13 Defining a trigger’s associated table.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number
 
DECLARE
   xLOCATION_CONFLICT   EXCEPTION;
 
BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
   RAISE xLOCATION_CONFLICT;
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/

Trigger Level

Each database trigger is defined at the statement-level or the row-level. A statement-level trigger fires once for each statement that causes the trigger to fire. If 10 rows are updated because of a single UPDATE statement, the trigger fires once. A row-level trigger fires once for each row of data modified by any given DML statement. If an UPDATE statement modifies 10 rows, the row-level trigger will fire 10 times. The definition of a trigger’s level is highlighted in Listing 7.14.

Listing 7.14 Declaring a trigger’s level.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number
 
DECLARE
   xLOCATION_CONFLICT   EXCEPTION;
 
BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
 
   RAISE xLOCATION_CONFLICT;
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/
 

               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter