 |
|
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.
Let’s 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 row’s 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
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
