Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Audit Expand / Collapse
Author
Message
Posted Wednesday, July 09, 2008 8:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 06, 2012 10:08 AM
Points: 78, 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.



Post #530902
Posted Wednesday, July 09, 2008 9:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #530912
Posted Wednesday, July 09, 2008 9:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Post #530920
Posted Wednesday, July 09, 2008 9:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #530926
Posted Wednesday, July 09, 2008 10:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 06, 2012 10:08 AM
Points: 78, Visits: 290
Thanks Gsquared and steve for the replies... That answers all my questions :) . I appreciate your help.
Post #531011
Posted Monday, February 08, 2010 4:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:10 AM
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
Post #861550
Posted Tuesday, February 09, 2010 7:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #862440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse