Triggers

Trigger is a database object ie used to execute an action basing on an even. Triggers are event-based programs, which are executed when an event occurs or raises or fires

Types of Triggers

Trigger Type is defined by the type of triggering transaction and by the level at which the trigger is executed

Triggers Are Of Two Types

1. Row Level Triggers

2. Statement Level Triggers

Row Level Triggers

A row trigger is fired each time a row in 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 the triggering statement affects no rows, the trigger is not executed at all. Row triggers should be used when some processing is required whenever a triggering statement affects a single row in a table.

Row level triggers are created using the "For Each Row" Clause in the Create Trigger Command

Statement triggers

A statement trigger is fried once on behalf of the triggering statement, independent of the number of rows the triggering statement affects (even if not rows are affected) statement triggers should be used when a triggering statement affects rows in a table but the processing required is completely independent of the number of rows affected

Statement level triggers are the default type of trigger created via Create Trigger Command

Syntax:

create or replace trigger <trigger_name>

{before/after/instead of}

{insert/update/delete}

[ of <column name> on <table name> ]

[ for each row [when <condition>] ]

Declare

variables declarations

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

begin

Executable statements

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

Exception

Exception statements

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

end;

/

Syntax Explanation :

or replace : Recreates the trigger if it already exists. this option can be used to change the definition of an existing trigger without requiring the user to drop the trigger first

Trigger Name : is the name of the trigger to be created

Before : Indicates that oracle fires the trigger before executing the trigger statement

After : Indicates that oracle fires the trigger After executing the trigger statement

Insert : Indicates that oracle fires the trigger whenever an INSERT statement adds a row to a table.

Delete : Indicates that oracle fires the trigger whenever a DELETE statement removes a row from the table.

Update : Indicates that oracle fires the trigger whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. if the OF clause is omitted, the oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table.

for Each Row : Designates the trigger to be a row trigger. the oracle engine fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the When clause. if this clause is omitted the trigger is a statement trigger.

When : specifies the trigger restriction. the trigger restriction contains a SQL condition that must be satisfied for the oracle to fire the trigger.

Basing on the above 2 types of triggers, they are further classified into 3 types

1. DML Triggers

2. DDL Triggers and

3. Instead of Triggers

1. DML Triggers

These triggers are executed before or after. we apply any dml operations on a table.

When we create a table. the trigger definition is stored in the database, which is identified with the trigger name. the code in the trigger is processed when we apply any command on the database or table

Examples :

Steps for Creating a Trigger

1. First Create a trigger, next set the server on with the following statement (set Serveroutput on)

2. Run that Trigger with the following statement

@ <trigger name>

3. perform some action (ie either insert or update or delete etc)

Statement Level Triggers

1. Create A Trigger, Which Displays A Message When Ever You Insert A New Row In To Sample1 Table

Create or replace trigger instrig1 before insert on sample1

Begin

dbms_output.put_line('one record inserted successfully.....');

End;

/

2. Create A Trigger, Which Displays A Message When Ever You Update An Existing Row In The Table Sample1

Create or replace trigger updtrig1 before update on sample1

Begin

dbms_output.put_line('one record updated successfully.....');

End;

/

1. Create A Trigger, Which Displays A Message When Ever You Delete A Row From The Table Sample1

Create or replace trigger deltrig1 before delete on sample1

Begin

dbms_output.put_line('record(s) deleted successfully.....');

End;

/

Row Level Triggers

1. Create A Trigger, Which Displays A Message When Ever You Insert A New Row Into A Table Sample1

Create or replace trigger instrig2 before insert on sample1

for each row

Begin

dbms_output.put_line(:new.sno||' record inserted successfully.....');

End;

/

1. Create a trigger, which displays a message when ever you update a row in the table sample1

Create or replace trigger updtrig2 before update on sample1 for each row

Begin

dbms_output.put_line(:old.sno||' record updated to '||:new.sno);

End;

/

1. Create A Trigger, Which Displays A Message When Ever You Delete A Row From The Table Sample1

Create or replace trigger deltrig2 after delete on sample1 for each row

Begin

dbms_output.put_line(:old.sno||' record deleted successfully.....');

End;

/

DDL TRIGGERS

1. Create A Trigger, Which Displays An Error Message When Ever You Create a New Table which starts with Letter ‘A’

Create or replace trigger ctrig1 before create on scott.schema

Begin

if dictionary_obj_name like 'a%' then

raise_application_error(-20001,'object name can not start with a');

End if;

End;

/

1. Create A Trigger, Which Displays An Error Message When Ever You try to drop any Table

Create or replace trigger prevent_drop after drop on scott.schema

Begin

if ora_dict_obj_type='table' then

raise_application_error(-20001,'object can not be dropped');

End if;

End;

/

1. Create A Trigger, Which Displays An Error Message When Ever You try to Alter any Table

create or replace trigger prevent_alter before alter on scott.schema

begin

if ora_dict_obj_type='TABLE' then

Raise_Application_Error(-20001,'Object Can not be altered');

end if;

end;

/

INSTEAD OF TRIGGER

Create a trigger, which inserts the given values in to the relevant table through a Composite view

Create or replace trigger instrig instead of

Insert on empdept for each row

Begin

if :new.deptno is not null and :new.dname is not null then

insert into department(deptno,dname) values (:new.deptno,:new.dname);

end if;

if :new.empno is not null and :new.ename is not null then

insert into employee(empno,ename) values (:new.empno,:new.ename);

end if;

End;

/