Knowledge base

Trigger mechanism

A trigger is a program defined on a database, written in the language of the database, executed when an event occurs on that database. Typically you define triggers on a database table to execute a program whenever there is an insert, update or delete on that table.

This technique is used to inform PRODBX of changes on focused tables.

However, it requires your database supports triggers. If it doesn't, you will have to program functions in your application, called by that application whenever an event occurs, that do the same as described below.

How it works:

First you have to create 2 additional tables:

  • TRB: circular buffer containing the table names, and primary key information of the records changed

Column

Format

PK

Description

IND

Numeric

X

Index or line number identifying the record

TABL

String(50)

 

Table name triggering this record or scheduled task to be kicked off.

EVENT

String(1)

 

1=insert
2=update
3=delete

SK1

String(50)

 

Alphanumeric primary key information of the record triggering this record.

SK2

String(50)

 

Alphanumeric primary key information of the record triggering this record.

SK3

String(50)

 

Alphanumeric primary key information of the record triggering this record.

SK4

String(50)

 

Alphanumeric primary key information of the record triggering this record.

SK5

String(50)

 

Alphanumeric primary key information of the record triggering this record.

NK1

Numeric

 

Numeric primary key information of the record triggering this record.

NK2 Numeric   Numeric primary key information of the record triggering this record.
NK3 Numeric   Numeric primary key information of the record triggering this record.
NK4 Numeric   Numeric primary key information of the record triggering this record.
NK5 Numeric   Numeric primary key information of the record triggering this record.
FLD String(50)   Alphanumeric holding the fieldname of the update field. FLD is
mandatory to be defined, but not mandatory to be used.

TDS_
UPDATE

Date

 

Optional field containing the timestamp when the record was written.

Myfield

 

 

Optional field. Free naming. Additional information that must be copied to
the STACK in order to use it in a PRODBX function.

 

  • HEAD: table with only one record containing the index of the last line written in TRB and the maximum number of records TRB may contain.

Column

Format

PK

Description

HEADER

Numeric

X

Index of the last line written to the TRB trigger / command buffer

MAXI

Numeric

 

Maximum number of entries the TRB buffer will / may hold.

Remember: if you create these tables in a different environment (user) then the main database, you must specify this in the database connections section of the configuration manager.

Suppose we have a table MYTABLE of which each record is uniquely identified when you give the contents of MYFIELD1 (alphanumeric), MYFIELD2 (alphanumeric) and MYFIELD3 (numeric). You create the triggers on MYTABLE. Whenever there is an insert, update or delete on MYTABLE the corresponding trigger will:

  • Read the value of HEADER in table HEAD
  • Increment this value by 1 or set the value to 1 when MAXI is reached and write this value back to HEADER in table HEAD.
  • Insert or update the record in TRB having IND equal this new value (HEADER). Other fields in TRB will be:
    • TABL = 'MYTABLE'
    • EVENT = 1, 2 or 3 depending on insert, update or delete
    • SK1 = content of MYFIELD1
    • SK2 = content of MYFIELD2
    • NK1 = content of MYFIELD3
    • eventually TDS_UPDATE = now

When PRODBX polls the database it will retrieve all records from TRB with IND between last read record and HEADER, and start interpreting these records.

PRODBX will look for as many primary key fields as defined in the Tables and table mappings section of the configuration manager. It will sort all primary key fields defined as alphanumeric and map them on SK1 to SK5, and sort all primary key fields defined as numeric and map them on NK1 to NK5. This is very important to know when you write the trigger, to map your database fields on SK1...NK5 by sorting them in alphabetic order and type.

The flowchart for a trigger would look like:

The HEAD and TRB table may be defined in a different library or user then the focused database. We call this the control library. You can specify this separately in the database connections section of your configuration manager.

This technique has the disadvantage that PRODBX has to poll the system on regular bases, even if there was no change on the database. The amount of data to be transferred is limited to the one and only record from TRB. If you are more familiar with ADO, you may replace this trigger technique with event driven ADO calls. Our personal experience is that yes the trigger mechanism gives some communication overhead to be tuned very carefully, but ADO most of the times gives you headache.

Oracle trigger

MS-SQL server trigger

Remark: Instead of writing the tablename in TRB.TABL you can also write the scheduled task name (be careful: case sensitive). The logic is that when PRODBX cannot find the tables and mappings for the TRB.TABL entry, it will also check if a scheduled task with this name exists. If so, it will kick off this task.