PL/SQL Triggers

Database Trigger

A database trigger is a block of code that is automatically executed in response to certain events. Triggers are executed implicitly whenever the triggering event happens.

The triggering event is either a INSERT, DELETE, or UPDATE command.

The timing can be either BEFORE or AFTER, INSTEAD OF trigger

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Example

Example of creating a trigger based on the following two tables:

CREATE TABLE T1 (a INTEGER);

CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.

CREATE TRIGGER tr1

AFTER INSERT ON T1

REFERENCING NEW AS newRow

FOR EACH ROW

WHEN (newRow.a <= 5)

BEGIN

INSERT INTO T2 VALUES(:newRow.a);

END tr1;

run;

Displaying Trigger Errors

If we get a message

Warning: trigger created with compilation errors.

you can check the error messages with:

show errors trigger ;

You can also type, SHO ERR (SHOW ERRORS) to see the most recent compilation error.

Viewing Defined Triggers

To view all the defined triggers, use:

select name_of_trigger from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body

from user_triggers

where trigger_name = '< name_of_trigger >';

Disabling Triggerss

To disable or enable a trigger:

alter trigger < name_of_trigger > {disable|enable};

Mutating Table Errors

Permissions

For creating triggers you should have create trigger privilege :

Example

Grant create trigger to John;

Sample table:

create table t1 (

id int,

name varchar(10),

primary key(id)

);

Before insert trigger:

Create or replace

Trigger tr1

Before

Insert

On t1

For each row

Begin

Dbms_output.put_line(’before insert of ’ || :new.name);

End;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);

Result:

before insert of sam

After insert trigger:

Create or replace

Trigger tr1

After

Insert

On t1

For each row

Begin

Dbms_output.put_line(’After insert of ’ || :new.name);

End;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);

Result:

After insert of sam

Before Update Statement Trigger:

create or replace

trigger tr1

before update

on t1

begin

dbms_output.put_line(’before updating some names(s)’);

end;

create or replace

trigger tr1

before update

on t1

for each row

begin

dbms_output.put_line(’before updating ’ ||

(:old.name) || ’ to ’ ||

to_char(:new.name));

end;

IF statements

create or replace

trigger tr1

before insert or update or delete on t1

for each row

begin

if inserting then

dbms_output.put_line(’inserting : ’ || :new.name);

elsif updating then

dbms_output.put_line(’updating : ’ ||

:old.name || ’ to ’ || :new.name);

elsif deleting then

dbms_output.put_line(’deleting : ’ || :old.name);

end if;

end;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);

Result:

inserting : sam

update t1 set name = ’g’ where name = ’a’;

updating: g to s

Working with Views

we will create a view (of t1 table):

CREATE OR REPLACE

VIEW t1_VIEW AS

SELECT NAME FROM t1;

Now, we know that updating (or inserting) into a view is kind of pointless; however, we can
provide this functionality using a trigger!

Example:

CREATE OR REPLACE

TRIGGER t1_VIEW_INSERT

INSTEAD OF INSERT ON t1_VIEW

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE(’INSERTING: ’ || :NEW.NAME);

-- we can also do

-- INSERT INTO t1(ID,NAME) VALUES (N,:NEW.NAME);

END;

When we do an insert statement on t1_VIEW:

INSERT INTO t1_VIEW(NAME) VALUES (’ poly’);

Which produces the result:

INSERTING: poly

The trigger will be fired when someone will try to insert a value into a VIEW.

PL/SQL Sitemap      PL/SQL Resources