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
RAISERROR ('error, please contact support', 2/*2=EXUSER*/, 1/*default state*/)
ROLLBACK TRANSACTION <---------
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.
You may want to try using the @@error after the update to check if there are any errors
This @@error has to be used after each DML say
And you can check the @error1 and @error2 to trap the error.
I dont knowif this will help you or not.