Triggers versus stored procedure

[pradeepbha]

What is the difference between triggers and stored procedure? when should each be used?

[JMac]

Simple explanation:

Triggers - fire in response to an event (After a user logs On, prior to an insert being made into a table, for every record deleted etc...).

Procedures are code modules that can be invoked from any piece of code or PL/SQL. Triggers always fire - automatically - once they are defined when the event occurs and regardless of whether or not the invoking event was triggered from an application , or SQL or PL/SQL. Procedures have to be invoked from your code.

So - if you need to do something no matter what causes the event to occur (ie) audit user access to the database via SQL, an application, ODBC etc then use a trigger.

I use procedures for repetitive pieces of code (validation etc.). It makes code maintenenace much easier.