martes, 24 de junio de 2008

What is trigger?

What is trigger?
Trigger is a kind of stored procedure that is executed implicitly when an UPDATE, INSERT, or DELETE statement is can be fired or executed, either before or after the triggering event.
A trigger has three parts:

  • A triggering event or statement
  • A trigger restriction
  • A trigger action

A triggering event can be a SQL statement, a database event, or a user event. It includes:

  • DML statements like INSERT, UPDATE, and DELETE on a table or view.
  • DDL statements like CREATE, ALTER, and DROP on a schema object.
  • Database operations like LOGON, LOGOFF, SERVERERROR, STARTUP, and SHUTDOWN.

A trigger restriction specifies a logical expression. A trigger fires only when this logical expression is TRUE.
A trigger action is a procedure that executes when the triggering event is issued and the trigger restriction evaluates Note: Important points regarding triggers:

  • The STARTUP trigger can only be an AFTER event trigger.
  • The SHUTDOWN trigger can only be a BEFORE event trigger.
  • The LOGON trigger can only be an AFTER event trigger.
  • The LOGOFF trigger can only be a BEFORE event trigger.
  • The SERVERERROR trigger can only be an AFTER event trigger.
What is the syntax for creating a trigger?
The syntax for creating a trigger is:
CREATE [OR REPLACE} TRIGGER trig_name
{BEFORE | AFTER | INSTEAD OF} event
ON {table_or_view_name | DATABASE]
[FOR EACH ROW [WHEN condition ]]
trig_body;
where, trig_name is the name of the trigger, event is the triggering event, table_or_view_name is the name of the trigger is to be fired, condition is the trigger restriction, and trig_body specifies the trigger body.