Let’s talk
about stored procedures. These are routines written in the T-SQL programming
language. They are a single batch of code. You can call these routines using an
EXEC command. Obviously, these routines can do DML. But they also can issue
DDL. They are not allowed to do a USE to switch databases. They can have
parameters which act as variables within the routines. Parameters are passed in
a comma separated list. These parameters can optionally be OUTPUT returning
values back to the caller. The routines exit when they hit a RETURN statement.
Triggers are similar to procedures. But you
never explicitly call triggers. They are implicitly executed when you do DML on
a table or view. There are two types of triggers – AFTER triggers and INSTEAD
OF triggers. The triggers fire once for a whole SQL statement. Their activity
is part of a transaction. Trigger actions can cause other triggers to fire, up
to a maximum nested depth of 32. The UPDATE() function takes a column as input
and tells whether that column was affected by the DML that caused the trigger
to fire. Similarly, the COLUMNS_UPDATED() function returns a bitmap of all
field affected.
