| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2010-10-05 : 13:12:47
|
greetingsi have created an audit table for each of the tables in my database. i went the route of one audit table per table for ease of management and speed.i want to have more control of the insertion of values to these audit tables so I would prefer to use sprocs rather than triggersI have implemented the following approach and it is workingBEGIN TRANSACTION; BEGIN TRY SET NOCOUNT ON; INSERT INTO dbo.payments ( payment_id , payment_date ) VALUES ( @payment_id , GETDATE() ) SET @primary_key = SCOPE_IDENTITY() EXEC dbo.spAudit @table_name = N'payments',@Action = N'INSERT', @primary_key = @primary_key COMMIT TRANSACTION @TranName; END TRY I want to use on sproc sp_audit to do all of my auditing. This will mean I will have to use dynamic sql. What would you suggest I use triggers or sproc?ThanksIf you don't have the passion to help people, you have no passion |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-05 : 13:34:38
|
| anyways using dynamic sql is bad idea. Better to write audit procs specific to tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-05 : 13:38:31
|
| generally speaking:If you want to guarantee that any and all changes to the table get logged then a trigger (for each subject table) is the way to go. Even if your policy is to only modify the table via a stored proc there will certainly be a case where an admin gets in there and does something. That change would be lost without a trigger.a trigger is technically a stored procedure so under the sheets there is no difference. A trigger will be implicitly part of the user transaction so you don't need an explicit transaction. However, for that reason, you should make the trigger code as brief and simple as possible. The only code should be the insert to the audit table. And the audit table should have no possibility of violating a constraint.What "control" will you be losing by using a trigger rather than an SP?Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-10-05 : 14:13:50
|
| wow i go to get coffee and all these responses, thanks! The reason I wanted to do it in sproc is in order to make it generic and have control over what happens under the hood ,it's a mechanic thing since I used to be a mechanic, I want, I need, I need control :) (What About Bob) I am just a control freak that is all. What TG says is on the money if someone made changes to table behind the scenes it will not be captured. It will be a very very simple trigger. The spAudit is already getting to be hairy with concatenation and all.or I could go the visakh way and create a sproc for each table. I guess there is a trade off. Looks like I might need to consider triggers, I just do not like triggers, no particular reason really.Thank you all very much!If you don't have the passion to help people, you have no passion |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-05 : 14:31:33
|
quote:
Originally posted by yosiasz
BEGIN TRANSACTION; BEGIN TRY SET NOCOUNT ON; INSERT INTO dbo.payments ( payment_id , payment_date ) VALUES ( @payment_id , GETDATE() ) SET @primary_key = SCOPE_IDENTITY() EXEC dbo.spAudit @table_name = N'payments',@Action = N'INSERT', @primary_key = @primary_key COMMIT TRANSACTION @TranName; END TRY
this is flawed. there's no rollback if the TRY failsalso, as said b4, triggers are the way to go. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-10-05 : 15:02:35
|
| russell i did not post the rollback that is why.thanksIf you don't have the passion to help people, you have no passion |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-10-05 : 15:11:03
|
quote:
Originally posted by yosiaszThe reason I wanted to do it in sproc is in order to make it generic and have control over what happens under the hood
You are not the first person to want to write generic triggers.quote:
Originally posted by yosiaszThe reason I wanted to do it in sproc is in order to make it generic and have control over what happens under the hood
You will not be the last person to learn that these two goals "generic" and "control" are actually at odds with eachother.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-10-05 : 16:52:05
|
| i have seen the light blindman, thank you!If you don't have the passion to help people, you have no passion |
|
|
|
|
audit via trigger or sproc