TRIGGERS

A database trigger is a named PL/SQL block stored in a database and exe-cuted

implicitly when a triggering event occurs. An act of executing a trig-ger

is referred to as firing a trigger. A triggering event is a DML (INSERT,

UPDATE, or DELETE) statement executed against a database table. A trig-ger

can fire before or after a triggering event. For example, if you have de-fined

a trigger to fire before an INSERT statement on the Customers table,

this trigger fires each time before you insert a row in the Customers table.

The general syntax for creating a trigger is as follows (the reserved words

and phrases surrounded by brackets are optional):

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE|AFTER} triggering_event ON table_name

[FOR EACH ROW]

[WHEN condition]

DECLARE

Declaration statements

BEGIN

Executable statements

EXCEPTION

Exception-handling statements

END;

The reserved word CREATE specifies that you are creating a new trigger.

The reserved word REPLACE specifies that you are modifying an existing

trigger. REPLACE is optional. However, notice that, most of the time,

both CREATE and REPLACE are present. Consider the following situation.

You create a trigger as follows:

CREATE TRIGGER trigger_name

…

In a few days you decide to modify this trigger. If you do not include the

reserved word REPLACE in the CREATE clause of the trigger, this gener-ates

an error message when you compile the trigger. The error message

states the name of your trigger is already used by another object. Once

REPLACE is included in the CREATE clause of the trigger, there is less of a

chance for an error because, if it is a new trigger, it is created, and if it is

an old trigger, it is replaced.

The trigger_name references the name of the trigger. BEFORE or AFTER

specify when the trigger fires (before or after the triggering event). The

triggering_event references a DML statement issued against the table.

The table_name is the name of the table associated with the trigger. The

clause, FOR EACH ROW, specifies a trigger is a row trigger and fires once

for each row either inserted, updated, or deleted. You will encounter row

and statement triggers in the next lab of this chapter. A WHEN clause

specifies a condition that must evaluate to TRUE for the trigger to fire. For

example, this condition may specify a certain restriction on the column

of a table. Next, the trigger body is defined. It is important for you to real-ize

if you drop a table, the table’s database triggers are dropped as well.

However, you should be careful when using the reserved word REPLACE for

a number of reasons. First, if you happen to use REPLACE and a name of an

existing stored function, procedure, or package, it will be replaced by the

trigger. Second, when you use the reserved word REPLACE and decide to as-sociate

a different table with your trigger an error message is generated. For

example, assume you created a trigger Customer_BI on the Customers table.

Next, you decided to modify this trigger and associate it with the Bookings

table. As a result, the following error message is generated:

ERROR at line 1:

ORA-04095: trigger 'Customers_BI' already exists on an-other

table, cannot replace it

Triggers are used for different purposes. Some uses for triggers are:

• Enforcing complex business rules that cannot be defined by

using integrity constraints.

• Maintaining complex security rules.

• Automatically generating values for derived columns.

• Collecting statistical information on table accesses.

• Preventing invalid transactions.

• Providing value auditing.

The body of a trigger is a PL/SQL block. However, there are several restrictions

that you need to know to create a trigger. These restrictions are:

• A trigger may not issue a transactional control statement such

as COMMIT, SAVEPOINT, or ROLLBACK. When the trigger

fires, all operations performed become part of a transaction.

When this transaction is committed or rolled back, the operations

performed by the trigger are committed or rolled back as

well.

• Any function or a procedure called by a trigger may not issue a

transactional control statement.

• It is not permissible to declare LONG or LONG RAW variables

in the body of a trigger.

Example:

CREATE OR REPLACE TRIGGER HW5

BEFORE INSERT ON bookings

FOR EACH ROW

---------- Declarations ----------

DECLARE

v_severity Number(1);

early_date bookings.date_rent_start%TYPE;

max_debt bookings.amount_due%TYPE;

severity_1_exc EXCEPTION;

severity_2_exc EXCEPTION;

severity_3_exc EXCEPTION;

no_data_exc EXCEPTION;

BEGIN

DBMS_OUTPUT.enable;

-- v_severity := 1;

SELECT MIN(date_rent_start),

MAX(amount_due)

INTO early_date, max_debt

FROM bookings

WHERE cust_no = :new.cust_no

AND paid <> 'Y'

AND SYSDATE - date_rent_start >= 90

AND amount_due >= 200;

IF max_debt IS NULL THEN

RAISE no_data_exc;

END IF;

IF SYSDATE - early_date >= 365 THEN

RAISE severity_3_exc;

ELSE IF SYSDATE - early_date >= 180

OR max_debt >= 500 THEN

RAISE severity_2_exc;

ELSE

RAISE severity_1_exc;

