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 Stored Objects
Oracle Tips by Burleson
 

The release of PL/SQL 2.0 and Oracle7 revolutionized the Oracle world. Previously, all code for enforcing business rules was stored at the application level, often leading to multiple occurrences of the same code in different applications. This was a nightmare when a business rule changed, since it was extremely difficult to track which applications enforced which rules.

Oracle7 allowed developers to store code at the database level, introducing stored procedures, functions, and packages, as well as database triggers. These objects must exist in only one place (inside the database). Not only did this reduce maintenance costs and duration, it also speeded application development time because code only needed to be written once. In many IS departments that use Oracle7, most or all enforcement of business rules is now handled through triggers and stored procedures.

Database Triggers

Database triggers are objects that are closely associated with tables. There are four distinct times at which database triggers fire, twice at the statement level and twice at the row level, as shown in Table 2.5.

Table 2.5 When database triggers fire

Trigger Type

Fires

before

The statement level trigger fires before a statement is executed on the trigger’s associated table.

after

The statement level trigger fires after a statement is executed on the trigger’s associated table.

before row

The row level trigger fires once for each row affected by a statement executed on the trigger’s associated table. The trigger fires prior to the execution of the statement and can alter the contents of the data.

after row

The row level trigger fires once for each row affected by a statement executed on the trigger’s associated table. The trigger fires following the execution of the statement.

Database triggers can fire on INSERT, UPDATE, and delete events. Thus, there are 12 possible triggers that can be written for a table. The types of database triggers execute in a specific order, as illustrated in Figure 2.11.

Database triggers are covered in more detail in Chapters 7.

     

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