November 25, 2009 at 3:16 pm
I have a small situation, we are hosting a third party application which uses SQL 2005 as a back end. I want to monitor one table for INSERT, UPDATE and DELETE but if I use trigger, their application fails and throw an error. They do not use stored procedure or functions that I can re-write. I cannot even modify their table structure to add time stamp. They mostly use dynamic SQL and it is not easy for us to ask them to change to something else.
All I want to achieve is, when a row is modified or inserted, I want to collect the row information and write it to different database table. Is there any way I can achieve this without using TRIGGER? I look into Service Broker, it did not help or I lost my focus.
We use SQL 2005 Enterprise on Windows 2003 R2.
I appreciate all of your suggestions on this regard.
November 25, 2009 at 3:54 pm
What is your trigger doing? A trigger that inserts data into another table would not affect the application.
You can run traces and collect that data when a particular table is modified.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 25, 2009 at 4:42 pm
I agree with Steve; a well written trigger would have no problem copying data to another table, so if you can show us your trigger code you tried, as well as the full CREATE TABLE of the table in question, we can show you a trigger examle that would not crash.
Lowell
November 25, 2009 at 10:21 pm
Even the best written triggers can cause an application to crash because of just one thing... the returned rows counts generated in the triggers are interpreted as "error messages". The fix is to make SET NOCOUNT ON one of the very first statements in the trigger.
--Jeff Moden
November 29, 2009 at 1:58 am
A problem can arise if the 3rd-party code uses @@IDENTITY rather than SCOPE_IDENTITY() immediately after any INSERTs, and where, within your trigger, you insert rows into another table with an IDENTITY column.
Another problem can be if the value of @@ROWCOUNT differs between trigger entry and exit, e.g. if the INSERT statement inserts 100 rows into a table but you then code the trigger to insert a single row into another table, then the value of @@ROWCOUNT after the trigger has completed will be 1 rather than 100. If the dynamic SQL explicitly checks the value of @@ROWCOUNT then the value may not be the value expected.
I'd be inclined to run SQL Profiler to view the application's dynamic SQL - this will then help you determine why the trigger is causing the app to fall over.
Chris
December 1, 2009 at 10:43 am
Hi Guys, Thank you for your help. I have resolved this issue by use "SET NOCOUNT ON", it was my first step to try to make it work and it does. Thank you all. I try to post this from yesterday somehow I got an error while reply. - Sihaab.
December 1, 2009 at 11:48 am
You should still post the code.... you might be surprised what else folks can help on especially if it turns out to be a RBAR trigger. 🙂
--Jeff Moden
December 1, 2009 at 11:55 am
Here is the code... (I try to post it yesterday, no luck)
CREATE TRIGGER dbo.MyTrigger ON dbo.MyTable
FOR INSERT, UPDATE AS
BEGIN
Set Nocount ON
--AUDIT NEW RECORD.
INSERT INTO TrackingDB.dbo.TrackingTable (
Col1, Col2, Col3, Col4
)
SELECT
ins.col1,
ins.col2,
ins.col3,
ins.col4
FROM Inserted ins
WHERE ins.col4 IN ('P','A1','A2','B')
END
December 1, 2009 at 11:59 am
I recommend changing the trigger to fire on Update or Delete, and to use the deleted table (instead of inserted). That will save performance on inserts. It gives you what the data was changed from and you can check what it was changed to by looking at the actual record. Saves space in your audit log, performs better.
Otherwise, it looks pretty standard.
- 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
December 1, 2009 at 12:07 pm
I agree with GSquared. The newly "INSERTed" row is in the original table. All you're doing with capturing INSERTs is effectively doubling the data. With some rare exceptions, I'll usually only capture changes (updates to existing rows) and full up DELETE's as he suggested.
Other than that, the trigger looks fine in that it ISN'T a RBAR trigger like what a lot of people end up writing.
--Jeff Moden
December 2, 2009 at 2:07 am
Jeff Moden (12/1/2009)
I agree with GSquared. The newly "INSERTed" row is in the original table. All you're doing with capturing INSERTs is effectively doubling the data. With some rare exceptions, I'll usually only capture changes (updates to existing rows) and full up DELETE's as he suggested.Other than that, the trigger looks fine in that it ISN'T a RBAR trigger like what a lot of people end up writing.
Love that logic.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply