Parts of A Trigger

TRIGGER
Database trigger is a PL/SQL block that fire or execute, in response to a specific

event in the database. Database triggers can be used in a variety of ways for the
information management of your database. For example, they can be used to
automate data generation, audit data modifications, enforce complex integrity
constraints, and customize complex security authorizations.
Parts of a Trigger
A trigger has three basic parts:
1. A triggering event or statement:
A triggering event or statement is the SQL statement, database event or user event
that causes a trigger to be fired.
2. A trigger restriction:
A trigger restriction specifies a Boolean [logical] expression that must be TRUE
for the trigger to fire.
3. A trigger action:
A trigger action is the PL SQL block that contains the sql statements and code to
be executed when a triggering statement is issued and the trigger restriction
evaluates to TRUE.
Types of triggers
Depending upon, when trigger is fired, it may be classified as :
1) Statement Level trigger
2) Row level trigger
3) Before trigger
4) After trigger
5) Instead of Trigger
1. BEFORE TRIGGER
BEFORE trigger indicates that Oracle fires the trigger before executing the triggering
statement.BEFORE triggers may not modify tables, but they can be used to verify input
column values, and also modify column values that are inserted or updated in a table.
2. AFTER TRIGGER
AFTER trigger indicates that Oracle fires the trigger after executing the triggering
statement.
3. STATEMENT TRIGGER
A statement trigger is fired once on behalf of the triggering statement, regardless of the
number of rows in the table that the triggering statement affects, ( even if no rows are
affected). For example, if a DELETE statement deletes several rows from a table, a
statement-level DELETE trigger is fired only once.Statement triggers are useful if the
code in the trigger action does not depend on the data provided by the triggering
statement or the rows affected. For example, use a statement trigger to:
 Make a complex security check on the current time or user
 Generate a single audit record
4. ROW TRIGGER
A row trigger is fired each time the table is affected by the triggering statement. For
example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired
once for each row affected by the UPDATE statement. If a triggering statement affects no
rows, a row trigger is not executed at all.Row triggers are useful if the code in the
trigger action depends on data provided by the triggering statement or rows that are
affected.
5. INSTEAD OF TRIGGER
INSTEAD OF triggers provide a transparent way of modifying views that cannot be

modified directly through DML statements ( INSERT, UPDATE, and DELETE). These
triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle
fires the trigger instead of executing the triggering statement.The trigger performs
UPDATE,DELETE or INSERT operations directly on the underlying tables.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the
INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF
triggers are activated for each row of the view that gets modified.
Trigger Type Combinations
Using the options listed previously, you can create four types of row and statement
triggers:
 BEFORE statement trigger
Before executing the triggering statement, the trigger action is run.

 BEFORE row trigger
Before modifying each row affected by the triggering statement and before
checking appropriate integrity constraints, the trigger action is run, if the trigger
restriction was not violated.
 AFTER row trigger
After modifying each row affected by the triggering statement and possibly
applying appropriate integrity constraints, the trigger action is run for the
current row provided the trigger restriction was not violated. Unlike BEFORE
row triggers, AFTER row triggers lock rows.
 AFTER statement trigger
After executing the triggering statement and applying any deferred integrity
constraints, the trigger action is run.
Syntax for trigger:

CREATE OR REPLACE TRIGGER <trigger_name> <BEFORE/AFTER>
<INSERT/UPDATE/DELETE> [OF column_name] ON <table_name>[FOR
EACH ROW][WHEN <condition>]
PL/SQL block
 The optional OR REPLACE is used to change the definition of an existing trigger
without first dropping it.
 Trigger name is the name of the trigger to be created.
 BEFORE trigger indicates that Oracle fires the trigger before executing the
triggering statement.
 AFTER trigger indicates that Oracle fires the trigger after executing the triggering
statement.
 The INSERT /DELETE /UPDATE indicates the triggering event.
 FOR EACH ROW designates the trigger to be a row trigger.ie. the trigger is fired
once for each row that is affected by the triggering event. If FOR EACH ROW is
omitted, the trigger is a statement trigger – the trigger is fired only once.
 WHEN specifies the trigger restriction. It contains an SQL condition that must be
satisfied for the trigger to be fired.
 PL/SQL block is the block of code to be executed when the trigger fires.
Triggers on System Events and User Events
You can use triggers to publish information about database events to subscribers.
Applications can subscribe to database events just as they subscribe to messages from
other applications. These database events can include:
 System events
o Database startup and shutdown
o Server error message events
 User events
o User logon and logoff
o DDL statements (CREATE, ALTER, and DROP)
o DML statements (INSERT, DELETE, and UPDATE)
Triggers supplement the standard capabilities of Oracle to provide a highly customized

database management system. For example, a trigger can restrict DML operations
against a table to those issued during regular business hours. You can also use triggers
to:
 Automatically generate derived column values
 Prevent invalid transactions
 Enforce complex security authorizations

 Enforce referential integrity across nodes in a distributed database
 Enforce complex business rules
 Provide transparent event logging
 Provide auditing
 Maintain synchronous table replicates
 Gather statistics on table access
 Modify table data when DML statements are issued against views
 Publish information about database events, user events, and SQL statements to
subscribing applications
Eg : A PL/SQL trigger that doesn’t permit any operation on EMPLOYEE table

before 10am and after 5pm
Table: EMPLOYEE
Empid Primary key
ename Varchar
salary Number
deptno number
create or replace trigger night before insert on emp

declare
hc varchar (3);
hn number (5);
begin
hc:=to_char(sysdate,'hh24');
hn:=to_number(hc);
if(hn<10 or hn>17) then
raise_application_error(-20101,'Not ready');
else
dbms_output.put_line('Possible');
end if;
end;

Parts of A Trigger

Uploaded by

Copyright:

Available Formats

Parts of A Trigger

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Parts of A Trigger

Uploaded by

Copyright:

Available Formats

TRIGGER

Database trigger is a PL/SQL block that fire or execute, in response to a specific

 Generate a single audit record

INSTEAD OF triggers provide a transparent way of modifying views that cannot be

Trigger Type Combinations

 BEFORE statement trigger

Before executing the triggering statement, the trigger action is run.

 AFTER row trigger

 AFTER statement trigger

Syntax for trigger:

Triggers on System Events and User Events

o Database startup and shutdown

o Server error message events

o User logon and logoff

o DDL statements (CREATE, ALTER, and DROP)

o DML statements (INSERT, DELETE, and UPDATE)

Triggers supplement the standard capabilities of Oracle to provide a highly customized

 Automatically generate derived column values

 Prevent invalid transactions

 Enforce complex security authorizations

 Enforce complex business rules

 Provide transparent event logging

 Maintain synchronous table replicates

 Gather statistics on table access

Eg : A PL/SQL trigger that doesn’t permit any operation on EMPLOYEE table

create or replace trigger night before insert on emp

You might also like