| Author |
Topic |
|
gercr
Yak Posting Veteran
53 Posts |
 Posted - 2003-05-15 : 18:35:12
|
| Hi, I want to audit (delete,insert,update) a specific table from a dabase, is this posible?? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-15 : 18:39:32
|
| Sure, ever used SQL Profiler? But what do you mean by audit? If you want to go back in time and still have your transaction log, then [url]http://www.lumigent.com/products/le_sql/le_sql.htm[/url]. Also, have a look at [url]http://www.lumigent.com/products/entegra/entegra.htm[/url].Tara |
 |
|
|
gercr
Yak Posting Veteran
53 Posts |
Posted - 2003-05-21 : 11:22:28
|
| What I mean with audit, is if a user make a delete on a X table, I want to this delete was logged somewhere. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-21 : 13:11:04
|
| You need a table with the exact sturcture as you base table, and add 3 columns to capture three types of data history add by (the user), history add datetime (when the change occurred) and history add type (update or delete).For an update or delete action create a trigger that will insert the row from the virtual inserted or deleted table to the history table.No need to worry about inserts as they will be in the base table.Hows that sound?Brett8-) |
|
|
|
gercr
Yak Posting Veteran
53 Posts |
Posted - 2003-05-21 : 14:24:00
|
| Thanks for the help, but that sounds too much work, so what we do was use the Profiler and store the results on a DB, and at the end of the day, we will delete all the records we don't need (select, etc) Regards.Gerardo G. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-21 : 14:27:34
|
| SQL Profiler shouldn't be used as an auditting tool except to audit a specific event on a specific day. It is not designed to be used all of the time. It is going to cause performance problems especially if you decide to run the tool on the database server.Tara |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-05-21 : 14:31:25
|
| Take a look at the Entegra tool that Tara references in her first post. I saw a demo of this at PASS last fall and was fairly impressed by what it could do.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-05-21 : 14:50:28
|
quote:
SQL Profiler shouldn't be used as an auditting tool except to audit a specific event on a specific day. It is not designed to be used all of the time. It is going to cause performance problems especially if you decide to run the tool on the database server.
In order to avoid some of the overhead associated with running SQL Profiler, a server-side trace can be setup using sp_trace_* procs. Edited by - izaltsman on 05/21/2003 14:52:51 |
|
|
|
|
Audit a Table