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 element(s) 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 set to YES, 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 set to YES, 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 set to YES, 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 set to YES, 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 set to YES, 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 set to YES, 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 SQL Query and Email actions. 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 (i.e. Insert, Delete, Update).

    • Database - Specifies database related events (i.e. Shutdown, Startup, Shutdown_Any, Dropped).

    • Object - Specifies object related events (i.e. Altered, Dropped).

    • Subscription - Specifies subscription related events (i.e. 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.

 

NOTE: A full list of AMTrigger objects exclusive to each trigger can be viewed from the Expression Builder by expanding Objects -> Triggers and selecting the desired trigger.