Project

General

Profile

Database Triggers

In ABL the schema triggers are used as a last resort for validation or otherwise update a record before it is stored to persistent database. There are actually seven types of such triggers. A constant for each of them is defined in DatabaseEventType enum class:

public enum DatabaseEventType
{
   ASSIGN(0x01),
   CREATE(0x02),
   DELETE(0x04),
   FIND(0x08),
   WRITE(0x10),
   REPLICATION_CREATE(0x20) [...],
   REPLICATION_DELETE(0x40) [...],
   REPLICATION_WRITE(0x80)  [...];
   [...]

The first one is particularly different form the other because it defined for a single field of a table. The other only make sense for events of the whole record.

Progress language supports two kind of database triggers:
  • schema triggers: these are defined within the data definition file (.df), but they are resolved at runtime. Their body is stored in a TRIGGER PROCEDURE statement, very similar to an external procedure.
  • session triggers: the programmer can define pieces of code to be executed for the first five types of triggers in a similar manner to a UI-trigger.
The management of these and the exact moment when they are fired is quite complex so there is a special class which manages them: DatabaseTriggerManager. This class assures:
  • the triggers are registered correctly for each scope where permanent database buffers are visible;
  • the schema triggers are resolved at the moment of invocation based on the current PRO-PATH;
  • in case multiple triggers are defined, the proper order of invocation is imposed;
  • enforces the schema triggers to be executed if they are not overridable;
  • prepares the parameters for a specific trigger invocation;
  • keep track of temporary disabled triggers (DISABLE TRIGGERS statement).

To achieve all the above, the manager keeps a complex scoped data structure of TriggerBlockLayer objects which is maintained using calls from TransactionManager via Scopable interface.

Schema Triggers

As noted above, the schema triggers are declared in the database schema and are presented to FWD in the exported .df file. Here is an example:

ADD TABLE "Book" 
  DUMP-NAME "book" 
  TABLE-TRIGGER "Create" OVERRIDE PROCEDURE "trg/create-trig-proc.p" CRC "?" 
  TABLE-TRIGGER "Delete" OVERRIDE PROCEDURE "trg/delete-trig-proc.p" CRC "?" 
  TABLE-TRIGGER "Find" NO-OVERRIDE PROCEDURE "trg/find-trig-proc.p" CRC "?" 
  TABLE-TRIGGER "Write" NO-OVERRIDE PROCEDURE "trg/write-trig-proc.p" CRC "?" 

ADD FIELD "book-id" OF "Book" AS integer 
  FORMAT "99999999" 
  LABEL "Book ID" 
  ORDER 10
  MANDATORY
  FIELD-TRIGGER "Assign" NO-OVERRIDE PROCEDURE "trg/assign-trig-proc-book-id.p" CRC "?" 
[...]

When converted, the information on these triggers are stored with the DMO interface associated with the respective table:

@Table(name = "book", legacy = "Book")
@Triggers({
   @Trigger(event = "CREATE", procedure = "trg/create-trig-proc.p", overridable = true),
   @Trigger(event = "DELETE", procedure = "trg/delete-trig-proc.p", overridable = true),
   @Trigger(event = "FIND", procedure = "trg/find-trig-proc.p"),
   @Trigger(event = "WRITE", procedure = "trg/write-trig-proc.p"),
   @Trigger(event = "ASSIGN", procedure = "trg/assign-trig-proc-book-id.p", field = "book-id"),
})
public interface Book
extends DataModelObject
{
   /**
    * Getter: book-id
    * <p>
    * Book ID
    * 
    * @return  Book ID
    */
   @Property(id = 1, name = "bookId", column = "book_id", legacy = "book-id", format = "99999999", initial = "0", columnLabel = "Book ID", label = "Book ID", mandatory = true, width = 4, order = 10)
   public integer getBookId();
   [...]

Notice the triggers are gathered and collectively stored in @Triggers annotation. The details for each individual trigger is store in a @Trigger annotation. The procedure name and, in case of the ASSIGN trigger, the field name are stored as legacy strings because they are resolved at runtime.

The actual code of the triggers are stored in special ABL source files which contain a single statement. Let's consider the trg/write-trig-proc.p to have the following content:

TRIGGER PROCEDURE FOR WRITE OF book OLD BUFFER oldBook.

FIND sql-counter WHERE buf-name = "book" AND op-name = "WRITE".
sql-counter.ops = sql-counter.ops + 1.

IF oldBook.isbn NE book.isbn THEN 
   MESSAGE "WT:" oldBook.isbn "->" book.isbn.

When converted, this trigger procedure looks like:

@SchemaTrigger(buffer = Book.Buf.class, event = DatabaseEventType.WRITE, table = "fwd.book", hasOldBuffer = true)
@DatabaseReferences(aliases = { "fwd" })
public class WriteTrigProc
extends DatabaseTrigger<Book.Buf> {
   SqlCounter.Buf sqlCounter = RecordBuffer.define(SqlCounter.Buf.class, "fwd", "sqlCounter", "sql-counter");
   Book.Buf book;
   Book.Buf oldbook;

   /** External procedure (converted to Java from the 4GL source code in trg/write-trig-proc.p). */
   @Override
   @LegacySignature(type = Type.MAIN, name = "trg/write-trig-proc.p")
   public void write(final Book.Buf _book, final Book.Buf oldbook) {
      WriteTrigProc.this.book = RecordBuffer.defineAlias(Book.Buf.class, _book, "book", "TRIGGER");

      externalProcedure(WriteTrigProc.this, TransactionType.FULL, new Block((Body) () ->  {
         RecordBuffer.openScope(book, oldbook, sqlCounter);
         WriteTrigProc.this.oldbook = oldbook;
         new FindQuery(sqlCounter, "upper(sqlCounter.bufName) = 'BOOK' and upper(sqlCounter.opName) = 'WRITE'", null, "sqlCounter.recid asc").unique();
         sqlCounter.setOps(plus(sqlCounter.getOps(), 1));

         if (_isNotEqual(oldbook.getIsbn(), book.getIsbn())) {
            message(new Object[] {
               "WT:", (character) new FieldReference(oldbook, "isbn").getValue(),
               "->",  (character) new FieldReference(book, "isbn").getValue()
            });
         }
      }));
   }
}

A first thing to observe is the @SchemaTrigger annotation. It contain some details about how this trigger procedure should be invoked, the supported buffers and the trigger type. All these are used in the background by the DatabaseTriggerManager to load and prepare the procedure as a valid trigger.

Then the abstract class DatabaseTrigger which our trigger extends. Any generated trigger must implement only one of the all default methods. Also, the code inside it is generated naturally, with the exception of the passed parameters. The current buffer use a buffer alias because it is bound to the new scope of the trigger block. The manager makes sure the optional OLD BUFFER, oldbook is passed in read-only mode so it does not need a scoping wrapper.

The other trigger type is the ASSIGN. It is invoked each time the associated field is modified. Such procedure will contain a statement as follows:

TRIGGER PROCEDURE FOR ASSIGN OF book.book-id OLD VALUE old-book-id.
It specifies the buffer, the field involved and, optionally, the old value of the field before the change took place. When converted to Java, the procedure with a similar code of the above will look like this:

@SchemaTrigger(buffer = Book.Buf.class, event = DatabaseEventType.ASSIGN, field = "bookId", table = "fwd.book", hasOldBuffer = true)
@DatabaseReferences(aliases = { "fwd" })
public class AssignTrigProcBookId
extends DatabaseTrigger<Book.Buf> {
   SqlCounter.Buf sqlCounter = RecordBuffer.define(SqlCounter.Buf.class, "fwd", "sqlCounter", "sql-counter");
   Book.Buf book;
   integer oldBookId;

   /** External procedure (converted to Java from the 4GL source code in trg/assign-trig-proc-book-id.p). */
   @Override
   @LegacySignature(type = Type.MAIN, name = "trg/assign-trig-proc-book-id.p")
   public void assign(final Book.Buf _book, final BaseDataType _oldBookId) {
      AssignTrigProcBookId.this.book = RecordBuffer.defineAlias(Book.Buf.class, _book, "book", "TRIGGER");

      externalProcedure(AssignTrigProcBookId.this, TransactionType.FULL, new Block((Init) () ->  {
         AssignTrigProcBookId.this.oldBookId = (integer) _oldBookId;
      }, 
      (Body) () ->  {
         RecordBuffer.openScope(book, sqlCounter);

         new FindQuery(sqlCounter, "upper(sqlCounter.bufName) = 'BOOK' and upper(sqlCounter.opName) = 'ASSIGN' and upper(sqlCounter.opField) = 'BOOK-ID'", null, "sqlCounter.recid asc").unique();
         sqlCounter.setOps(plus(sqlCounter.getOps(), 1));

         message(new Object[] {
            "WT:", oldBookId,
            "->",  (character) new FieldReference(book, "isbn").getValue()
         });
      }));
   }
}

As in case of the WRITE trigger, the manager makes sure the passed buffer is correctly scoped and write accessible and the old value of the field is provided if the trigger requires it in the annotation.

Session Triggers

The session triggers are pieces of code the programmer can use to replace or enhance the data handling from the schema triggers. Unlike the latter which are valid in all programs which use the specific table, these are short-lived, scoped to the specific block where they were defined. This is handled automatically by the DatabaseTriggerManager.

Let's construct a pair of these, with a similar content with the schema trigger exemplified above:

ON WRITE OF book OLD BUFFER old-book DO:
   FIND sql-counter WHERE buf-name = "book" AND op-name = "WRITE".
   sql-counter.ops = sql-counter.ops + 1. 

   IF old-book.isbn NE book.isbn THEN 
      MESSAGE "SESSION-WRITE:" old-book.isbn "->" book.isbn.
END.

ON ASSIGN OF book.book-id OLD VALUE old-book-id OVERRIDE DO:
   FIND sql-counter WHERE buf-name = "book" AND op-name = "ASSIGN" AND op-field = "book-id".
   sql-counter.ops = sql-counter.ops + 1.

   MESSAGE "SESSION-ASSIGN:" book.book-id "->" old-book-id.
END.

The FWD will create separate internal procedures for each of these triggers and register them with the DatabaseTriggerManager:

      registerDatabaseTrigger(DatabaseEventType.WRITE, Book.Buf.class, TriggerBlock0.class, TriggerExample.this, false, true);
      registerDatabaseTrigger(DatabaseEventType.ASSIGN, Book.Buf.class, "bookId", TriggerBlock1.class, TriggerExample.this, true, true);
      [...]

   public class TriggerBlock0
   extends DatabaseTrigger<Book.Buf> {
      @Override
      public void write(final Book.Buf book, final Book.Buf oldBook) {
         trigger(new Block() {
            public void body() {
               RecordBuffer.openScope(book, oldBook);
               DatasetExample.this.book = book;
               DatasetExample.this.oldBook = oldBook;

               new FindQuery(sqlCounter, "upper(sqlCounter.bufName) = 'BOOK' and upper(sqlCounter.opName) = 'WRITE'", null, "sqlCounter.recid asc").unique();
               sqlCounter.setOps(plus(sqlCounter.getOps(), 1));

               if (_isNotEqual(oldBook.getIsbn(), book.getIsbn())) {
                  message(new Object[] {
                     "SESSION-WRITE:", (character) new FieldReference(oldBook, "isbn").getValue(),
                     "->",             (character) new FieldReference(book, "isbn").getValue()
                  });
               }
            }
         });
      }
   }

   public class TriggerBlock1
   extends DatabaseTrigger<Book.Buf> {
      @Override
      public void assign(final Book.Buf bookBuf2, final BaseDataType _oldBookId) {
         trigger(new Block() {
            public void pre() {
               DatasetExample.this.oldBookId = (integer) _oldBookId;
            }

            public void body() {
               RecordBuffer.openScope(bookBuf2);
               DatasetExample.this.bookBuf2 = bookBuf2;

               new FindQuery(sqlCounter, "upper(sqlCounter.bufName) = 'BOOK' and upper(sqlCounter.opName) = 'ASSIGN' and upper(sqlCounter.opField) = 'BOOK-ID'", null, "sqlCounter.recid asc").unique();
               sqlCounter.setOps(plus(sqlCounter.getOps(), 1));

               message(new Object[] {
                  "SESSION-ASSIGN:", (integer) new FieldReference(bookBuf2, "bookId").getValue(),
                  "->",              oldBookId
               });
            }
         });
      }
   }

Other Operations with Triggers

Optionally, a schema trigger can be always revered back using specialized form of the statement:

ON WRITE OF book REVERT.
ON ASSIGN OF book.book-id REVERT.
Which, in turn, will be converted by FWD into following statements:
         deregisterDatabaseTrigger(DatabaseEventType.WRITE, Book.Buf.class);
         deregisterDatabaseTrigger(DatabaseEventType.ASSIGN, Book.Buf.class, "bookId");

Finally, in Progress the triggers may temporarily be suspended using 

DISABLE TRIGGERS FOR DUMP OF Book.
DISABLE TRIGGERS FOR LOAD OF Book ALLOW-REPLICATION.
These pieces of code will be converted to Java as:
         disableDumpTriggers(Book.Buf.class);
         disableLoadTriggers(Book.Buf.class, true);