Database Trigger

Description

Triggers task execution when a specific command or operation is performed on a SQL or Oracle database.

Practical Usage

Ideal for automated execution of database related operations. For instance, a SQL query task can start upon entry of data into a specific table.

Related Topics  

Parameters

General

Property	Type	Description
Database type	Options	The type of database to monitor. The available options are: SQL - MS SQL database will be monitored. Oracle - Oracle database will be monitored.
Server	Text	The name of the database server to be monitored.
Username	Text	The username used to authenticate with the database.
Password	Text	The password linked with the username used to authenticate with the database.
Notification port	Number	Indicates the port number that the notification listener listens on for database notifications. If the port number is set to -1 (default), a random port number is assigned to the listener when started. This parameter is active only if the Database type parameter is set to Oracle.

Prerequisites

Property	Type	Description
Table	Text	The database table which holds the data elements to be monitored. This value must include the schema name and table name separated by a dot (.) entered in the following format (minus the brackets): [Schema_Name].[Table_Name].
Insert	Yes/No	If selected, an insert command will trigger task execution (set to yes by default). The INSERT statement is used to add new rows of data to a table.
Drop	Yes/No	If selected, a drop command will trigger task execution (set to no by default). The DROP command permanently deletes a table from the database.
Delete	Yes/No	If selected, a DELETE command will trigger task execution (set to no by default). The DELETE statement is used to delete rows from a table.
Alter	Yes/No	If selected, an ALTER command will trigger task execution (set to no by default). The ALTER command is used to modify the definition (structure) of a table by modifying the definition of its columns.
Update	Yes/No	If selected, an UPDATE command will trigger task execution (set to no by default). The UPDATE statement is used to modify the existing rows in a table.

Behavior

Property	Type	Description
Enable trigger	Yes/No	If selected, the trigger will immediately be active upon creation.
Trigger after the condition has been met 'X' times.	Number	If enabled, specifies the total number of times the monitored condition must transpire in order for the trigger to become active. For example, if a Process trigger is set to wait for the "Notepad" process to start and this parameter is set to 3, the task will not launch until the third instance of the "Notepad" process starts.

Additional Notes

AMTrigger

When this trigger is activated, it automatically passes the AMTrigger object to the task. AMTrigger is a standard Automate dataset and can be used much like the datasets created by the Database - SQL query activity and Email action. The fields of AMTrigger can be used within a task to determine specific values, such as whether or not the task was started by a trigger, which trigger started the task, when the trigger was activated and other properties. AMTrigger populates a unique set of field–value pairs for each Automate trigger. The following table lists the ones specific to this trigger. For more details about a specific AMTrigger field–value pair, click the associated link.

Name	Data Type	Return Value
AMTrigger.Action	String	Returns the action that took place to cause the task to trigger.
AMTrigger.Source	String	Returns the database event source that activated the trigger. This value varies depending on the type of database being monitored: SQL - If monitoring a SQL database, the return value indicates the source that initially activated the trigger, such as a change to the query data or the database’s state. Oracle - If monitoring an Oracle database, the return value can be an enumeration related to the database event source. For example, if a table has been altered and a new row has been inserted into the same table, this property is set to either Object or Data. Below lists other possible return values: Data - Specifies data related events (for example, Insert, Delete, Update). Database - Specifies database related events (for example, Shutdown, Startup, Shutdown_Any, Dropped). Object - Specifies object related events (for example, Altered, Dropped). Subscription - Specifies subscription related events (for example, subscription end).
AMTrigger.Table	String	Returns the name of the table that was originally monitored to cause the task to trigger.
AMTrigger.Type	String	Returns the database event type that activated the trigger. This value varies depending on the type of database being monitored: SQL - If monitoring a SQL database, describes the different notification types that can be received: Change - Data on the server being monitored changed. Subscribe - There was a failure to create a notification subscription. Unknown - Used when the type option sent by the server was not recognized by the client. Oracle - If monitoring an Oracle database, property returns the database event type for the notification: Change - A change occurred in the database. Subscribe - A change occurred in the subscription. Query - A query-based change occurred in the database.