July 9, 2008 at 8:56 am
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.
July 9, 2008 at 9:02 am
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
July 9, 2008 at 9:05 am
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.
July 9, 2008 at 9:08 am
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
July 9, 2008 at 10:01 am
Thanks Gsquared and steve for the replies... That answers all my questions 🙂 . I appreciate your help.
February 8, 2010 at 4:38 am
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
February 9, 2010 at 7:51 am
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply