Alert on data deletion

We have an employee table that contains bank details and are experiencing
problems with account numbers being erased and lost. In order to track down
why this is happening (either due to our application code or SQL
replication) we'd like to be able to prevent certain columns from being
deleted if they already contain some data.

Is it possible to setup a check constraint to prevent our ee_acct_no columns
from being set to NULL or blank strings if it contains an account number
(i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
always know employees bank details until later, so we do need to put them on
our database without bank details initially.

Also, if possible, can someone suggest a stored procedure or trigger i could
create that would fire a user-defined error message that would email an
operator if a bank account number changed?

Many thanks

Dan Williams.On Thu, 10 Mar 2005 14:35:55 +0000 (UTC), Dan Williams wrote:

> We have an employee table that contains bank details and are experiencing
> problems with account numbers being erased and lost. In order to track down
> why this is happening (either due to our application code or SQL
> replication) we'd like to be able to prevent certain columns from being
> deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no columns
> from being set to NULL or blank strings if it contains an account number
> (i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
> always know employees bank details until later, so we do need to put them on
> our database without bank details initially.

If ee_acct_no starts out null and later becomes non-null, then a check
constraint can't do the trick. You need a trigger ... which answers the
next question

> Also, if possible, can someone suggest a stored procedure or trigger i could
> create that would fire a user-defined error message that would email an
> operator if a bank account number changed?

CREATE TRIGGER trig_ee_acct
ON ee_acct
FOR UPDATE
AS
IF UPDATE(ee_acct_no)
BEGIN
declare @.msg varchar(400)
RAISERROR ('The ee_acct_no column must never be changed', 16, 1)
ROLLBACK TRANSACTION
END
GO

As Books Online describes:

All ad hoc messages have a standard message ID of 14,000.

Therefore in enterprise manager, you can set an email alert on message ID
14,000, and operators will get an email.

> Many thanks
> Dan Williams.|||Ross Presser (rpresser@.imtek.com) writes:
> As Books Online describes:
> All ad hoc messages have a standard message ID of 14,000.
> Therefore in enterprise manager, you can set an email alert on message ID
> 14,000, and operators will get an email.

Is that SQL 7 Books Online? The number for ad hoc messages is 50000.

Dan could also use sp_addmessage to add a custom message, for instance
75321 for this error, and say:

RAISERROR(75321, 16, 1)

and the set up the alert on this code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan Williams (dtwilliams@.hotmail.com) writes:
> We have an employee table that contains bank details and are
> experiencing problems with account numbers being erased and lost. In
> order to track down why this is happening (either due to our application
> code or SQL replication) we'd like to be able to prevent certain columns
> from being deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no
> columns from being set to NULL or blank strings if it contains an
> account number (i.e a 9 digit number)? We have setup the column to allow
> NULL's as we don't always know employees bank details until later, so we
> do need to put them on our database without bank details initially.

That would have to be a trigger. Ross showed you the basics, but I like
to add some more details.

First IF UPDATE() a bit heavy-handed. IF UPDATE() only tells us that
the column mentioned in the SET clause, but not that the value was
actually changed.

So, you would have to compare inserted and deleted with each other
to compare these. However, joining inserted and deleted can have
costly performance effects. My standard routine is to save inserted
and deleted into table variables and then work with these.

Note that it's a good idea to keep IF UPDATE(), so that you don't
perform the check if the UPDATE is for a completely different column
only.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 10 Mar 2005 21:46:25 +0000 (UTC), Erland Sommarskog wrote:

> Ross Presser (rpresser@.imtek.com) writes:
>> As Books Online describes:
>>
>> All ad hoc messages have a standard message ID of 14,000.
>>
>> Therefore in enterprise manager, you can set an email alert on message ID
>> 14,000, and operators will get an email.
> Is that SQL 7 Books Online? The number for ad hoc messages is 50000.
> Dan could also use sp_addmessage to add a custom message, for instance
> 75321 for this error, and say:
> RAISERROR(75321, 16, 1)
> and the set up the alert on this code.

SQL 2000 BOL. And it's self-contradictory!

msg_id

Is a user-defined error message stored in the sysmessages table. Error
numbers for user-defined error messages should be greater than 50,000. Ad
hoc messages raise an error of 50,000.

msg_str

Is an ad hoc message with formatting similar to the PRINTF format style
used in C. The error message can have up to 400 characters. If the message
contains more than 400 characters, only the first 397 will be displayed and
an ellipsis will be added to indicate that the message has been cut. All ad
hoc messages have a standard message ID of 14,000.|||That's great. Thanks a lot.

I've managed to create a trigger that makes use of custom made error
messages that get emailed to me whenever an account number gets
changed. Here is my SQL code:-

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.eecode varchar(30)
SET @.eecode = (select ee_code FROM inserted)

IF UPDATE(ee_acct_no)
DECLARE @.oldAcctNo varchar(10)
DECLARE @.newAcctNo varchar(10)

SET @.oldAcctNo = (select ee_acct_no from deleted)
SET @.newAcctNo = (select ee_acct_no from inserted)

IF LEN(@.oldAcctNo) > 0 AND @.newAcctNo = ''
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) > 0 AND LEN(@.newAcctNo) < 8
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16,
1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) = 8 AND @.newAcctNo <> @.oldAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
END

I believe my logic could probably be made more efficient, but initially
this appears to be working. However, i am experiencing issues with my
custom alert not being triggered consistently when the account numbers
are being changed. Do the insert and delete trigger tables get purged
after the trigger fires?

After updating an employees account number, i do initially receive the
email alert (with details of the old and new numbers), but if i update
it immediately after, it doesn't seem to fire the alert. However, if i
wait a couple of minutes, it appears to work ok.

That's why i'm thinking the inserted and deleted tables still contain
the previously saved information. Is there anyway i can purge the
information?

Thanks again

Dan|||dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
> CREATE TRIGGER trig_ee_acct ON employee
> FOR UPDATE
> AS
> DECLARE @.eecode varchar(30)
> SET @.eecode = (select ee_code FROM inserted)
> IF UPDATE(ee_acct_no)
> DECLARE @.oldAcctNo varchar(10)
> DECLARE @.newAcctNo varchar(10)
> SET @.oldAcctNo = (select ee_acct_no from deleted)
> SET @.newAcctNo = (select ee_acct_no from inserted)

This is not a good trigger. A trigger fires once per statement, and
the deleted/inserted tables can contain many rows, and a good trigger
should handle this. So you will need to rewrite your trigger.

> That's why i'm thinking the inserted and deleted tables still contain
> the previously saved information. Is there anyway i can purge the
> information?

deleted/inserted are so-called virtual tables and are constructed from
the transaction log, and they cannot be accessed outside the scope of
the trigger.

By the way, if you need to make many accesses to the tables in your
trigger, it's a good idea to copy the interesting columns to table
variables and work with these instead. This can give quite some
performance improvements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9616EB37A93C5Yazorman@.127.0.0.1...
> dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
>> CREATE TRIGGER trig_ee_acct ON employee
>> FOR UPDATE
>> AS
>>
>> DECLARE @.eecode varchar(30)
>> SET @.eecode = (select ee_code FROM inserted)
>>
>> IF UPDATE(ee_acct_no)
>> DECLARE @.oldAcctNo varchar(10)
>> DECLARE @.newAcctNo varchar(10)
>>
>> SET @.oldAcctNo = (select ee_acct_no from deleted)
>> SET @.newAcctNo = (select ee_acct_no from inserted)
> This is not a good trigger. A trigger fires once per statement, and
> the deleted/inserted tables can contain many rows, and a good trigger
> should handle this. So you will need to rewrite your trigger.
>> That's why i'm thinking the inserted and deleted tables still contain
>> the previously saved information. Is there anyway i can purge the
>> information?
> deleted/inserted are so-called virtual tables and are constructed from
> the transaction log, and they cannot be accessed outside the scope of
> the trigger.
> By the way, if you need to make many accesses to the tables in your
> trigger, it's a good idea to copy the interesting columns to table
> variables and work with these instead. This can give quite some
> performance improvements.

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?

Many thanks

Dan|||Dan Williams (dtwilliams@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp