Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Trigger Occasionally fails

    I created a trigger to update another table when a record is inserted. This trigger appears to work about 99% of the time, but occasionally it will fail. Here is the code for the trigger, does anyone see anyting wrong with this?

    CREATE TRIGGER trgUpdateClaimCapture ON ResponseCapture
    FOR INSERT
    AS
    BEGIN
    UPDATE ClaimCapture
    SET ResponseTime = GETDATE()
    FROM Inserted I, ClaimCapture C
    WHERE C.Site = I.Site
    AND C.Batch = I.Batch
    AND C.RxNumber = I.RxNumber
    AND C.ServiceDate = I.ServiceDate
    AND C.ResponseTime IS NULL
    END

    Any help is appreciated....thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the error?

    Dup Key?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by Brett Kaiser
    What's the error?

    Dup Key?
    It never does return an error, the trigger will just fail to update the table, even though the corresponding table has data the meets the triggers specifications.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well it's not that trigger isn't firing...I would guess somethings wrong with your key structure, or the null part of the predicate...

    I'd suggest something like:

    Code:
    CREATE TABLE Trigger_Log (TriggerName sysname, TriggerFiredt datetime, RowsAffected1 int, RowsAffected2 int, ReturnCode int)
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgUpdateClaimCapture]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
    drop trigger [dbo].[trgUpdateClaimCapture]
    GO
    
    CREATE TRIGGER trgUpdateClaimCapture ON ResponseCapture
    FOR INSERT
    AS 
    BEGIN
    	DECLARE @x int, @y int, @z int
    	
    	UPDATE ClaimCapture
    	   SET ResponseTime = GETDATE()
    	  FROM Inserted I, ClaimCapture C
    	 WHERE C.Site = I.Site
    	   AND C.Batch = I.Batch
    	   AND C.RxNumber = I.RxNumber
    	   AND C.ServiceDate = I.ServiceDate
    	   AND C.ResponseTime IS NULL
    
    	SELECT @x = @@ROWCOUNT, @y = @@ERROR
    
    	SELECT @z = COUNT(*) FROM inserted
    
    	INSERT INTO TriggerLog(TriggerName, TriggerFiredt, RowsAffected1, RowsAffected2, ReturnCode)
    	SELECT 'trgUpdateClaimCapture',GetDate(),@z,@x,@y
    
    END
    GO
    That is if you're allowed to add audits...

    I might even put the keys in the log....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Re: Trigger Occasionally fails

    As Brett mentioned if no error is returned maybe it is something simple. A problem I'm having with a recent update to one of our programs is that now it is entering an empty string into certain fields instead of a [null] value some of the time. Is it possible that in the 'non-firing' records that something is setting C.ResponseTime to an empty string?

    Check with
    select * from ClaimCapture where ResponseTime = '' -- Two single quotes.




    Originally posted by Jaredke
    I created a trigger to update another table when a record is inserted. This trigger appears to work about 99% of the time, but occasionally it will fail. Here is the code for the trigger, does anyone see anyting wrong with this?

    CREATE TRIGGER trgUpdateClaimCapture ON ResponseCapture
    FOR INSERT
    AS
    BEGIN
    UPDATE ClaimCapture
    SET ResponseTime = GETDATE()
    FROM Inserted I, ClaimCapture C
    WHERE C.Site = I.Site
    AND C.Batch = I.Batch
    AND C.RxNumber = I.RxNumber
    AND C.ServiceDate = I.ServiceDate
    AND C.ResponseTime IS NULL
    END

    Any help is appreciated....thanks.

  6. #6
    Join Date
    Feb 2004
    Posts
    8
    Sounds loke something good to try. Thanks for the info...

  7. #7
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by Jaredke
    Sounds loke something good to try. Thanks for the info...
    I checked for an empty string in the field, but isn't it impossible for a datetime column to have an empty string value?

  8. #8
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Honestly I hadn't thought about datetetime fields in particular, this just struck me as similar to results I'm getting caused by having both empty strings and [null] values in the same column. However I think that all of mine are some variation on text fields, still finding more as this update just hit this week.

    Originally posted by Jaredke
    I checked for an empty string in the field, but isn't it impossible for a datetime column to have an empty string value?

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Just to as a#$l as I usually am, make sure you rewrite your UPDATE statement so that there is no opportunity for discrepancy coming from this end:

    UPDATE C
    SET ResponseTime = GETDATE()
    FROM Inserted I, ClaimCapture C
    ...

    Since FROM clause is present, and you use an alias for ClaimCapture (C), UPDATE should address the alias (C) not the object name.

    As per "failure to update" you need to look for discrepancies between what you "think" the data looks like in the table, and what it actually is.

    In the situation like your there is no room for mysteries.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And to get even more philosophical...always use ANSI Joins

    inserted i INNER JOIN ClaimCapture c
    ON...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by Brett Kaiser
    And to get even more philosophical...always use ANSI Joins

    inserted i INNER JOIN ClaimCapture c
    ON...
    Yeah, I guess my way was a little old school. Do you think there is any performance increase in the way the query would be joined .... ANSI vs. my old school way? Also, is there any benefit of using the alias in the UPDATE clause vs. the way I spelled out the table name, besides readability?

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No performance advantage, I think, but I've run into issues when my old 6.5 code started choking after applying SP6a, when I was UPDATEing temp tables (UPDATE #tmp FROM #tmp inner join ...)

    I don't know if similar issues can be exposed in 7.0/2K, because once learning the lesson, I switched to the right way of doing it.

    I also noticed, that whenever triggers are used, it's indicative of an application written around direct queries against the datasource (SELECT,INSERT,UPDATE,DELETE) rather than wrapping all data access into stored procedures/functions. Only in rare cases do you need to use a trigger, usually to support a weird business rule.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •