Triggers:
Triggers are PLSQL block of code that will be executed automatically upon an event.They are mainly used for Auditing purposes,to prevent a user from performing certain activities,security purposes and so on.
There are several types of Triggers in Oracle.Lets see each one of them.
DDL Trigger :
We can write a trigger such that the trigger will be executed before/after a DDL statement (Drop,Create,Alter etc).Such triggers are called as a DDL trigger.
DML Trigger :
When we write a trigger that is executed when a DML operation like insert/update/delete happens we can call it as a DML trigger.
Event Trigger :
When a trigger is fired upon a system event like login /logoff of system,database etc they are called as event triggers.
Instead of Triggers :
We can cause a trigger to be fired instead of performing an activity which is called as instead of triggers .
Compound Triggers :
It is a new concept which is released in Oracle 11g,They allow multiple triggers to be created at the same point of time.
Components of a trigger :
The triggers have the below components :
1.Trigger Name
2.Triggering event (update/insert etc)
3.Triggering time (Before/After)
4.Triggering level(Statement/row level).
Syntax of a Trigger :
A Trigger can be written as :
create or replace trigger trigger_name
(before/after/instead of) (update/insert/delete) on table_name
begin
<code>
end;
Lets see each one of the trigger now .
DDL Trigger :
A DDL trigger is fired when a DDL change happens to the table associated with the trigger
eg :
create or replace trigger abc_test
before drop on table employees
begin
raise_application_error('-1000','the table employees cannot be dropped');
end ;
Triggers are PLSQL block of code that will be executed automatically upon an event.They are mainly used for Auditing purposes,to prevent a user from performing certain activities,security purposes and so on.
There are several types of Triggers in Oracle.Lets see each one of them.
DDL Trigger :
We can write a trigger such that the trigger will be executed before/after a DDL statement (Drop,Create,Alter etc).Such triggers are called as a DDL trigger.
DML Trigger :
When we write a trigger that is executed when a DML operation like insert/update/delete happens we can call it as a DML trigger.
Event Trigger :
When a trigger is fired upon a system event like login /logoff of system,database etc they are called as event triggers.
Instead of Triggers :
We can cause a trigger to be fired instead of performing an activity which is called as instead of triggers .
Compound Triggers :
It is a new concept which is released in Oracle 11g,They allow multiple triggers to be created at the same point of time.
Components of a trigger :
The triggers have the below components :
1.Trigger Name
2.Triggering event (update/insert etc)
3.Triggering time (Before/After)
4.Triggering level(Statement/row level).
Syntax of a Trigger :
A Trigger can be written as :
create or replace trigger trigger_name
(before/after/instead of) (update/insert/delete) on table_name
begin
<code>
end;
Lets see each one of the trigger now .
DDL Trigger :
A DDL trigger is fired when a DDL change happens to the table associated with the trigger
eg :
create or replace trigger abc_test
before drop on table employees
begin
raise_application_error('-1000','the table employees cannot be dropped');
end ;
In the above example the trigger will be executed whenever any user tries to drop the table employees.
the trigger raises an exception saying that the employee table cannot be dropped.Along with this we can also insert a record into a table which has the user and the time stamp so that we can find out if anyone has tried to delete the employee table and at what time.This is really useful for security purposes.Since the trigger is called before a DDL statement on the table employees this can be called as a DDL trigger.
DML Trigger:
create or replace trigger emp_update
after update on employees
begin
dbms_output.put_line('The employee table is updated at '|| sysdate);
end;
Above is an example for a DML trigger .Whenever a user tries to update the employee table .Once the update is completed the trigger will be fired since we have created it as after update on employees .If we create it as before update on employees ,it will be fired before updating the table.The output will be printed as some user has updated the employee table at this time .This type of triggers are also called as after trigger since the trigger is fired after the update in the table.
Event Trigger :
If you are working as a database administrator and you have a very important files in a system .and whenever someone logs in we should be able to track which user has logged into the system at what time .In this type if scenario we can go for an event trigger.
Row level triggers:
Row level triggers will be executed once for each row that is effected in a DML statement .For Eg if there is a trigger after update on employees and if the update happens for 10 rows then the trigger will be fired 10 times .It is represented as for each row clause in a trigger .Eg :
create or replace trigger trig_name
after update on employees
for each row
when :new.salary < :old.salary
begin
insert into audit_table values (:new.salary,:old.salary,:new.emp_id);
end ;
In the above trigger for each row clause indicates that it is a row level trigger and the when condition is used to restrict the trigger execution.In this case the trigger will be executed only when the new salary that is updated is less than the old salary.
The :new and :old are called as correlated identifies and it can be used only with a row level trigger
Statement level trigger :
A statement level trigger will be fired only once for a statement though it may cause multiple rows to be effected .By default the trigger will be a statement level trigger .
We cannot use the qualifiers like old and new in the statement level triggers .But they can be used when we need a single operation to be done after a DML command .
Difference Between Row level and Statement Level triggers :
The row level triggers will be fired once for every row effected whereas the statement level triggers will be fired only once for an operation.
The row level triggers can have qualifies such as new and old where as a statement level triggers cannot have qualifiers .
Commit in Triggers:
We cannot issue a commit/rollback inside a trigger body.This is because Triggers are part of larger transactions and commit/rollback might cause a change in the main data .
For eg :we are issuing a commit inside an before update trigger which inserts a record into the audit table.Once we issue the update command ,the trigger will run successfully and insert the record in Audit table.But what if the original update statement fails ?
But If there are situations where we should go for commit in a trigger.We should use
PRAGMA AUTONOMOUS TRANSACTION command .it means that the trigger will execute as an autonomous transaction and will commit /rollback.But this is generally not recommended.
Drawbacks of Trigger :
Since the triggers are executed automatically creating unnecessary triggers will cause huge costs.
We should never a write a trigger that does an operations which cannot be rollbacked.
For eg I have an after insert trigger which sends a mail to the admin that a particular record is inserted.In that case we issue an insert and the mail is sent to the admin.What if we rollback the insert.So the record no longer remains in the table,But mails would be sent that the record is inserted.So using any UTL packages which cannot be rollbacked should not be written in a trigger body.
Composite Triggers :Oracle 11g :
Composite triggers are a new concept which is introduced in Oracle 11g.
They allow multiple triggers to be executed at the same point of time
Row level triggers:
Row level triggers will be executed once for each row that is effected in a DML statement .For Eg if there is a trigger after update on employees and if the update happens for 10 rows then the trigger will be fired 10 times .It is represented as for each row clause in a trigger .Eg :
create or replace trigger trig_name
after update on employees
for each row
when :new.salary < :old.salary
begin
insert into audit_table values (:new.salary,:old.salary,:new.emp_id);
end ;
In the above trigger for each row clause indicates that it is a row level trigger and the when condition is used to restrict the trigger execution.In this case the trigger will be executed only when the new salary that is updated is less than the old salary.
The :new and :old are called as correlated identifies and it can be used only with a row level trigger
Statement level trigger :
A statement level trigger will be fired only once for a statement though it may cause multiple rows to be effected .By default the trigger will be a statement level trigger .
We cannot use the qualifiers like old and new in the statement level triggers .But they can be used when we need a single operation to be done after a DML command .
Difference Between Row level and Statement Level triggers :
The row level triggers will be fired once for every row effected whereas the statement level triggers will be fired only once for an operation.
The row level triggers can have qualifies such as new and old where as a statement level triggers cannot have qualifiers .
Commit in Triggers:
We cannot issue a commit/rollback inside a trigger body.This is because Triggers are part of larger transactions and commit/rollback might cause a change in the main data .
For eg :we are issuing a commit inside an before update trigger which inserts a record into the audit table.Once we issue the update command ,the trigger will run successfully and insert the record in Audit table.But what if the original update statement fails ?
But If there are situations where we should go for commit in a trigger.We should use
PRAGMA AUTONOMOUS TRANSACTION command .it means that the trigger will execute as an autonomous transaction and will commit /rollback.But this is generally not recommended.
Drawbacks of Trigger :
Since the triggers are executed automatically creating unnecessary triggers will cause huge costs.
We should never a write a trigger that does an operations which cannot be rollbacked.
For eg I have an after insert trigger which sends a mail to the admin that a particular record is inserted.In that case we issue an insert and the mail is sent to the admin.What if we rollback the insert.So the record no longer remains in the table,But mails would be sent that the record is inserted.So using any UTL packages which cannot be rollbacked should not be written in a trigger body.
Composite Triggers :Oracle 11g :
Composite triggers are a new concept which is introduced in Oracle 11g.
They allow multiple triggers to be executed at the same point of time
