TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS IN T-SQL

Triggers:

A Trigger is a special type of stored procedure that’s automatically invoked when the user try to modify data that it’s designed to protect. Triggers help to secure data’s integrity  by preventing unauthorized or inconsistent changes from being made.

Triggers Performance:

From the performance point-of-view, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used up by referencing other tables. The referencing can be fast if the other tables are in memory or a bit slower if they read from disk.

Rules for creating Triggers:

i)                    Triggers can’t be created on temporary tables. They can, however, reference views and temporary tables.

ii)                  Triggers can’t return result sets. Using the IF EXISTS clause as a part of a SELECT Statement in trigger code is a common practice.

iii)                Triggers should be used to maintain data integrity, maintain referential integrity, and encapsulate business rules.

iv)                Triggers can be encrypted in the syscomments table if you specify the WITH ENCRYPTION option.

v)                  WRITETEXT statement don’t activate triggers. They are used to modify text or image data, and it’s a nonlogged transaction.

vi)                The following SQL Statement can’t be used in a trigger:

Ø  All CREATE Statements

Ø  All DROP Statements

Ø  ALTER TABLE and ALTER DATABASE

Ø  TRUNCATE TABLE

Ø  RECONFIGURE

Ø  LOAD DATABASE or TRANSACTION

Ø  UPDATE  STATISTICS

Ø  SELECT INTO

Ø  DISK STATEMENT