none
After calling insert trigger and when the exception comes , it does not insert the record on triggered table. RRS feed

  • Question

  • I am trying to insert after insert trigger on Sales table.  The record must be inserted on sales  even if there is error and rollback  at the time of the the insertion in Audittrial table . even though an error is coming , it should  not be affected on Sales insertion , Please can you help

    CREATE  TRIGGER  [dbo].[TRGINSERTAUDIT]
       ON  [dbo].[SALES]

    AFTER INSERT
       As
       Begin

     BEGIN TRAN

          Insert Into AUDITTRIAL
           (RECORDID,OLDVALUE,NEWVALUE)
           Values
           (@ID,@OLDVALUE,@NEWVALUE)
          End try
         BEGIN CATCH
         SET @ErrorMessage  = ERROR_MESSAGE()
        SET @ErrorSeverity = ERROR_SEVERITY()
        SET @ErrorState    = ERROR_STATE()
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
        
         ROLLBACK

      END


    polachan

    Friday, June 29, 2018 8:11 AM

Answers

  • I am asking , even if  there is CATCH Error and failed  , how can I make the record inserted     on SALES table  after the fail  of  insert Trigger at the time of insert into nsert Into AUDITTRIAL

    polachan

    then what you need is an INSTEAD OF INSERT trigger which checks the condition before and even if error happens you can catch it and do the insert to the master table.

    But in that case your audit table will be missing the details which will defeat the purpose of audit capture itself.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by polachan Tuesday, July 3, 2018 3:08 PM
    Friday, June 29, 2018 10:27 AM

All replies

  • What kind of error is happening when you try to insert to AuditTrail?

    For example if its a Foreign key violation, then it will never get inserted unless the reference data is present in the master table or the constraint is disabled so its not as easy as just catching the error in a catch block and try to insert again


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Friday, June 29, 2018 8:14 AM
  •  (@ID,@OLDVALUE,@NEWVALUE)

    You use variables, but you haven't declared them anywhere; the creating of the trigger already Fails.

    I guess you are looking for Use the inserted and deleted Tables


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 29, 2018 9:24 AM
  • I am asking , even if  there is CATCH Error and failed  , how can I make the record inserted     on SALES table  after the fail  of  insert Trigger at the time of insert into nsert Into AUDITTRIAL

    polachan

    Friday, June 29, 2018 9:54 AM
  • I am asking , even if  there is CATCH Error and failed  , how can I make the record inserted     on SALES table  after the fail  of  insert Trigger at the time of insert into nsert Into AUDITTRIAL

    polachan

    then what you need is an INSTEAD OF INSERT trigger which checks the condition before and even if error happens you can catch it and do the insert to the master table.

    But in that case your audit table will be missing the details which will defeat the purpose of audit capture itself.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by polachan Tuesday, July 3, 2018 3:08 PM
    Friday, June 29, 2018 10:27 AM
  • I am trying to insert after insert trigger on Sales table.  The record must be inserted on sales  even if there is error and rollback  at the time of the the insertion in Audittrial table . even though an error is coming , it should  not be affected on Sales insertion , Please can you help

    Then you find another solution than a trigger. A trigger executes in the context of a transaction defined by the statement that fired the trigger, and you should see the trigger as part of that statement. And the statement is mean to be atomic. That is, if the trigger has failed, the statement has failed.

    Friday, June 29, 2018 10:15 PM
  • To add to what Erland said, you probably ought to get a third-party audit package for your company and not try to do this inside the database itself. Besides the technical problems you're running into, there's the legal problem. You just made yourself completely responsible for any failed audits! Though I personally don't like going to jail, so I always recommend this approach (I been an expert witness at cases where people did what you're trying to do).

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, July 1, 2018 5:24 PM