Oracle – PL-SQL- Introduction of Triggers

Oracle, Oracle-Triggers, Varinder Sandhu

A trigger is a procedure that is run automatically by the database when a specified SQL DML INSERT, UPDATE, or DELETE statement is run against a table or view.

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

The trigger is mostly used for keeping the integrity of the information on the database.

Trigger Event:

  1. A trigger can fire before or after the SQL statement runs.
  2. A trigger can may be run once for every row affected. Such a trigger is known as a row-level trigger.
  3. A trigger can may be run for all the rows. Such trigger is known as a statement-level trigger. Use statement-level triggers when you need to check business rules that are not row dependent.
  4. A row-level trigger has access to the old and new column values when the trigger fires as a result of an UPDATE statement on that column.

You create a trigger using the CREATE TRIGGER statement.

Syntax for the CREATE TRIGGER statement is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} trigger_event
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
BEGIN
  trigger_body
END trigger_name;

Where

  1. OR REPLACE specifies the trigger is to replace an existing trigger if present.
  2. BEFORE specifies the trigger fires before the triggering event is performed.
  3. AFTER specifies the trigger fires after the triggering event is performed.
  4. INSTEAD OF specifies the trigger fires instead of performing the triggering event.
  5. trigger_event specifies the event that causes the trigger to fire.
  6. table_name specifies the table that the trigger references.
  7. FOR EACH ROW specifies the trigger is a row-level trigger.
  8. A row-level trigger is run for each row when the trigger fires.
  9. If you omit FOR EACH ROW, the trigger is a statement-level trigger.
  10. A statement-level trigger is run once when the trigger fires regardless of the number of rows affected.
  11. trigger_condition specifies a Boolean condition that limits when a trigger actually runs its code.
  12. trigger_body contains the SQL and PL/SQL statements that perform the trigger’s task.

Related Posts

,