February 27, 2008 at 11:51 pm
Hello All
I'm new to sql.I have implemented DDL trigger that tracks any data that is created,Dropped or altered.My concern is if any View or Stored Procedure is altered then how to get the data that was there before alteration so that I can compare the old and the the altered data and make out exactly what were the changes made. This is somewhat similar to DML trigger[INSTEAD Of] and [AFTER] but unfortunately DDL trigger doesn't supports this.Can anyone think of something through which I can track both values?
Any help is welcome
THANKS in advance:)
February 28, 2008 at 6:27 pm
You're getting into audit table territory here. Running a DML AFTER trigger to insert old and new data into audit tables. Which can take up a lot of space.
The question is, should the data be getting changed at all? If you're worried about it being changed when it shouldn't, maybe you need to tighten the security up first. Like trying to figure out if a burglar took any money or not instead of just locking the door in the first place.
You may also want to look at your backup regime as well. If data was erroneously changed, how would you recover from it? Could you restore from backups up to the point it happened if you wanted/had to?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 29, 2008 at 5:53 am
hiya all
i am also new to sql.............
as far as i hv understood , is that u want to fire a trigger when any changes is made on view or sp and compare the previous with new ones.........
the logic u can apply is that ......... u need to fire the trigger before the concerned query executes.... and in trigger u can use 'print' to print the previous query..
let me know if u do that!!!!!!!!
thanx
🙂
February 29, 2008 at 6:15 am
In the DDL trigger you have access to the EVENT_DATA function which returns an XML document that includes a the node (/EVENT_INSTANCE/TSQLCommand/CommandText that has the T-SQL run. You would need to have the trigger enabled at create and you need to store your audit data in a table. Then you can compare new to previous. Check out this article:
http://www.sqlservercentral.com/articles/Auditing/62126/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply