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

• 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.

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.