Transcript Triggers

Triggers
What is a trigger?
• A trigger defines an action that the database
should take when some event occurs in the
application.
• It is triggered by an event.
– An insert, update or delete statement on a specific
table or view
– A create, alter or drop statement on any schema
object
– A database startup or instance shutdown
– A user logon / off
• The trigger works either before or after the
event, according to specification.
Triggers and exceptions
• Often triggers are used to check to see if an event is
within the domain constraints of the system:
– E.g. I can’t sell stock I don’t have.
– A football team cannot play itself.
– If I need to reorder stock, my reorder quantity should not be less
than my reorder level.
• Note that there are other ways to do this, other than
using a trigger, but they are not defined in the DDL (data
definition language, so are not as close to the data).
• If a trigger finds an error, it needs to inform:
– The user
– The DBMS
Trigger definition
Create or replace trigger <triggername>
Before | after
{delete|insert|update [of
column[,column]…]}
ON table-name
[referencing {old [as] <old> [new
[as] <new>]
For each row
[when (condition)]]
Pl/sql_block
definitions
• Trigger-name
– is the name of the trigger.
• Before |after
– Before indicates that the trigger should be fired before the
triggering statement.
– After indicates that the trigger should be fired after the triggering
statement.
• Delete | Insert | Update [of column]
– Delete indicates that the triggering statement deletes [a] row[s].
– Insert indicates that the triggering statement inserts a row.
– Update indicates that the triggering statement updates the
specific column(s) mentioned in the list.
• Referencing
– Specifies correlation names the can be used to refer
to the old and new values of the row components that
are being affected by the trigger.
• For each row
– If this is used, the trigger is fired for each row that the
triggering statement affects. If it is omitted, the trigger
works only once per statement.
• When
– Specifies the trigger restriction.
Example trigger
create or replace trigger insert_stock
before insert on stock for each row
declare
price_alert
exception;
out_of_stock exception;
begin
if (:new.unit_price < :new.unitcostprice) then
raise price_alert;
end if;
if (:new.stock_level is null or :new.stock_level <
:new.reorder_level) then
raise out_of_stock;
end if;
exception
when price_alert then
raise_application_error(-20002,'Below cost selling');
when out_of_stock then
raise_application_error(-20001,'Need to reorder stock');
when others then rollback work;
end;
Example run – below cost
Stock level low
Before or after?
• In the case of the previous two errors, they
are more informational than prohibiting, so
it would be more appropriate to put them
in as ‘after’ insert than before.
• Let’s amend the trigger, to make it operate
after the insert and give a warning.
– Take out exceptions
– Handle warnings within code.
‘After’ trigger
create or replace trigger after_stock
after insert on stock for each row
begin
if (:new.stock_level < :new.reorder_level) then
dbms_output.put_line(:new.stock_code||' being
reordered.');
insert into restock values (sysdate, :new.stock_code);
end if;
if (:new.unit_price < :new.unitcostprice) then
dbms_output.put_line(:new.stock_code||' selling below
cost - record added.');
end if;
exception
when others then
dbms_output.put_line('Unexpected error encountered work rolled back');
rollback work;
end;
Sample output
To show triggers
– select trigger_name from user_triggers;
• To get more info:
– Select trigger_name, trigger_type,
triggering_event, table_name,
referencing_names, trigger_body from
user_triggers where trigger_name =
'<trigger_name>';
Write a trigger for…
• Write a trigger to ensure that a stock item
cannot be sold for less than €1.