• Trigger:
  • A trigger is a group or set of SQL and PL/SQL statements that are executed by Oracle itself.
  • Advantages:
  1. To prevent misuse of database. For example, to prevent transaction on Sunday.
  2. To implement business rule constraints, such as balance should not be negative value.
  3. To enforce complex security authorization, such as enforcing different passwords for different operations.
  4. To track the operations begin performed on specific tables with details like operation, time when it is performed, user name who performed it, etc.
  5. To implement automatic back-up of the database.
  • Types of triggers:
  • Row v/s Statement Trigger:
  1. Row trigger:
  • Fired each time a row is affected by the triggering statement.
  • For example, if UPDATE operations update five rows in a table, a trigger will be executed five times.
  • If no rows are affected by the triggering statement, the trigger will not be executed.

     2.Statement trigger:

  • Fired only once.
  • For example, if an UPDATE operation updates five rows in a table, a trigger will be executed only once.
  • Trigger will be executed once, even if no rows are affected by the triggering statement.
  • Before v/s after trigger:
  1. Before trigger:
  • Trigger is executed before the triggering statement.
  • Used to determine whether a triggering statement should be allowed to execute or not.

      2.After trigger:

  • Trigger is executed before the triggering statement.
  • Used to determine whether a triggering statement to complete execution before trigger.