You can roll back triggers using either the rollback trigger statement or the rollback transaction statement (if the trigger is fired as part of a transaction). However, rollback trigger rolls back only the effect of the trigger and the statement that caused the trigger to fire; rollback transaction rolls back the entire transaction. For example:
begin tran
insert into publishers (pub_id) values ("9999")
insert into publishers (pub_id) values ("9998")
commit tran
If the second insert statement causes a trigger on publishers to issue a rollback trigger, only that insert is affected; the first insert is not rolled back. If that trigger issues a rollback transaction instead, both insert statements are rolled back as part of the transaction.
The syntax for rollback trigger is:
rollback trigger [with raiserror_statement]
The syntax for rollback transaction is described in Chapter 23, “Transactions: Maintaining Data Consistency and Recovery.”
raiserror_statement prints a user-defined error message and sets a system flag to record that an error condition has occurred. This provides the ability to raise an error to the client when the rollback trigger is executed, so that the transaction state in the error reflects the rollback. For example:
rollback trigger with raiserror 25002 "title_id does not exist in titles table."
For more information about raiserror, see Chapter 15, “Using Batches and Control-of-Flow Language.”
The following example of an insert trigger performs a similar task to the trigger forinsertrig1 described in “Insert trigger example”. However, this trigger uses a rollback trigger instead of a rollback transaction to raise an error when it rolls back the insertion but not the transaction.
create trigger forinsertrig2 on salesdetail for insert as if (select count(*) from titles, inserted where titles.title_id = inserted.title_id) != @@rowcount rollback trigger with raiserror 25003 "Trigger rollback: salesdetail row not added because a title_id does not exist in titles."
When the rollback trigger is executed, Adaptive Server aborts the currently executing command and halts execution of the rest of the trigger. If the trigger that issues the rollback trigger is nested within other triggers, Adaptive Server rolls back all the work done in these triggers up to and including the update that caused the first trigger to fire.
When triggers that include rollback transaction statements fired from a batch, they abort the entire batch. In the following example, if the insert statement fires a trigger that includes a rollback transaction (such as forinsertrig1), the delete statement is not executed, since the batch is aborted:
insert salesdetail values ("7777", "JR123",
"PS9999", 75, 40)
delete salesdetail where stor_id = "7067"
If triggers that include rollback transaction statements are fired from within a user-defined transaction, the rollback transaction rolls back the entire batch. In the following example, if the insert statement fires a trigger that includes a rollback transaction, the update statement is also rolled back:
begin tran
update stores set payterms = "Net 30"
where stor_id = "8042"
insert salesdetail values ("7777", "JR123",
"PS9999", 75, 40)
commit tran
See Chapter 23, “Transactions: Maintaining Data Consistency and Recovery,” for information on user-defined transactions.
Adaptive Server ignores a rollback trigger executed outside of a trigger and does not issue a raiserror associated with the statement. However, a rollback trigger executed outside a trigger but inside a transaction generates an error that causes Adaptive Server to roll back the transaction and abort the current statement batch.
