Dan Williams (dt********@hotmail.com) writes:
OK, thanks for the advice. I've just discovered the wonders of triggers
and have previously been performing validation and integrity checks in
my application code, so i'm very much a beginner in writing SQL code.
Could you provide me with an example trigger that i can use or point me in
the direction of a good web site that i can learn from?
Writing triggers is not fundamentaly different from writing stored
procedures, although a few things apply:
1) The "inserted" and "deleted" are visible in the trigger only, not
from stored procedures or dynamic SQL called from the trigger.
2) The tables are slow to access, so if the trigger has many references
to them, copying to a table variable is recommendable.
3) You are always in the context of the transaction defined by the statement
that fired the trigger. For this reason, one should engage in long-
running operations, as this can give contention problems.
4) Any error (save RAISERROR) terminates execution, aborts the batch and
rolls back the transaction.
5) Likewise, if the transaction count on exit differs from the trancount
when the trigger started execution, this also causes the entire batch
to be rolled back.
The trigger you posted could be rewritten to something like:
CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS
DECLARE @inserted TABLE (...)
DECLARE @deleted TABLE (...)
INSERT @inserted (...)
SELECT ... FROM inserted
INSERT @deleted (...9
SELECT ... FROM deleted
IF UPDATE(ee_acct_no)
BEGIN
IF EXISTS (SELECT *
FROM @inserted i
JOIN @deleted d ON i.pk = d.pk
WHERE len(d.oldAcctNo) > 0
AND nullif(i.newAcctNo, '') IS NULL
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END
IF EXISTS (SELECT *
FROM @inserted i
WHERE len(i.oldAcctNo) <> 8)
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16, 1)
ROLLBACK TRANSACTION
END
IF EXISTS (SELECT *
FROM @inserted i
JOIN @deleted d ON i.pk = d.pk
WHERE d.oldAcctNo <> i.newAcctNo
BEGIN
RAISERROR (50001, 10, 1, @eecode, @oldAcctNo, @newAcctNo)
ROLLBACK TRANSACTION
END
END
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp