Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unhappy Unanswered: UPDATE trigger not working properly

    We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:

    UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

    Code = 800a0c93
    Source = ADODB.Recordset
    Description = Operation is not allowed in this context.

    What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.

    trigger code:

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




    ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
    ON viewViaNetCardholder
    INSTEAD OF UPDATE AS
    UPDATE tblIDCross
    SET
    tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
    tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
    tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
    tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
    tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
    tblIDCross.intCustomerType = ins.intCustomerType
    FROM INSERTED ins
    WHERE tblIDCross.intUSCardId = ins.intUSCardId




    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    Please help...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Do you happen to have any computed columns in your table def?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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