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
