DB trigger

Operation name

DB trigger

Functional Overview

DB triggers is the trigger function to monitor the specified database table and execute script according to the status value the status column.

DB triggers passes the data of monitoring table as XML type argument to the script to execute.The script to execute get XML data from DB trigger via XML type script input variable and uses the data to synchronize with the database.

To execute DB trigger, you must prepare a column to specify status value in the monitoring table.

Refer to here for more information about pre-settings.

Fire image

Before trigger firing

During trigger firing

Type of event to monitor

Insert

Fires when the value of the status column in the monitoring table is [Waiting trigger when inserted].
After the script is executed, change the value of [Waiting trigger when inserted] to [Unmodified].

Update

Fires when the value of the status column in the monitoring table is [Waiting trigger when updated].
After the script is executed, change the value of [Waiting trigger when updated] to [Unmodified].

Delete

Fires when the value of the status column in the monitoring table is [Waiting trigger when deleted].
After the script is executed, change the value of [Waiting trigger when deleted] to [Unmodified].

Properties

DB trigger settings

Item name

Content

Remarks

Trigger name

Enter the name of the trigger.

Connection

Select Global Resources.

Refer to here for available global resources.

Watch table

Select or enter the table to be monitored.

The maximum number of tables listed is determined by Global Resource settings.
Variables such as environment variables cannot be used.

Status column

Select or enter the status column to be monitored.

Variables such as Environment Variables cannot be used.

Watch event

Select or enter the status column to be monitored.

[Insert]: (default)
Fires when the value of the status column is [Waiting trigger when inserted].
[Update]:
Fires when the value of the status column is [Waiting trigger when updated].
[Delete]:
Fires when the value of the status column is [Waiting trigger when deleted].

Multiple items can be selected.

Watch interval

Enter the monitoring interval in seconds.

The default value is "10".

End option

Select the option at the end of the script.

[Change status only when succeeded]: (default)
Only when the execution of the script succeeded, change value of all status columns back to [Unmodified] or [Deleted].
[Change status regardless of success/failure]:
Regardless whether the execution of the script succeeded, change value of all status columns back to [Unmodified] or [Deleted].

Store sending data to script in memory

Select whether to hold data retrieved when the trigger fired in the memory.

[Checked]: (default)
Holds data retrieved when the trigger fired in the memory.
[Not Checked]:
Hold data retrieved when the trigger fired in the file.

Select [Not Checked] when the data is large volume.

Status values

Enter a value for each state.

Value	Status
0 (default)	Unmodified
1 (default)	Waiting trigger when inserted
2 (default)	Waiting trigger when updated
3 (default)	Waiting trigger when deleted
4 (default)	Deleted

[Value] can be edited.
Specify [Value] as integer "0" to under "100", or "11000" and higher.(The maximum value depends on the type of the status column.)
The range can also be changed.
Refer to "Execution ID"for more information.
[Value] cannot be specified with duplicate values.
[Status] can not be edited.

Execution content settings

Item name	Content	Remarks
Trigger owner	Select the owner of the trigger to be created.	Can only be selected by users with administrator privileges. (Ordinary users can select themselves only.)
Execution user name	Select the user to run script specified by [Script].
Password	Type the password associated with the user specified by [Execution user name].
Service	Select the script to run by trigger.	Project that was registered as a service displays.
Script	Select the script to run by trigger.	Script in the service specified by [Service] displays.
Script arguments	script input variables that is set in script specified by [Script] displays	The script output variable does not display. It is necessary that Script input variable of XML type is set at execution script. Also XML type script input variable should be placed at the highest position. Refer to "Input variable / Output variable" for how to set script output variable on script and use it.
Script arguments / Variable name	Script variable name is displayed.	Can not be edited.
Script arguments / Type	Script variable number type is displayed.	Can not be edited.
Script arguments / Value	Enter a script variable value.

Execution option settings

For more information about Setting execution option, refer to "Execution option settings".

For pre-settings

When using the DB trigger, requires the following two to be preset.

Add a status column
Add a status column in the table to be monitored.

Example:Add a status column "STATUS_COLUMN" in "SAMPLE" table

