3 July 2012

Triggers in Sql

trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server


Types of Triggers

This section describes the different types of triggers:

  • Row Triggers and Statement Triggers
  • BEFORE and AFTER Triggers
  • INSTEAD OF Triggers
  • Triggers on System Events and User Events

Row trigger fires once for each row affected. It uses FOR EACH ROW clause. They are useful if trigger action depends on number of rows affected.

Statement Trigger
 fires once, irrespective of number of rows affected in the table. Statement triggers are useful when triggers action does not depend on

BEFORE triggers
 The trigger action here is run before the trigger statement.

AFTER triggers
 The trigger action here is run after the trigger statement.

INSTEAD of Triggers provide a way of modifying views that can not be modified directly using DML statements.

LOGON triggers fires after successful logon by the user and LOGOFF trigger fires at the start of user logoff