Using Triggers in Blackfish SQL Tables

This chapter discusses triggers and Blackfish SQL tables.

About Triggers
Viewing Triggers
Creating Triggers in Blackfish SQL for Windows Databases
Creating Triggers in Blackfish SQL for Java Databases

You can create row level triggers for a Blackfish SQL table. In Blackfish SQL for Java, you can implement them in Java. In Blackfish SQL for Windows, you can implement them in Delphi, C#, or VB.NET.

All trigger methods must be declared static and have only one parameter of type TriggerContext. The TriggerContext class is symantically identical in Blackfish SQL for Java and Blackfish SQL for Windows. However, there are differences in syntax for the two different platforms. On the Windows platform, the class TriggerContext is in the Borland.Data.DataStore namespace. On the Java platform, the TriggerContext class is in the com.borland.datastore package.

The trigger context can be used to obtain access to:

A connection object
The new row for insert, and update triggers
The old row for update and delete triggers

These rules apply:

New row values are modified in a BEFORE UPDATE or BEFORE INSERT trigger.
Old row values cannot be modified.
Foreign key and primary key constraints are applied after BEFORE triggers.
AFTER triggers are called after the operation has completed, including successful completion of foreign key and primary key constraints.
The trigger implementation should avoid DML operations against the same table. This has the potential for infinite recursion.
If there are multiple triggers of the same type for the same table, the calling order of the triggers is the order in which they were created.
Commit and rollback operations are ignored inside the execution of the trigger If an exception is thrown from inside a trigger. The affects of the statment that caused the trigger to be executed will be rolled back.

The requirements for deploying applications with trigger implementations are the same for both stored procedures and triggers.

Viewing Triggers

To view the triggers created for a table, call the DB_ADMIN.GET_TRIGGERS stored procedure. For a complete description see the Stored Procedures Reference.

Creating Triggers in Blackfish SQL for Windows Databases

Use the CREATE TRIGGER statement to create a trigger in a Blackfish SQL for Windows database. See the SQL Reference for syntax and examples of the CREATE TRIGGER statement.

Example

These example statements create triggers for the Customer class:

Copy Code

CREATE TRIGGER BEFORE_INSERT_CUSTOMER BEFORE INSERT ON CUSTOMER AS 
  OrderEntryAssembly::OrderEntry.Customer.beforeInsertTrigger
CREATE TRIGGER BEFORE_UPDATE_CUSTOMER BEFORE UPDATE ON CUSTOMER AS 
  OrderEntryAssembly::OrderEntry.Customer.beforeUpdateTrigger
CREATE TRIGGER BEFORE_DELETE_CUSTOMER BEFORE DELETE ON CUSTOMER AS 
  OrderEntryAssembly::OrderEntry.Customer.beforeDeleteTrigger
  
CREATE TRIGGER AFTER_INSERT_CUSTOMER AFTER INSERT ON CUSTOMER AS 
  OrderEntryAssembly::OrderEntry.Customer.afterInsertTrigger
CREATE TRIGGER AFTER_UPDATE_CUSTOMER AFTER UPDATE ON CUSTOMER AS 
  OrderEntryAssembly::OrderEntry.Customer.afterUpdateTrigger
CREATE TRIGGER AFTER_DELETE_CUSTOMER AFTER DELETE ON CUSTOMER AS 
OrderEntryAssembly::OrderEntry.Customer.afterDeleteTrigger

This shows the Delphi implementation of the Customer class triggers:

Copy Code

TCustomer = class
  class procedure BeforeInsertTrigger(Context: TriggerContext); static;
  class procedure BeforeUpdateTrigger(Context: TriggerContext); static;
  class procedure BeforeDeleteTrigger(Context: TriggerContext); static;
  class procedure AfterInsertTrigger(Context: TriggerContext); static;
  class procedure AfterUpdateTrigger(Context: TriggerContext); static;
  class procedure AfterDeleteTrigger(Context: TriggerContext); static;
end;

{ TCustomer }
  class procedure TCustomer.AfterDeleteTrigger(Context: TriggerContext);
    begin
      HandleBeforeInsert(Context.GetNewRow());
    end;
  class procedure TCustomer.AfterInsertTrigger(Context: TriggerContext);
    begin
      HandleBeforeUpdate(Context.GetOldRow(), Context.GetNewRow());
    end;      
  class procedure TCustomer.AfterUpdateTrigger(Context: TriggerContext);
    begin
      HandleBeforeDelete(Context.GetOldRow(), Context.GetNewRow());
    end;      
  class procedure TCustomer.BeforeDeleteTrigger(Context: TriggerContext);
    begin
      HandleAfterInsert(Context.getNewRow());
    end;
  class procedure TCustomer.BeforeInsertTrigger(Context: TriggerContext);
    begin
      HandleAfterUpdate(Context.getNewRow());
    end;
  class procedure TCustomer.BeforeUpdateTrigger(Context: TriggerContext);
    begin
      HandleAfterDelete(Context.getNewRow());
    end;

This shows the C# implementation of the Customer class triggers:

Copy Code

public class Customer {
  public static void BeforeInsertTrigger(TriggerContext Context)
  {
    HandleBeforeInsert(Context.GetNewRow());
  }
  public static void BeforeUpdateTrigger(TriggerContext Context)
  {
    HandleBeforeUpdate(Context.GetOldRow(), Context.GetNewRow());
  }
  public static void beforeDeleteTrigger(TriggerContext Context)
  {
    HandleBeforeDelete(Context.getNewRow());
  }
  public static void afterInsertTrigger(TriggerContext Context) 
  {
    HandleAfterInsert(Context.getNewRow());
  }
  public static void afterUpdateTrigger(TriggerContext Context) 
  {
    HandleAfterUpdate(Context.getNewRow());
  }
  public static void afterDeleteTrigger(TriggerContext Context) 
  {
  HandleAfterDelete(Context.getNewRow());
  }
}

Creating Triggers in Blackfish SQL for Java Databases

Use the CREATE TRIGGER statement to create a trigger in a Blackfish SQL for Java database. See the SQL Reference for syntax and examples of the CREATE TRIGGER statement.

Examples:

These example statements create triggers for the Customer class:

Copy Code

CREATE TRIGGER BEFORE_INSERT_CUSTOMER BEFORE INSERT ON CUSTOMER AS 
  orderentry.Customer.beforeInsertTrigger
CREATE TRIGGER BEFORE_UPDATE_CUSTOMER BEFORE UPDATE ON CUSTOMER AS 
  orderentry.Customer.beforeUpdateTrigger
CREATE TRIGGER BEFORE_DELETE_CUSTOMER BEFORE DELETE ON CUSTOMER AS 
  orderentry.Customer.beforeDeleteTrigger

CREATE TRIGGER AFTER_INSERT_CUSTOMER AFTER INSERT ON CUSTOMER AS 
  orderentry.Customer.afterInsertTrigger
CREATE TRIGGER AFTER_UPDATE_CUSTOMER AFTER UPDATE ON CUSTOMER AS 
  orderentry.Customer.afterUpdateTrigger
CREATE TRIGGER AFTER_DELETE_CUSTOMER AFTER DELETE ON CUSTOMER AS 
  orderentry.Customer.afterDeleteTrigger

This shows the Java implementation of the Customer class triggers:

Copy Code

public class Customer {
  public static void beforeInsertTrigger(TriggerContext context)
    throws Exception
    {
      handleBeforeInsert(context.getNewRow());
    }
  public static void beforeUpdateTrigger(TriggerContext context)
    throws Exception
    {
      handleBeforeUpdate(context.getOldRow(), context.getNewRow());
    }
  public static void beforeDeleteTrigger(TriggerContext context)
    throws Exception
    {
      handleBeforeDelete(context.getOldRow(), context.getNewRow());
    }                       
  public static void afterInsertTrigger(TriggerContext context) 
    {
      handleAfterInsert(context.getNewRow());
    }
  public static void afterUpdateTrigger(TriggerContext context) 
    {
      handleAfterUpdate(context.getNewRow());
     }   
  public static void afterDeleteTrigger(TriggerContext context) 
    {
      handleAfterDelete(context.getNewRow());
    }
 }