October 25, 2004 at 12:52 pm
Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?
They also want us to track schema changes. Is this possible?
Thanks, Dave
October 25, 2004 at 2:16 pm
October 26, 2004 at 2:20 am
I guess a triger could do the job and checking for errors after each action.
October 26, 2004 at 3:46 am
log and perfect transaction designment can help you , but I cannot say more.
I will pay attention to your question.
October 26, 2004 at 3:51 am
Trigger would not work with a failure as it would be rolled back also. Transaction Log might be a good place but wil require a third party app.
I sugest setting up a trace to send to a file those items or use Profiler to capture. Or better add logging to the front end application(s) to write either to a text file, the windows event log or some other location.
October 26, 2004 at 6:28 am
how about using an alert.
you could use the alert to add rows to a table when the raiserror generated by a failed insert or update is executed.
MVDBA
October 26, 2004 at 2:07 pm
If you perform these through Stored Procedures, then you could modify your stored procedures to perform the logging when an error condition was found. If you were concerned about ad-hoc SQL statements, then you could restrict access only to the Stored Procedures as well.
October 26, 2004 at 2:15 pm
Are u performing your insert, updates and delete through a stored procedure. If Yes then its can be done. Basically you would have to create a second shadow table for all the tables in question. then after your insert statement you would put same insert to the shadow table.
ITS little bit extra work but works fine. You would just have to tell the procedure to insert into a shadow table only when @@rowcount != 1.
and same can be done with the triggers. With the triggers it will be less code.
Thanks.
Taj
Tajammal Butt
October 26, 2004 at 2:22 pm
If you can, name your Transaction.
BEGIN TRANSACTION InsertTableName
I wasn't born stupid - I had to study.
October 26, 2004 at 4:48 pm
I do something similar, but I do it in the middle tier. We use only sprocs (no in-line sql) - using MS .Net / ADO.Net. Each sProc is invoked via a method in a single class using the same connection string (for pooling). Each method that invokes the sproc is in a Try/Catch block. Should the sproc fail, the failure is then reported / audited according to defined business rules (within the catch).
I find managing this in the middle tier much easier to maintain than TSQL based error handling within the sprocs.
October 27, 2004 at 7:51 am
Now I think of it and after trying some scripts here is my 2 cents.
when you logging a failure you will not get the record because during the record failure, actual record is already lost. Sop here is what I will do, I will create a second audit table with fields like id, failure_code, table_name and getdate.
During the procedure after you do your insert.....find IF @@rowcount != 1
INSERT into table_audit values(id, failure_code, table_name, getdate)
For Update and deletes, you will use
IF @@error != 0
INSERT into table_audit values(id, failure_code, table_name, getdate)
Reason for using @@error for updates and deletes is that you can delete or update more than one record using some conditions. Inserts are mostly one record if coming from front end.
Hope you got the idea what to do. You will also need some errortrapping in sp's...
Tajammal Butt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply