Audit

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Audit

Audit

Rate Topic

Display Mode

Topic Options

Author

Message

Kayal-717623

Posted Wednesday, July 09, 2008 8:56 AM

SSC 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

GSquared

Posted Wednesday, July 09, 2008 9:02 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Steve Jones - SSC Editor

Posted Wednesday, July 09, 2008 9:05 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

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

GSquared

Posted Wednesday, July 09, 2008 9:08 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Kayal-717623

Posted Wednesday, July 09, 2008 10:01 AM

SSC 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

kambanjet

Posted Monday, February 08, 2010 4:38 AM

Forum 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

RBarryYoung

Posted Tuesday, February 09, 2010 7:51 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

kambanjet (2/8/2010)

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 »

Permissions