Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: Rollback in a trigger

    Hello,

    I would like to create a trigger (instead of insert) on MS-SQL Server table in a way that if something happens (say x=1) I would like to rollback the insert.

    In the trigger code I have

    ...
    IF (x=1)
    BEGIN
    RAISERROR ('error, please contact support', 2/*2=EXUSER*/, 1/*default state*/)
    ROLLBACK TRANSACTION <---------
    END
    ...

    My question is:
    I can use ROLLBACK or ROLLBACK TRANSACTION
    In the docs I have read that the ROLLBACK rollbacks to the far most old begin transaction but when I test it it rollbacks the same using ROLLBACK TRANSACTION.

    I have tested it using:
    begin transaction t1
    update tx set c1 = c1 + 1
    INSERT INTO test_table values(.....)
    commit transaction t1

    And both the update and the insert are rolled back on both cases.


    - Any idea why or what am I missing ?



    Regards,

    Tal Olier (otal@mercury.co.il)

  2. #2
    Join Date
    Aug 2002
    Posts
    11
    You may want to try using the @@error after the update to check if there are any errors
    IF @@error<>0
    Rollback transaction
    This @@error has to be used after each DML say
    INSERT .....
    SET @error1=@@error

    UPDATE ...
    SET @error2=@@error

    And you can check the @error1 and @error2 to trap the error.
    I dont knowif this will help you or not.

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    found some answers...

    Found some answers...

    1
    ===
    It rolls back to the outermost transaction.
    Triggers always effectively run inside a transaction.

    rollback, rollback tran and rollback transaction are the same thing.

    If you want to rollback part of a transaction (not recommended without a lot of thought) then decare a save point and rollback to that

    Note that a rollback does not terminate processing.


    2
    ===
    ROLLBACK will rollback the entire transaction.

    ROLLBACK TRAN
    can have the optional savepoint name so a transaction can
    be part rolled back. Using a savepoint name without TRAN
    does not work.

Posting Permissions

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