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

 

 

   
  Oracle Tips by Burleson

Administration of Triggers in Oracle

Database triggers are PL/SQL procedures stored in the database and associated with specific actions on a database level. Beginning with Oracle8, a new type of trigger called an INSTEAD OF trigger could be created exclusively for views. Under Oracle8i and Oracle, the concept of database event triggers has been added. A database event trigger triggers on DDL or database events such as STARTUP, SHUTDOWN, LOGIN, LOGOFF, or server errors. 

Tip

Never depend on the order in which triggers fire. This is not guaranteed to always be identical.

Creation of Database Triggers

Database triggers are created using the CREATE TRIGGER command, and are ENABLED upon creation. There were three basic types of triggers in Oracle7: BEFORE, AFTER, and FOR EACH ROW. Under Oracle8, a fourth was added: the INSTEAD OF trigger for views. Since the FOR EACH ROW clause can be combined with the other two non-view triggers, this gives four types of triggers: BEFORE and AFTER statement triggers and BEFORE and AFTER row triggers. In addition, each of the four types can be tied to the three basic actions, DELETE, INSERT, and UPDATE, resulting in 12 possible triggers per table.     

A view can only have an INSTEAD OF trigger (under Oracle8 only). The Oracle8i DDL and database event triggers are also created using the CREATE TRIGGER command. DDL events that can cause a trigger to fire are CREATE, ALTER, or DROP on clusters, tables, views, procedures, functions, packages, roles, sequences, synonyms, indexes, tablespaces, triggers, types, and users. The database event triggers are LOGON, LOGOFF, SERVERERROR, STARTUP, and SHUTDOWN, with the LOGON, STARTUP, and SERVERERROR coming AFTER event triggers. The LOGOFF and SHUTDOWN are BEFORE event triggers. You can find the exact syntax for the CREATE TRIGGER command in the SQL reference at the techent.oracle.com Web site.       

Database triggers are complex, so if you do not save the creation script, it will be very difficult to readily recall the exact command used in many cases. The script in TRIG_RCT.SQL, available from the Wiley Web site, can be used to retrieve trigger definitions for the database. Prior to the release of Oracle version 7.3, triggers were compiled at runtime. Since 7.3, they are stored in the database as Pcode. This provides significant performance benefits over earlier versions since the overhead of reparsing the trigger for each firing is eliminated. This allows larger and more complex triggers to be created without fear of performance degradation caused by reparsing large sections of code. In versions 8, 8i and 9i triggers can be pinned into memory using the DBMS_SHARED_POOL.KEEP(‘trigger_name’, ‘T’) procedure call.

Conditional Predicates  

New to Oracle8 and continued in Oracle8i and Oracle is the concept of a conditional predicate for a trigger that tells the trigger why it is being firedThese conditional predicates are of the form:

   INSERTING. Evaluates to TRUE if an insert operation fired the trigger.

   DELETING. Evaluates to TRUE if a delete operation fired the trigger.

   UPDATING. Evaluates to TRUE if an update operation fired the trigger.

   UPDATING(column). Evaluates to TRUE if the operation is an update and the specified column is updated.

See Code Depot


www.oracle-script.com

  
 

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.