Difference b/w Triggers & Constraints

Difference b/w Triggers & ConstraintsConstraints are used explicitly for data integrity. A check constraint is
used to make sure that the data entered in a column conforms to a specific
set of rules; a default constraint ensures that a column will be populated
with a defined value if none is given when the row is inserted; a primary
key constraint (like a unique constraint) ensures that the data in the
affected column(s) only appears once in the table; and a foreign key
constraint ensures that a parent row exists for the value in the local
column.
Triggers *can* be used for some of these things, but they have many other
uses as well. Auditing is the first thing that comes to mind. It is also
possible to implement multi-row constraints, such as ensuring that at least
three rows exist that match a certain set of rules, or that at most 5 rows
meet some criteria (this would be difficult to implement as a check
constraint, but could also be implemented by the stored procedure(s)
handling the inserts/updates if you can rely on them being the only direct
interface to the base table). If you have a non-updateable view, you can
use an INSTEAD OF trigger on the view to support update/insert/delete on the
base tables, without your programmers having to learn the more complicated
schema that lies beneath the view itself.
This stuff comes at a cost, of course; in general, triggers are more
expensive resource-wise than constraints, so I would implement triggers
sparingly and only when necessary (e.g. when constraints are not allowed or
when they do not meet your needs).
It also seems to be a very popular request to send e-mail, use xp_cmdshell,
launch a DTS package, write to the file system, or write to the event log in
a trigger. Resist the temptation to have a trigger wait for any external
processing!
A
"Rock" <Rock@.discussions.microsoft.com> wrote in message
news:E8D0CAF4-937F-42C4-A016-92D387B5D236@.microsoft.com...
>sql