The status column date type need to be integer (INTEGER).Data size need to be "5" or more.

Create an XML type script input variable
Create an XML type script input variable in the script to be executed by the trigger.
About Script variables, refer to "Script variable".

Example: Create XML type script input variable "in_xml" in script "Script".

XML data passed from the trigger will be assigned to the script input variable at the top of the script, so please put the XML type script variable on the top of the list.

Available Global Resources

Global resources of the following adapters can be specified in [Connection].

Adapter Type
Access Adapter
DB2 Adapter
MySQL Adapter
Oracle Adapter
PostgreSQL Adapter
SQL Server Adapter
JDBC Adapter
ODBC Adapter
Dr.Sum EA Adapter
Amazon RDS for MySQL Adapter
Amazon RDS for Oracle Adapter
Amazon RDS for PostgreSQL Adapter
Amazon RDS for SQL Server Adapter
Azure SQL Database Adapter

Handling input XML Data

When handling XML data passed from the trigger by Mapper in the executed script, please read the schema by the following procedure.

Place the Read Table operation of the monitored database on the Script Canvas.
Select the same global resource and table with that specified in DB trigger in the properties of Read Table.
Place the Mapper on Script Canvas, draw data flow from Start icon(XML type script input variable).
Mapper editor opens, and select the right-click menu [Load schema] of the root of the input data.
Select the component placed in 1. from [Select from component].

If the monitored table's structure has been changed, repeat the above procedure to update Mapper schema.

About Execution ID

After firing the trigger, the integer value from "1000" to "10999" is used internally as "Execution ID".Each trigger is assigned with 3 execution IDs.
For example, when 100 trigger is fired, there will be 300 execution IDs assigned.The maximum number of execution ID assigned at the same time is 10000 (as for 3333 trigger).
After execution, the assigned execution ID is discarded, and then reused in the trigger firing afterwards.When the execution ID assigned at the same time exceeds 10000, trigger will not be fired.

Execution ID settings can be modified with the following properties file.

Key	Location	Description	Remarks
min.execution.id	$DATASPIDER_HOME/server/plugin/trigger/modules/database_trigger/META-INF/ database_trigger.properties	Specifies the starting number of Execution ID.	Please specify with an integer other than the ones specified in [Status values]. "1000" is set when omitted.(Default) The integers over [Min.execution.id] or under [min.execution.id] + [max.execution.number] cannot be specified in [Status values].
max.execution.number		Specifies the maximum number of execution IDs to be assigned.	Please specify an integer value. "10000" is set when omitted.(Default) The default value is recommended. [Min.execution.id] + [max.execution.number] is the maximum value of Execution ID.

Specification limit

Trigger fired in deleting monitoring event only updates the status column, it does not delete the records.
When necessary, please delete the records where the value is set as [Deleted] in the subsequent process.

XML data passed to script to execute when trigger is fired is the data of the record with the status value of the monitoring event.Data model is "table model type".

Example: When 3 columns: "Column 1", "Column 2" and "Status column" are defined in the table , the data passed to the script to execute is as follows.
```
<table>
  <row>
    <column>value of column 1</column>
    <column>value of column 2</column>
    <column>value of status column</column>
  </row>
    :
</table>
```

When the execution of the script takes more time than [Watch interval], the monitoring of status column will resume after the execution ends.
(in that case, the execution interval of the script becomes the monitoring interval.)
In other words, even if monitoring event occurs during the trigger's execution, the event will not be detected.

Main exceptions

Nothing.

Notes

After registering trigger, if you change the status of the services and scripts that are set to trigger (or add/delete script variables, change service name/script name), those changes are not reflected in the registered trigger.
Open the properties of the target trigger, after re-selection of services and scripts Click [Finish] to re-register.

Status change at the end of the script execution may fail.
- Example: Can not connect to the database
  -> Error is output to server console and server.log.
- Example: The record to change is locked
  -> Trigger processing will end without updating.

If you use a column which includes multibyte letters or platform dependent characters for the [Status column], enclose the column name with quotation marks.
The type of quotation marks suitable varies depending on databases. For more details, please contact database vendors.

You can only use "default" type of global resources for [Connection]