What is a Database Trigger?
Database trigger is a PL/SQL block that is
executed on an event in the database.
The event is related to a particular data manipulation of a table such
as inserting, deleting or updating a row of a table.
Triggers
may be used for any of the following:
* To implement complex business rule, which cannot be implemented
using integrity constraints.
* To audit the process. For
example, to keep track of changes made to a table.
* To automatically perform an action when another concerned action
takes place. For example, updating a
table whenever there is an insertion or a row into another table.
Triggers are similar to stored procedures, but
stored procedures are called explicitly and triggers are called implicitly by
Oracle when the concerned event occurs.
Note: Triggers are automatically executed by Oracle and
their execution is transparent to users.
Types of Triggers
Types of Triggers
Depending upon, when a trigger is fired, it may
be classified as :
* Statement-level trigger
* Row-level trigger
* Before triggers
* After triggers
Statement-level Triggers
A statement trigger is fired only for once for a
DML statement irrespective of the number of rows affected by the statement.
For example, if you execute the following UPDATE
command STUDENTS table, statement trigger for UPDATE is executed only for once.
update students
set bcode=’b3’
where bcode = ‘b2’;
However, statements triggers cannot be used to
access the data that is being inserted, updated or deleted. In other words, they do not have access to
keywords NEW and OLD, which are used to access data.
Statement-level triggers are typically used to
enforce rules that are not related to data. For example, it is possible to
implement a rule that says “no body can modify BATCHES table after 9 P.M”.
Statement-level trigger is the default type of
trigger.
Row-level Trigger
A row trigger is fired once for each row that is
affected by DML command. For example, if
an UPDATE command updates 100 rows then row-level trigger is fired 100 times
whereas a statement-level trigger is fired only for once.
Row-level trigger are used to check for the
validity of the data. They are typically used to implement rules that cannot be
implemented by integrity constraints.
Row-level triggers are implemented by using the
option FOR EACH ROW in CREATE TRIGGER statement.
Before Triggers
While defining a trigger, you can specify
whether the trigger is to be fired before the command (INSERT, DELETE, and UPDATE) is executed or
after the command is executed.
Before triggers are commonly used to check the
validity of the data before the action is performed. For instance, you can use
before trigger to prevent deletion of row if deletion should not be allowed in
the given case.
AFTER Triggers
After triggers are fired after the triggering
action is completed. For example, If after trigger is associated with INSERT
command then it is fired after the row is inserted into the table.
Possible Combinations
The following are the various possible
combinations of database triggers.
* Before Statement
* Before Row
* After Statement
* After Row
Note: Each of the above triggers can be associated with INSERT, DELETE,
and UPDATE commands resulting in a total of 12 triggers.
In the next section, we will see how to create
database triggers.
Creating a Database Trigger
CREATE TRIGGER command is used to create a
database trigger. The following details are to be given at the time of creating
a trigger.
* Name of the trigger
* Table to be associated with
* When trigger is to be fired - before or after
* Command that invokes the trigger - UPDATE, DELETE, or INSERT
* Whether row-level trigger or not
* Condition to filter rows.
* PL/SQL block that is to be executed when trigger is fired.
The following is the syntax of CREATE TRIGGER
command.
CREATE [OR REPLACE] TRIGGER
trigername
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF columns]}
[OR {DELETE | INSERT
|UPDATE [OF columns]}]...
ON table
[FOR EACH ROW [WHEN condition]]
[REFERENCING [OLD AS old]
[NEW AS new]]
PL/SQL block
If FOR EACH ROW
option is used then it becomes a row-level trigger otherwise it is a
statement-level trigger.
WHEN is used to fire the trigger
only when the given condition is satisfied.
This clause can be used only with row triggers.
For example, the following trigger is fired only
when AMOUNT is more than 1000.
create or replace trigger ..
before insert on payments
for each row
when
:new.amount > 1000
OF option allows
you to specify updation of which columns will fire trigger. The list of columns
can be given by separating column by comma.
REFERENCING is used to use new names instead of default correlation names OLD
and NEW. See the section “Correlation
Names “
The following is a simple database trigger that
is used to check whether date of joining of the student is less than or equal
to system date. Otherwise it raises an error.
create or replace trigger students_bi_row
before insert
on students
for each row
begin
if
:new.dj > sysdate then
raise_application_error
(-20002,'Date of joining cannot be after system date.');
end
if;
end;
STUDENTS_BI_ROW is the name of the trigger. It
represents table name – STUDENTS, event of the trigger – BI (before insert) and
type – ROW. Though trigger name can be anything, it is better you follow a
convention while naming it.
FOR EACH ROW specifies that this trigger is a
row-level trigger.
Condition :NEW.DJ > SYSDATE checks whether
the value of DJ of the row being inserted is greater than system date. If the
condition is true then it raises an error using RAISE_APPLICATION_ERROR
procedure.
Note: Use data dictionary view USER_TRIGGERS to list the existing
triggers.
The following command can be used to display the
name and body of each trigger in the current account.
select trigger_name,
trigger_body from user_trigger;
Column
TRIGGER_BODY is of LONG type. How many characters of this column will be
displayed is determined by variable LONG in SQL*Plus. Set variable LONG to a larger value to see
the complete body of the trigger. The default value of LONG is 80.
The following is another trigger used to
implement a rule that cannot be implemented by integrity constraints. The
trigger checks whether payment made by the student is more than what the
student is supposed to pay.
create or replace trigger payments_bi_row
before insert
on payments
for each row
declare
v_dueamt
number(5);
begin
v_dueamt := getdueamt(:new.rollno);
if :new.amount > v_dueamt then
raise_application_error(-20112,'Amount being paid is more than what is
to be paid');
end if;
end;
The above trigger makes use of GETDUEAMT
function that we created in the previous chapter. It first gets the due amount of the given
roll number. If the amount being paid – values of AMOUNT column – is more than
the due amount then trigger fails.
Statement-level trigger example
The following is an example of statement-level
trigger. As we have seen in the previous section, a statement trigger is fired
only for once for the entire statement irrespective of the number of rows
affected by the statement.
create or replace trigger payments_biud
before insert or update or delete
on payments
begin
if
to_char(sysdate,'DY') = 'SUN' then
raise_application_error(-20111,'No changes can be made on sunday.');
end if;
end;
The above trigger is fired whenever an UPDATE or
DELETE or INSERT command is executed on PAYMENTS table. Trigger checks whether
day of week of the system date is Sunday.
If condition is true then it raises an application error. Since the
trigger is not concerned with data, it uses a statement-level trigger.
Valid Statements in trigger body
The following are the only valid statements in
trigger body. Trigger can make use of existing stored procedures, functions and
packages (as shown in the previous example).
q DML commands
q SELECT INTO command
Correlation Names
The default correlation names are NEW for new
values and OLD for old values of the row. So, in order to access the values of
new row use NEW and to access the values of old (existing) row, use OLD.
It is also possible to change these correlation
names using REFERENCING option of CREATE TRIGGER command. This is done mainly
to avoid names conflicts between table names and correlation names.
See the table 1, to understand the availability
of correlation name with triggering commands.
|
Command
|
NEW
|
OLD
|
|
DELETE
|
No
|
Yes
|
|
INSERT
|
Yes
|
No
|
|
UPDATE
|
Yes
|
Yes
|
Table 1: Availability of correlation names
NEW is not available with DELETE command because there are no new
values. In the same way, OLD is
not available with INSERT because a new row is being inserted and it doesn’t
contain old values. Triggers based on UPDATE command can access both NEW and
OLD correlation names. OLD refers to values that refer to values before the
change and NEW refers to values that are after the change.
The following trigger prevents any change to
AMOUNT column of PAYMENTS table. This is done by comparing old value with new
value and if they differ that means the value is changed and trigger fails.
create or replace trigger payments_bu_row
before update
on payments
for each row
begin
if :new.amount <> :old.amount then
raise_application_error(-20113,'Amount cannot be changed. Please delete
and reinsert the row, if required');
end if;
end;
Instead-of Trigger
These trigger are defined on relation-views and
object-views. These triggers are used to
modify views that cannot be directly modified by DML commands. Unlike normal
trigger, which are fired during the execution of DML commands, these triggers
are fired instead of execution of DML commands. That means instead of executing
DML command on the view, Oracle invokes the corresponding INSTEAD-OF trigger.
The following is a view based on STUDENTS and
PAYMENTS tables.
create view
newstudent
as
select
s.rollno, name, bcode,gender, amount
from students s, payments p
where
s.rollno = p.rollno;
If you try to insert data into NEWSTUDENT table
then Oracle displays the following error.
SQL>
insert into newstudent values (15,'Joe','b2','m',2000);
insert
into newstudent values (15,'Joe','b2','m',2000)
*
ERROR
at line 1:
ORA-01779:
cannot modify a column which maps to a non key-preserved table
But, we want the data supplied to NEWSTUDENT
view to be inserted into STUDENTS and PAYMENTS table. This can be done with an instead
of trigger as follows:
create or replace trigger newstudent_it_bi_row
instead of insert
on newstudent
for each row
begin
--
insert into STUDENTS table first
insert into students(rollno,bcode,name,gender,dj)
values(:new.rollno, :new.bcode, :new.name, :new.gender,sysdate);
--
insert a row into PAYMENTS table
insert into payments
values(:new.rollno, sysdate, :new.amount);
end;
The above INSTEAD-OF trigger is used to insert
one row into STUDENTS table with roll number, name, batch code and gender of
the student. It then inserts a row into PAYMENTS table with roll number, date
of payment - sysdate, and amount.
Since we created an INSTEAD-OF trigger for
INSERT command, Oracle invokes this trigger when an INSERT command is executed
on NEWSTUDENTS view.
SQL> insert into newstudent values
(15,'Joe','b2','m',2000);
So the above command inserts two rows – one into
STUDENTS table and another into PAYMENTS table. The following two queries will
ratify that.
select rollno, bcode,name, gender, dj
from students where rollno = 15;
ROLLNO
BCODE NAME G DJ
--------- ----- ------------------------------ -
---------
15
b2 Joe m 30-Oct-01
select * from payments where rollno = 15;
ROLLNO
DP AMOUNT
--------- --------- ---------
15
30-OCT-01 2000
Knowing which command fired the trigger
When a trigger may be fired by more than one DML
command, it may be required to know which DML command actually fired the
trigger.
Use the following conditional predicates in the
body of the trigger to check which command fired the trigger.
* INSERTING
* DELETING
* UPDATING
The following example is used to log information
about the change made to table COURSES. The trigger makes use of a COURSES_LOG
table, which is created with the following structure.
create table courses_log
(
cmd number(1),
pk varchar2(20),
dc date,
un varchar2(20)
);
Now a row level trigger is used to insert a
row into COURSES_LOG table whenever there is a change to COURSES table.
create
or replace trigger courses_biud_row
before
insert or delete or update
on
courses
for
each row
begin
if
inserting then
insert into courses_log values (1,
:new.ccode,sysdate, user);
elsif
deleting then
insert into courses_log
values(2,:old.ccode,sysdate,user);
else
insert into courses_log
values(3,:old.ccode,sysdate,user);
end if;
end;
After the trigger is created, if you execute an UPDATE
command as follows:
update
courses set fee = fee * 1.1 where ccode = 'ora';
it will fire COURSES_BIUD_ROW trigger, which
will insert a row into COURSES_LOG table as follows:
SQL>
select * from courses_log;
CMD PK DC UN
---------
-------------------- --------- --------------------
3 ora 30-OCT-01 BOOK
Enabling and disabling triggers
A database trigger may be either in enabled
state or disabled state.
Disabling trigger my improve performance when a
large amount of table data is to be modified, because the trigger code is not
executed.
For example,
UPDATE
students set name = upper(name);
will run faster if all the triggers fired by UPDATE on STUDENTS
table are disabled.
Use ALTER TRIGGER command to enable or disable
triggers as follows:
alter trigger
payments_bu_row disable;
A disabled trigger is not fired until it is
enabled. Use the following command to
enable a disabled trigger.
alter trigger payments_bu_row enable;
The following command disables all triggers of
STUDENTS table.
alter
table students disable all triggers;
Dropping a Trigger
When a trigger is no longer needed it can be
dropped using DROP TRIGGER command as
follows.
drop trigger payments_bu_row;
Summary
Database triggers are used to implement complex
business rules that cannot be implemented using integrity constraints. Triggers
can also be used for logging and to take actions that should be automatically
performed.
Trigger may be fired before the DML operation or
after DML operation. Trigger may be fired for each row affected by the DML
operation or for the entire statement.
INSTEAD-OF trigger are called instead of
executing the given DML command. They are defined on relational-views and
object-views (will be discussed later in this book).
A trigger can be disabled to increase the
performance when a lot of data manipulations are to be done on the table.
