Objectives: triggers vs. stored procedure/Function
  • Salient Notes:
    • Unlike Procedure, the Trigger can't take any parameter.
    • Procedures and Functions can return any-value , whereas trigger can only return a defined recordset.
    • A stored procedure is called explicitly, where as triggers event based and executed automatically.
    • A trigger is associated with a Table of a database, where as a procedure may not have any association with the database. ( When we do some internal calculation or assessment, it may run without involving any database)
    • It is not possible to track or debug triggers.
What is Trigger: Triggers are PL/SQL or PL/PGSQL constructs usually created and associated with a table.
The most frequent uses of the triggers are with some events associated with an UPDATE, INSERT or a DELETE functions. These triggers are supposed to implicitly fired depending upon the instructions passed to them.
  • Row Level :With each row option, a trigger is engaged at the individual row of a table.
  • Statement :  Unless "IF FOR EACH ROW" is used Trigger is fired when any row of a table is affected, with a SQL statement.
  • Before and After triggers: As the name goes, BEFORE  triggers will role before any alteration takes place in a row; whereas AFTER triggers will role after committing row/rows alterations.
  • Parameters : Trigger can's accept any parameter , stored function or procedure can.
  • Returns : Trigger can't return any value, OUT parameter in procedure and return attribute in a function can return a value to the caller.
  • Events: A trigger work likes an event, and is executed before or after a process or function.
  • Transaction: A trigger can be used tightly with a transaction, procedures and functions can only be used indirectly.
  • Calls : A procedure or function is invoked manually, where as a trigger is always associated widely with any changes in the table.
Both Oracle and PostgreSQL support triggers, in oracle trigger can be written in a PL/SQL Block
Oracle Trigger Example;

Now Update the table emp_copy1

update emp_copy1 set sal = sal*1.1 /* ( no semicolon)*/

Original Table:

Updated Table: