Post reply

Anything other than Trigger ?

  • November 25, 2009 at 3:16 pm

    #218045

    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

    #1084396

    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.

  • November 25, 2009 at 4:42 pm

    #1084403

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 25, 2009 at 10:21 pm

    #1084442

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 29, 2009 at 1:58 am

    #1085124

    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

    #1085969

    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

    #1085996

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 1, 2009 at 11:55 am

    #1086000

    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

    #1086001

    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

    #1086008

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 2, 2009 at 2:07 am

    #1086311

    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