DB Trigger Services (Magic xpi 4.13)

The trigger service provides a mechanism to capture the CRUD operations in the database with the help of database triggers. A user can capture the Create, Update and Delete events for multiple tables in the selected database and trigger the flow based on these events.

For this, the user needs to provide the database connection details and then add the tables for which the Create, Update and Delete event needs to be captured. Multiple tables can be grouped under the events. These events will then be used by the trigger defined for the flows.

The DB Trigger service contains the following parameters. The parameters in bold are mandatory.

Parameter	Description
JDBC URL	Enter the JDBC URL to connect to the database. The JDBC URL can be selected using environment variable as well. The JDBC URL must contain the database server JDBC URL. It can optionally contain the database name, user name and password in the required format.
Database Name	Enter the Database name. If the database name is defined as part of JDBC URL, this field can be left blank. The database name entered here will take precedence over the database name given in the JDBC URL.
User Name	Enter the user name to connect to the database. If the user name is defined as part of JDBC URL, this field can be left blank. The User Name entered here will take precedence over the User Name given in the JDBC URL.
Password	Enter the password to authenticate with the database. If the user name is defined as part of JDBC URL, this field can be left blank. The password entered here will take precedence over the password given in the JDBC URL.
Cleanup Age (In Days)	Enter the cleanup age value in days. This value will be used to cleanup the events which are processed and are past the cleanup age. By default, the cleanup will be done at midnight. Alternatively, the user can set explicit cleanup hour value between 1 to 24. To change the default value, add a dbtrigger.clean.at property to the JVM_ARG in the Magic.ini file. If the value for the cleanup age is empty, then by default it will be set to 1 day at runtime.

The DB Trigger service also gives the following buttons:

Name	Description
Validate	The validate button first will attempt to connect to the database based on the database parameters. Once the connection is successful, it will check if the TriggerAction table exists in the database. If the Triggers table is not present in the database, it will create the table. If the database connection is successful and the Triggers table is created, it will display a success message.
Events	Click this button to open the Trigger Events dialog. If the database definition is not validated, this will validate it first.

DB Trigger Service connects to the different databases using their respective JDBC drivers. Magic xpi installation does not ship all the supported JDBC drivers. The user is required to provide them manually, if required. The JDBC driver jar files should be placed in <Magic xpi installation path>\Runtime\Java\DatabaseDrivers before creating the DB Trigger Service.

TriggerAction Table

On the Trigger Events dialog, a user will add the tables for which the Create, Update or Delete events need to be captured. Once all the events are added, a template script will be generated for creating the triggers in the database. This script will use the TriggerAction table to store the CRUD events which are captured. This table will be polled at runtime by the DB Trigger. The rows which are processed will be marked for clean-up. Once these records are past the cleanup age, defined above, they will be deleted from the table.

The records in the table will be processed based on the following:

· The new events will be inserted in the TriggerAction table with the value of the TriggerStatus column set to 0. All the records with value as 0 will be processed by the DB Trigger at runtime.

· Once a record is processed, the TriggerStatus value will be changed to 1. All the rows in the table with TriggerStatus as 1 are eligible for clean-up.

· If the processing fails for any record the TriggerStatus value will be set to 2. It is the user's responsibility to take action on the failed rows. To re-process these records, the user will have to fix the error and then set the TriggerStatus value to 0.

Before executing a project at runtime. make sure that the TriggerAction table exists in the database and all the required triggers are created (using the generated scripts) on the tables selected in the Trigger Events. For the Oracle database, the sequence with the name TRIGGERACTION_ACTIONID_SEQ should also be dropped after running the drop command on the TriggerAction table. Use the following database commands for dropping the table and the sequence: DROP TABLE TriggerAction; DROP SEQUENCE TRIGGERACTION_ACTIONID_SEQ;

List of Supported Databases

The databases supported by the DB Trigger Service are:

· MS-SQL

· Oracle

· MySQL

· DB2

· DB2/400

· PostgreSQL

Below is the list of the supported databases drivers certified for Magic xpi. This is not an exhaustive list and any other compatible driver may also work.

Database	Driver Name JAR File(s)
MSSQL	sqljdbc4-4.0.jar
MySQL	mysql-connector-java-5.1.41.jar
Oracle	ojdbc7.jar
PostgreSQL	postgresql-42.2.6.jre7.jar
DB2/400	Jt400.jar
DB2	db2jcc4.jar db2jcc.jar db2jcc_license_cu.jar

(Since version: 4.13)

Related Topics

Trigger Events