Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2004-10-25 : 14:46:27
|
| 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 |
|
|
MuadDBA
628 Posts |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2004-10-25 : 16:49:17
|
| I would say your best bet is to catch the error in the application because you get all the info, you can test the @@error variable in TSQL immediately after a INSERT / DELETE / UPDATE statement, but you just get a number, you can't catch the dynamic part (the offending column name data type etc.) if there is a way to do that in TSQL stored procs I'd love to know cause I havn't figured it out yet. As for schema changes I have no experience of trying to track that so will be of little help.;-]... Quack Waddle |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-10-25 : 16:56:42
|
| crazyjoe,I don't believe triggers can capture failed Insert, Update and Delete attempts, only success. The DDL link you referenced is for the next version of SQL Server.cas_o,Unfortunately this won't work at the application level. That would require too many changes to the code and would also not audit statements executed through ISQL or Query Analyzer.Thanks anyway. I'm guessing Profiler is the only option.Dave |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-25 : 22:21:35
|
| It's not easy within sql server as any statement can abort the batch and you have no chance of trapping that.The only way would be to put an instead of trigger on all the tables and log an attempted update and it's success to a text file or something that isn't rolled back. A failure would be highlighted by the lack of success.Think the only realistic method would be profiler but this will miss things if you have a lot of throughput so may not be good.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-26 : 00:58:51
|
| "Auditors want us to track when Insert, Update and Delete failures occur"Dunno if this is any help ... we do all inserts through SProcs (from an auditors point of view you'd probably need to trap ad-hoc SQL too, but ...)We have a "LogMySP" Sproc that records the SProc being called, and its parameters, and MyError=999 (the 999 indicates that the SProc started and didn't finish)At the end of the SProc we update the Log row with the actaul error - 0=No error, n=Some error defined within the SProc.We then report, daily, on any non-zero MyError values."They also want us to track schema changes"http://www.nigelrivett.net/DMOScriptAllDatabases.htmlKristen |
|
|
|
|
|
|
|
|
Audit failed Insert, Update, Delete Statements?