Data-protection triggers with custom logging

[Gila47ac]

I need a set of SQL Server 2000 triggers (for multiple tables) that will - dependent on the data values - prevent UPDATEs/DELETEs to the row(s) "in perpetuity". (OK, there are some exceptions, but I can handle those.)

I have chosen to use the default type of trigger rather than an INSTEAD OF trigger, because it seems the latter will require much more logic & maintenance.

By using ROLLBACK TRANSACTION and RAISERROR, I have the basic trigger that prevents the UPDATE/DELETE. What I would like to have the trigger do in addition is to log the attempted UPDATE/DELETE to a table (first choice) or a file (second choice).

INSERTing a log record to a table has no effect, even if placed after the ROLLBACK TRANSACTION. Does anyone know of a simple workaround for this?

Alternatively, I might write to a file using xp_cmdshell, but the powers-that-be might object. Do any other easy alternatives come to mind?

 

[NoCoolHandle]

Ok... 1. Instead of triggers are really a tool for indexed views and allow you to "REPLACE" the action you are writing the trigger for... THerefore you chose right.. It is not necessary..

To get at the data that is about to be updated or deleted you can access if from inside the trigger (there is a special pair of tabeles that are only available for triggers called the "inserted" and "deleted"

The row(s) as it(they) would have been before an update exists in the "Deleted" table

THe row(s) as they/it will appear if the update finishes is in the INserted Table.

for a delete ther is no "Inserted" table
and for the insert there is no "Deleted" table.

Therefore if you create a table with the same stucture (and maybe a couple of new cols added for auditing) you could simply from the trigger do a

insert into TABLEX_history
select * , suser_sname(),getdate(),host_name() from inserted

the tablex_history would have 3 new cols added for "LoggedINUser" , "modification Date","HOstPC"

This would then allow you to track back to who / where they were sitting and when the mod was to have taken place.

HTH


Rob

 

[Gila47ac]

Yes, I am using "inserted" and "deleted" (in fact I need values from both pseudo-tables for my logic).

But I have the apparently conflicting desires to: 1) rollback the changes, to protect the data; 2) raise an error, to notify the client software of the condition; and 3) log the fact that the client's inappropriate request occurred. Of these, #3 is the least important, but it would be nice.

The INSERT INTO that you describe is approximately what I want (my first choice is to log to a table, as I noted in the earlier post), but when the trigger with that code fires, no record is inserted into that table; this is presumably due to the automatic rollback of the implicit transaction for the combined UPDATE & trigger, caused by my RAISERROR (to implement #2). Is that part clear?

Thanks for your consideration.

 

[NoCoolHandle]

Yeh - sorry not thinking all the way through..
The rollback will cause the history row to be rolled back also.

Maybe you do need an instead of trigger.

this would allow you to examine what the update is supposed to be and based on business logic you could either then allow the udpate or not allow it. Either way you will be able ot insert into your history as therewill be no rollback.


Rob

 

[Gila47ac]

NCH, per your suggestion I tried an INSTEAD OF trigger. But I get the same results as with the AFTER trigger - the RAISERROR causes an automatic rollback of the implicit transaction, so no log entry is written to the table.

I could still write the log record to a file, as I posted earlier ... or I see maybe I could send an email message, using xp_sendmail. But using a table entry is still my first choice.