Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit


Audit

Author
Message
Kayal-717623
Kayal-717623
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 290
Hi all,

I have few basic questions.

1. Does trigger guaranteed to fire for each row? Even if i do bulk insert?

2. Can i pass input parameter to a trigger. For example, for audits, I need to track the userid who modified the record in the front end. So i need to pass the userid to the trigger to update the audit table. How will i do that.

3. For audits, I have the previous and after value in the xml fields. So do i need a schema for it. I just have xml fragments stored. So i guess without xml schema i can query the data right?

4. Storing the data with FOR XML RAW or FOR XML auto, Elements is easy to query data? Because we query the data quite often and i want some efficient way to store the xml fragments in xml data type.

The articles about audits in sqlserver central is very informative. The articles by GSquared about audits is amazing.

Any help or suggestion is greatly appreciated.

Thanks in advance.
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 9729
Generally, bulk insert doesn't fire triggers. But it can, if you set it to do so. For example, the Bulk Insert command has an option "With Fire_Triggers". Most of the time, I'd recommend logging what file you imported, and keeping a copy of the file, rather than do a row-by-row log of a bulk import.

You can't actually pass parameters to triggers. You can use the User_Name function in the trigger, or you can have a column in the table where you put the "updated by" user name, and include that in the trigger. If you're using a generic connection, and don't store who is using which SPID when, then putting that in the main table, and including that column in the logging trigger, will do what you need.

You can query XML data without a specific schema, or you can define a schema and use that in your logging and querying.

I've had no trouble querying the XML data created by such a log. It's just XML data, and SQL 2005 is quite good at querying it. You can even index it.

Thank you for your compliment on my articles. I appreciate that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: Administrators
Points: 41354 Visits: 18876
The trigger is guarenteed to fire for each insert, once. It does not fire for each row.

you need to use the inserted and deleted tables to access the rows that were inserted/updated/deleted.

The suser_sname() or user_name() can be used to grab the ID of the person changing the data.

The XML schema and querying are really up to you. It's not that hard to query XML in SQL Server 2005, but it does take some skills.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 9729
I read your question about firing for each row differently than Steve did.

Triggers will fire for ALL rows, but not for EACH row, if you get the difference.

If you use a command that updates 10 rows, all at once, the trigger will fire once, but the logging triggers in included in the article will log the changes to all 10 rows. ALL rows, not EACH row.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Kayal-717623
Kayal-717623
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 290
Thanks Gsquared and steve for the replies... That answers all my questions Smile . I appreciate your help.
kambanjet
kambanjet
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
Good Day,

I would like to ask for help on setting up auditing in SQL 2005 for any updates, deletions, and insertions done through the back-end or through SQL Server Manager.

I dont know if this is possible.

Thank you
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10234 Visits: 9517
kambanjet (2/8/2010)
Good Day,

I would like to ask for help on setting up auditing in SQL 2005 for any updates, deletions, and insertions done through the back-end or through SQL Server Manager.

I dont know if this is possible.

Thank you

Please start a new thread for your question.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search