PL/SQL Triggers

 

 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

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 Triggers

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.