Creating Triggers - Firebird

A trigger is defined with the CREATE TRIGGER statement, which is composed of a Header and a body. The trigger header, which is quite different from a stored procedure header, contains

  • A trigger name that is unique within the database
  • A table name that identifies the table with which to associate the trigger
  • Attributes that determine state, phase, DML event, and, optionally, sequence

    The trigger body, like a stored procedure body, contains

    • An optional list of local variables and their data types.
    • A block of statements in Firebird procedure and trigger language, bracketed by BEGIN and END. These statements are performed when the trigger fires. A block can itself include other blocks, so that there may be many levels of nesting.

    Syntax

    For all versions of Firebird, the syntax pattern for CREATE TRIGGER is

    CREATE TRIGGER name FOR {table| view}
    [ACTIVE | INACTIVE]
    {BEFORE | AFTER} {DELETE | INSERT | UPDATE}
    [POSITION number]
    AS <trigger_body> ^
    <trigger_bod y>=[<variable_declaration_list>]<block>
    <variable_declaration_list> =DECLARE VARIABLE
    variable datatype;
    [DECLARE VARIABLE variable datatype;...]
    <block>=
    BEGIN
    <compound_statement>[<compound_statement>...]
    END
    <compound_statement>=<block>|statement;

    In v.1.5, it is possible to merge all events into a single phase-trigger:

    CREATE TRIGGER name FOR {table | view}
    [ACTIVE | INACTIVE]
    {BEFORE | AFTER}
    {DELETE OR {[INSERT [OR UPDATE]} | {INSERT OR [..]} | {UPDATE OR [..]}}
    [POSITION number]
    AS <trigger_body> ^

    Header Elements

    Everything preceding the AS clause forms the trigger header. The header must specify the unique name of the trigger and the name of an existing, committed table or view that it is to belong to.

    Naming Triggers

    The syntax requires that the trigger name be unique among all trigger names in the database. It is good practice to adopt some convention for naming triggers that is meaningful to you and obvious to others who will work with your database. The author uses a “formula” identifying phase and event (BI | AI | BU | AU | BD | AD | BA | AA, the latter two representing “Before All” and “After All”), table name, and sequence number, if relevant. For example, a Before Insert trigger for the Customer table might be named BI_Customer1.

    CREATE TRIGGER BI_CUSTOMER FOR CUSTOMER...

    Trigger Attributes

    The remaining attributes in the trigger header are

    • The trigger status, ACTIVE or INACTIVE, determines whether the trigger will be “up and running” when it is created. ACTIVE is the default. Deactivating a trigger is useful during development and testing.
    • The phase indicator, BEFORE or AFTER, determines the timing of the trigger relative to the write action being executed by the DML event.
    • The DML event indicator specifies the type of SQL operation that shall trigger execution of the module: INSERT, UPDATE, or DELETE.
    • In Firebird 1.0.x, exactly one event indicator must be specified. From v.1.5 onward, the optional <event> OR <event>... extension allows two or three events to be coded conditionally into a single module. For example, ...BEFORE INSERT OR UPDATE OR DELETE... allows you to provide actions for all three events. The Boolean context variables INSERTING, UPDATING, and DELETING support the branching logic.
    • The optional sequence indicator, POSITION number, specifies when the trigger is to fire in relation to other trigger modules for the same phase and event.

    The Trigger Body

    In all Firebird code modules, the body consists of an optional list of local variable declarations followed by a block of statements. Programming a trigger body is exactly the same as programming a stored procedure body—refer to the preceding chapter. Of interest to us in this chapter are some special extensions PSQL provides to support the trigger context and some special roles for triggers in implementing and enforcing business rules.

    Triggers can invoke stored procedures. The calling rules are exactly the same for trigger modules as for stored procedures. Exception-handling techniques are discussed in the next chapter.

    Triggers can process cursors, perform operations on other tables, and post events. They can throw and handle exceptions, including those raised from nested procedures. Triggers are never called by procedures, other triggers, or applications. They do not support input or output arguments at all.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics