FirebirdSQL logo

IN AUTONOMOUS TRANSACTION Examples

Using an autonomous transaction in a trigger for the database ON CONNECT event, to log all connection attempts, including those that failed:

CREATE TRIGGER TR_CONNECT ON CONNECT
AS
BEGIN
  -- Logging all attempts to connect to the database
  IN AUTONOMOUS TRANSACTION DO
    INSERT INTO LOG(MSG)
    VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
  IF (EXISTS(SELECT *
             FROM BLOCKED_USERS
             WHERE USERNAME = CURRENT_USER)) THEN
  BEGIN
    -- Logging that the attempt to connect
    -- to the database failed and sending
    -- a message about the event
    IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO LOG(MSG)
      VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
      POST_EVENT 'CONNECTION ATTEMPT BY BLOCKED USER!';
    END
    -- now calling an exception
    EXCEPTION EX_BADUSER;
  END
END

POST_EVENT

Posts an event for notification to registered clients on commit

Syntax
POST_EVENT event_name;
Table 1. POST_EVENT Statement Parameter
Argument Description

event_name

Event name (message) limited to 127 bytes

The POST_EVENT statement notifies the event manager about the event, which saves it to an event table.When the transaction is committed, the event manager notifies applications that have registered their interest in the event.

The event name can be a code, or a short message: the choice is open as it is a string of up to 127 bytes.Keep in mind that the application listening for an event must use the exact event name when registering.

The content of the string can be a string literal, a variable or any valid SQL expression that resolves to a string.