Triggers in Oracle

Oracle Trigger executes based on an event, say after a table update or before a login etc.
Oracle Database automatically executes a trigger when certain conditions occur.

Oracle triggers classified into

1. DML Triggers
2. DDL Triggers
3. Instead of Trigger

In this post we will examine DML triggers

DML Triggers

DML triggers are which associated with DML operations, say insert into a table, update a view, delete from a table etc
Mainly DML triggers are 2 types, row level and statement level.
Row level triggers are fired whenever a row changes and statement level fires whenever any statement ( for example a batch update ) executes. Both row level and statement level can be defined with 3 different DMLs, insert,delete and update

In all-together

DML Triggers

  • Row Level
  1. Before Insert  and After Insert 
  2. Before Update and After Update
  3. Before Delete and After Delete
  • Statement Level
  1. Before Insert  and After Insert
  2. Before Update and After Update
  3. Before Delete and After Delete

So you can create total 2*3*2 = 12 types of DML triggers in Oracle

Statement level DML trigger will fire once per batch, Say I have Statement-lelvel before update trigger and I am updating the whole table using just one update command, the trigger will fire only once.

Row-level trigger will always fire for each row.

“:new” and “:old”
“:new” and “:old” keywords can be used in row-level triggers only. “:old” is used to reference old column value and :new is used to reference new column. Only in Update triggers you can use “:new” and “:old” in same context. See below table for more information.

old and new reference
old and new reference

CREATE TRIGGER

CREATE TRIGGER [SCHEMA].TRIGGER_NAME
BEFORE INSERT
ON [SCHEMA].TABLE_NAME FOR EACH ROW
DECLARE
BEGIN
“:new”.colum_name1  := ‘some_value’;
“:new”.column_name2 := ‘some_value’;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_NAME;

Here [SCHEMA] is optional. In above a row-level trigger after insert on a TABLE_NAME will be created.
Below another example which combines two different type of trigger together.

CREATE or REPLACE TRIGGER [SCHEMA].TRIGGER_NAME
BEFORE INSERT
ON [SCHEMA].TABLE_NAME FOR EACH ROW
DECLARE
BEGIN
if inserting then
if “:new”.recid is null then
— do something —
end if;
elsif updating then
— do something —
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_NAME;
/

CREATE or REPLACE command creates the trigger if it doesn’t exists and replaces if it exists. Note that it replaces the trigger for the base table only. You cannot replace trigger name which is associated with another table.

DROP TRIGGER

DROP TRIGGER TRIGGER_NAME;

The above command will drop the trigger permanently.

ALTER TRIGGER

ALTER TRIGGER command can be used to rename, compile, disable and enable the trigger;

ALTER TRIGGER trg_dept compile; — recompiles the trigger

ALTER TRIGGER trg_dept disable; — disables the trigger

ALTER TRIGGER trg_dept enable; — disables the trigger

ALTER TRIGGER trg_dept rename to trg_emp; — rename the trigger

Next :- DDL Triggers

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s