Interesting Problem

Question

Post reply

Interesting Problem

Karin Hollands

Grasshopper

Points: 12
More actions
January 2, 2007 at 5:34 pm

#69374

Hi All,
I'm after some advice on which direction to head with a particular problem I'm working on at the moment. I've implemented an audit trigger to track updates on various tables (trigger code based on this article http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk). This all seems to running fine however the next logical step is to be able to retrieve the audited information and reconstruct or rollback a particular record. To make it all more interesting the trigger is a generic trigger and can be applied to any table thus the restore also needs to be able to return any table.
I have a CRL Stored Procedure that reconstructs the record and returns it. However it would be far nicer to be able to change this into a Table-Valued Function so that I can join on the linking tables. Yet I don't seem to be able to create the Table Valued Function without specifying the columns, which immediately negates the whole idea of making a generic trigger and restore.
Is there a better way of doing this that I haven't been able to find?
Or can anyone suggest a different way of returning the records produced by my CLR function that will allow it to be generic and more T-SQL friendly than a stored procedure?
Cheers,
Karin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5362 More actions · Answer 1

Interesting article but it's not quite true that it needs a clr - I did something similar for pre v2005

http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html

As to your question - you can get the table structure from system tables (as in the trigger above) and use dynamic sql to contstruct a row - I would probably do it in a temp table.

Although I think what you are looking for is an SP that gets a copy of the current row in the table (just a select into a temp table) then run a series of updates of columns as it runs back through the audit table by update date until it reaches the required datetmie at which point it will have the data.

Depends a bit on what you want to do though - if you just want to reverse the changes it is just a matter of applying the values from the audit trail table in order.

If you use a temp table then most of the processing will need to be done in dynamic sql. I would think about creating a dummy temp table, adding columns to it using alter statements - this will then give you the structure which you can use in following dynamic sql statements to save having everything in a single batch.

Cursors never.
DTS - only when needed and never to control.

Karin Hollands Grasshopper Points: 12 More actions · Answer 2

Thanks for that. I didn't realise you could use dynamic sql that way. As a programmer it was drummed into me that dynamic sql was baaaaaad so I've avoided looking at it as a solution.

Cheers

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Good, keep it that way. Now you also know that there are a few exceptions to that rule (very few).