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

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

DDL Triggers

DDL Triggers are triggers which associated with DDL (Data Definition Language) such as Dropping a table, Altering a column  etc. DDL triggers execute every time a DDL statement is executed. Generally DBA’s create DDL triggers for auditing and enforcement purposes.

General Syntax

create or replace trigger
DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN

— code here —

END;
/

Example :-

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on DATABASE
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Note :- ddl_table is a user defined table which is in DBA schema.

Within the database any DDL issued, details will be saved into ddl_table table.

similarly DDL trigger can be created within schema also.

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on SCHEMA
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

DDL Triggers for LOGON/LOGOFF

LOGON

create or replace trigger
ddl_trigger3
after LOGON on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

LOGOFF

create or replace trigger
ddl_trigger3
after LOGOFF on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/DDL Triggers are triggers which associated with DDL (Data Definition Language) such as Dropping a table, Altering a column  etc. DDL triggers execute every time a DDL statement is executed. Generally DBA’s create DDL triggers for auditing and enforcement purposes.

General Syntax

create or replace trigger
DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN

— code here —

END;
/

Example :-

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on DATABASE
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Note :- ddl_table is a user defined table which is in DBA schema.

Within the database any DDL issued, details will be saved into ddl_table table.

similarly DDL trigger can be created within schema also.

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on SCHEMA
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

DDL Triggers for LOGON/LOGOFF

LOGON

create or replace trigger
ddl_trigger3
after LOGON on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

LOGOFF

create or replace trigger
ddl_trigger3
after LOGOFF on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/