FirebirdSQL logo
 VIEWPROCEDURE 

Database Triggers

Triggers can be defined to fire upon “database events”;a mixture of events that act across the scope of a session (connection), and events that act across the scope of an individual transaction:

  • CONNECT

  • DISCONNECT

  • TRANSACTION START

  • TRANSACTION COMMIT

  • TRANSACTION ROLLBACK

[fblangref50-ddl-trgr-ddltrigger] are a subtype of database triggers, covered in a separate section.

Who Can Create a Database Trigger?

Database triggers can be created by:

Execution of Database Triggers and Exception Handling

CONNECT and DISCONNECT triggers are executed in a transaction created specifically for this purpose.This transaction uses the default isolation level, i.e. snapshot (concurrency), write and wait.If all goes well, the transaction is committed.Uncaught exceptions cause the transaction to roll back, and

  • for a CONNECT trigger, the connection is then broken and the exception is returned to the client

  • for a DISCONNECT trigger, exceptions are not reported.The connection is broken as intended

TRANSACTION triggers are executed within the transaction whose start, commit or rollback evokes them.The action taken after an uncaught exception depends on the event:

  • In a TRANSACTION START trigger, the exception is reported to the client and the transaction is rolled back

  • In a TRANSACTION COMMIT trigger, the exception is reported, the trigger’s actions so far are undone and the commit is cancelled

  • In a TRANSACTION ROLLBACK trigger, the exception is not reported and the transaction is rolled back as intended.

Traps

There is no direct way of knowing if a DISCONNECT or TRANSACTION ROLLBACK trigger caused an exception.It also follows that the connection to the database cannot happen if a CONNECT trigger causes an exception and a transaction cannot start if a TRANSACTION START trigger causes one, either.Both phenomena effectively lock you out of your database until you get in there with database triggers suppressed and fix the bad code.