Invocation of Triggers

Contact Us or call 1-877-932-8228
Invocation of Triggers

Invocation of Triggers

Rather than being explicitly invoked or called from a SQL statement, a trigger is called when an event occurs. The three categories of events that can cause a trigger to fire are DML statements (INSERT, UPDATE, DELETE), DDL statements (e.g. CREATE or ALTER) or database events (logon, logoff, startup, shutdown, or when an error occurs). Possible trigger events include the following:

  • CREATE
  • DELETE
  • DROP
  • DROP OR TRUNCATE
  • INSERT
  • INSERT OR UPDATE
  • INSERT OR UPDATE OR DELETE
  • UPDATE
  • UPDATE OR DELETE

A number of distinctions need to be kept in mind when writing triggers. Triggers can run before or after an event occurs. The number of times a DML trigger fires can vary based upon whether the trigger is coded to run once per statement or once for every row. In situations where a view is not inherently updatable ( cannot be modified directly through DML statements) there are also INSTEAD OF triggers that provide a way of changing the data displayed by the view. These distinctions are identified in reference to triggers as the trigger timing firing points. The available trigger timing firing points (which are permutations of the distinctions previously mentioned) are as follows:

  • BEFORE EACH ROW
  • AFTER EACH ROW
  • BEFORE STATEMENT
  • AFTER STATEMENT
  • BEFORE EVENT
  • AFTER EVENT
  • INSTEAD OF

Triggers that fire in response to a DML statement might need to access data included in the statement itself or data that is part of an affected record. The values originally in the table can be referenced using :OLD and the values that are part of the current statement can be referenced using :NEW.

Next