END IF;

END IF;

EXCEPTION

WHEN severity_1_exc THEN

DBMS_OUTPUT.PUT_LINE('Severity 1 - This person must PAY!');

WHEN severity_2_exc THEN

DBMS_OUTPUT.PUT_LINE('Severity 2 - This person must PAY!');

WHEN severity_3_exc THEN

DBMS_OUTPUT.PUT_LINE('Severity 3 - This person must PAY!');

WHEN no_data_exc THEN

DBMS_OUTPUT.PUT_LINE('no_data_exc');

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No credit history found on his guy.');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Sorry. An error has occurred.');

END;

/

show errors

More Examples

drop table repair_header_log;

create table Repair_Header_Log

(Repair_ID number(4),

Customer_ID number(4),

Date_Deleted date,

User_Deleting varchar2(30));

create or replace trigger Repair_Header_Delete_After after

delete on Repair_Header

for each row

declare

i number;

begin

insert into Repair_Header_Log

(Repair_ID, Customer_ID, Date_Deleted, User_Deleting)

values

(:old.Repair_ID, :old.Customer_ID, SYSDATE, USER);

end;

drop table Credit_Charge_Log;

drop table Credit_Charge_Attempt_Log;

drop table Credit_Master;

create table Credit_Charge_Log

(Card_Number varchar2(16),

Amount number(9,2),

Vendor_ID varchar2(8),

Transaction_Date date);

create table Credit_Charge_Attempt_Log

(Card_Number varchar2(16),

Amount number(9,2),

Vendor_ID varchar2(8),

Transaction_Date date);

create table Credit_Master

(Card_Number varchar2(16),

Last_Name varchar2(30),

First_Name varchar2(20));

create or replace trigger Credit_Charge_Log_Ins_Before before

insert on Credit_Charge_Log

for each row

declare

total_for_past_3_days number;

begin

-- Check the credit charges for the past 3 days.

-- If they total more than $1000.00, log this entry

-- in the Credit_Charge_Attempt_Log for further handling.

select sum(amount)

into total_for_past_3_days

from Credit_Charge_Log

where

Card_Number = :new.Card_Number and

Transaction_Date >= sysdate-3;

if total_for_past_3_days > 1000.00 then

insert into Credit_Charge_Attempt_Log

(Card_Number, Amount, Vendor_ID, Transaction_Date)

values

(:new.Card_Number, :new.Amount, :new.Vendor_ID, :new.Transaction_Date);

end if;

end;

/

---------------------------------------------------------------------------------------------------

drop table Block_Trade_Log;

create table Block_Trade_Log

(Stock_Symbol varchar2(6),

Price number,

Blocks_Traded number,

Bought_Sold char(1),

Running_Avg_3_Days number,

Timestamp date);

create or replace trigger Block_Trade_Log_Ins_Before before

insert on Block_Trade_Log

for each row

declare

Running_Avg number := 0;

begin

select avg(price)

into Running_Avg

from Block_Trade_Log

where

Stock_Symbol = :new.Stock_Symbol and

Timestamp >= SYSDATE-3;

:new.Running_Avg_3_Days := Running_Avg;

end;

/

create or replace trigger Block_Trade_Log_Ins_After after

insert on Block_Trade_Log

for each row

declare

Too_Many_Blocks exception;

begin

if :new.Blocks_Traded > 1000000 then

raise Too_Many_Blocks;

end if;

exception

when Too_Many_Blocks then

raise_application_error (-20001, 'You cannot trade more than one million blocks');

end;

-----------------------------------------------------------------------------------------------

drop table Shipment;

create table Shipment

(Shipment_ID varchar2(12),

Product_Code varchar2(12),

Quantity number,

Customer_ID varchar2(12),

Manual_Check char(1) check (Manual_Check in ('Y','N')),

Entered_By varchar2(30),

Entered_On date);

create or replace trigger Shipment_Ins_Before before

insert on Shipment

for each row

when (to_number(to_char(sysdate,'HH24')) > 17)

declare

begin

:new.Manual_Check := 'Y';

end;

/

rem Example of Cascading Triggers

drop table tab1;

drop table tab2;

drop table tab3;

create table tab1

(col1 number);

create table tab2

(col2 number);

create table tab3

(col3 number);

create or replace trigger tab1_Update_Before before

update on tab1

for each row

declare

begin

insert into tab2

(col2)

values

(:old.col1);

end;

/

create or replace trigger tab2_Insert_Before before

insert on tab2

for each row

declare

begin

update tab3

set

col3 = :new.col2;

end;

/

create or replace trigger tab3_Update_After after

update on tab3

declare

begin

insert into tab3

(col3)

values

(27);

end;

/