ASA SQL User's Guide
Transact-SQL Compatibility
Transact-SQL procedure language overview
Trigger compatibility requires compatibility of trigger features and syntax. This section provides an overview of the feature compatibility of Transact-SQL and Adaptive Server Anywhere triggers.
Adaptive Server Enterprise executes triggers after the triggering statement has completed: they are statement level, after triggers. Adaptive Server Anywhere supports both row level triggers (which execute before or after each row has been modified) and statement level triggers (which execute after the entire statement).
Row-level triggers are not part of the Transact-SQL compatibility features, and are discussed in Using Procedures, Triggers, and Batches.
Features of Transact-SQL triggers that are either unsupported or different in Adaptive Server Anywhere include:
Triggers firing other triggers Suppose a trigger carries out an action that would, if carried out directly by a user, fire another trigger. Adaptive Server Anywhere and Adaptive Server Enterprise respond slightly differently to this situation. By default in Adaptive Server Enterprise, triggers fire other triggers up to a configurable nesting level, which has the default value of 16. You can control the nesting level with the Adaptive Server Enterprise nested triggers option. In Adaptive Server Anywhere, triggers fire other triggers without limit unless there is insufficient memory.
Triggers firing themselves Suppose a trigger carries out an action that would, if carried out directly by a user, fire the same trigger. Adaptive Server Anywhere and Adaptive Server Enterprise respond slightly differently to this situation. In Adaptive Server Anywhere, non-Transact-SQL triggers fire themselves recursively, while Transact-SQL dialect triggers do not fire themselves recursively.
By default in Adaptive Server Enterprise, a trigger does not call itself recursively, but you can turn on the self_recursion option to allow triggers to call themselves recursively.
ROLLBACK statement in triggers Adaptive Server Enterprise permits the ROLLBACK TRANSACTION statement within triggers, to roll back the entire transaction of which the trigger is a part. Adaptive Server Anywhere does not permit ROLLBACK (or ROLLBACK TRANSACTION) statements in triggers because a triggering action and its trigger together form an atomic statement.
Adaptive Server Anywhere does provide the Adaptive Server Enterprise-compatible ROLLBACK TRIGGER statement to undo actions within triggers. See ROLLBACK TRIGGER statement.