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

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Triggers

A trigger  is a block of PL/SQL code that executes when a database event occurs.  Because the database executes the trigger, no parameters can be passed in, or returned by, a trigger.  The trigger can execute any PL/SQL code other PL/SQL block can execute, to include calling procedures and functions, DML or DDL (creating or manipulating objects or data). 

Since a trigger executes on a database event, it can capture, change, or update data being inserted, updated, or deleted to include stopping undesirable changes. Triggers can be used to replicate data to other tables, insert data into tables with foreign key constraints in the correct order, or just about anything else you can do in PL/SQL.

Triggers execute on database events.  In Oracle 8i and above, just about anything that happens in the database can have a trigger attached to it.  This includes database startup/shutdown, user logon/logoff, and server errors.  Triggers can also be attached to event against database objects such as tables.  

There are two types of triggers on a table event: a statement level trigger  and a row level trigger.  A statement level trigger executes once when the event happens.  If you are performing a bulk insert and want to log that an insert took place you would use a statement level trigger to add one log entry for the bulk insert (even though you may insert 1000 rows).  A row level trigger executes for every row acted upon during the event.  In a bulk insert, the row level trigger will execute for each row being inserted.  The row level trigger contains the FOR ALL ROWS clause .

The example below is a statement level trigger.

create trigger
   t1
on insert or update or delete on
   book
begin
  myapp.log_event;
end;

On a bulk operation this trigger will execute once, no matter how many rows are inserted, updated, or deleted.  The example below is basically the same trigger as a row level trigger.

create trigger
   t2
on insert or update or delete on
   book
for each row
begin
  myapp.log_event;
end;

This trigger  will execute once for each row inserted, updated or deleted.

The specification for a trigger differs from the procedure or function in that it must tell the database on what event the trigger is executed and if the trigger executes before the event or after it.  Some events are restricted in their execution capabilities.  For example, a trigger that executes before the database startup event is not possible, nor is it possible to execute a trigger after a database shutdown event. 

Lets look at using a trigger to create complicated primary keys for tables in the PUBS database.  In the PUBS database the primary keys are a varchar2 datatype that is based on a sequence.  For the author table, the primary key starts with a letter A followed by at least three numbers.  What we need is a trigger that creates a new key for the author table when a row is inserted.  The key is needed for each row so this will be a row level trigger.

First, we create a sequence for the trigger to use.  Note that the sequence starts at the number 20 to account for the rows that are already in the table.

 SQL> create sequence author_key_seq
  2  increment by 1
  3  start with 20
  4  cache 3 noorder
  5  / 

Sequence created.

Now create a trigger on the author table that creates the new primary key when a row is inserted.  The example below creates a row level trigger that executes before a row is inserted into the table.  It generates the primary key and replaces the rows primary key column with the new key before the row is inserted into the table.

SQL> create or replace trigger author_key_gen_tr
  2  before insert on author
  3  for each row
  4  declare
  5    n_number number;
  6  begin
  7    select author_key_seq.nextval into n_number
         from dual;
  8    :NEW.author_key := 'A'||lpad(n_number,3,0);
  9  end;
 10  / 

Trigger created.

There are a couple of new items in the example above.  Line 2 defined the event that the trigger will fire on, before every insert on the author table.  This trigger will not fire on updates or deletes.  Line 3 specifies that the trigger will fire for each row inserted.  The trigger will fire for every row, single insert or bulk insert.  Line 8 introduces a new notation.  The :OLD and :NEW notation are used to reference the row data before the trigger execution and after the trigger execution.  Note that we are referencing the values before and after the trigger execution, not the event.  In the example above, the trigger does not care what (if anything) is inserted in the author_key column.  The trigger creates a new key from the sequence and places it in the :new.author_key  variable, which is then inserted into the table instead of the column value before the trigger fired.  The SQL in the example below inserts one row of data into the author table.

SQL> insert into author values
  2  ( ''
  3  , 'Spade'
  4  , 'Sam'
  5  , '234-234-5678'
  6  , '123 Here St'
  7  , 'Thereville'
  8  , 'ST'
  9  , '98765'
 10  , '45822');

1 row created.

Here a row is inserted into the author table with a blank (or NULL) author_key column.  When the author_key is selected from the table, you can see that the trigger created a key value and placed it into the row before the row was inserted into the table.

SQL> select
  2    author_key
  3  from author
  4    where author_last_name = 'Spade';
 

AUTHOR_KEY
-----------
A020


The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

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

 

email BC:


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

Oracle is the registered trademark of Oracle Corporation.