Database Trigger

To learn more about triggers in general, see section Understanding Triggers.

Database trigger event occurs when a change in the monitored database table is detected. There might be new records, or existing records that have been updated. Database trigger doesn't wait for any event change, such as data delivery. Instead, it pulls data from the database in defined time intervals.

Typical usage: An existing business system executes a transaction, which in effect updates data in a database table. Loftware Automation detects the updated and new records, and prints their contents on labels.

General

This section allows you to configure the most important file trigger settings.

  • Name: Specifies the unique name of the trigger. The names helps you distinguish between different triggers when you configure them in Automation Builder, and later when you run them in Automation Manager.

  • Description: Allows you to describe the role of this trigger. Help the users with a short explanation about what the trigger does.

  • Database connection: Specifies connection string to the database. Click Define to open the Database dialog box. Use it to configure the database connection, including database type, table name, and user credentials. You have to connect to a database that supports access with SQL commands. For this reason, you cannot use the database trigger to automatically detect data changes in CSV text files or Microsoft Excel spreadsheets.

    [Note] Note

    Configuration details depend on the type of selected database. The options in the dialog box depend on the database driver that you use. For configuration details, see user guide for your database driver. For more information about database connectivity, see section Accessing Databases.

  • Check database in the time intervals: Specifies the time interval in which the database is polled for changes in records.

  • Detection Options and Advanced: These options allow you to fine-tune the record detection mechanism. After the records are acquired from the database, Actions tab automatically displays the For Each Record action, using which you map table fields to label variables.

    Get records based on unique incremental field value

    With this option enabled, the trigger monitors the specified auto-incremental numeric field in the table. Loftware Automation remembers the field's value for the last processed record. At the next polling interval, only records with values greater than the remembered value are acquired.

    To configure this option, select the table name in which the records reside (table name), the auto-incremental field (key field) and the starting value for the field (key field default value). Internally, the variable KeyField is used to refer to the last remembered value of key field.

    [Note] Note

    The last value of the key field is remembered internally, but is not updated back into configuration, so the value for key field default value does not change in this dialog box. You can safely reload configuration and/or stop/start this trigger in the Automation Manager and still keep the last remembered value. However, if you remove the configuration from Automation Manager and add it back, the value of last remembered key field is reset to what you have defined in key field default value.

    Get records and delete them

    With this option selected, all records are acquired from the table and deleted. To configure this option, select the table name in which the records reside (table name) and specify the primary key in the table (key fields). While Automation allows you to have a table without a primary key, it is strongly recommended that you define the primary key. If the primary key exists, the records are deleted one by one if a particular record is processed in the actions.

    [Warning] Warning

    If the primary key does not exist, all records obtained by the current trigger are deleted at once. That's fine if no error occurs during record processing. However, if there is a processing error with some record, Automation stops processing further records. Because all records captured in this polling interval have already been deleted without being processed, you can lose data. Therefore, having a primary key in a table is advisable.

    SQL Code Examples

    [Note] Note

    These SQL statements are read-only and provided for reference only. To provide custom SQL statements, select the Get and manage records with custom SQL detection method.

    Example table:

    ID

    ProductID

    CodeEAN

    ProductDesc

    AlreadyPrinted

    1

    CAS0006

    8021228110014

    CASONCELLI ALLA CARNE 250G

    Y

    2

    PAS501

    8021228310001

    BIGOLI 250G

    3

    PAS502GI

    8021228310018

    TAGLIATELLE 250G

    Example of Update SQL statement in case the table contains primary index: 

    DELETE FROM [Table]
WHERE [ID] = :ID

    ID field in the table is defined as primary index. The :ID construct in WHERE clause contains value of field ID in each iteration. For the first record, the value of ID is 1, for the second record 2, etc. Specifying the colon in front of the field name in SQL statement specifies how the variable is used.

    Example of Update SQL statement in case the table does has no primary index defined: 

    DELETE FROM [Table]

    If no primary index is defined in the table, all records are deleted from the table after the first record gets processed.

    Get records and update them

    In this case, all records are acquired from the table and updated. You can write a custom value into field in the table as indication 'this records has already been printed'. To configure this option, you have to select the table name, in which the records reside (table name), select the field that you want to update (update field), and enter the value that will be stored in the field (update value). Internally, the variable UpdateValue is used in the SQL statement to refer to the current value of field (update value).

    While Automation allows you to have a table without a primary key, it is strongly recommended that you define a primary key. If the primary key exists, the records will be updated one by one , when the particular record is processed in the actions.

    [Warning] Warning

    If the primary key does not exists, all records obtained in the trigger are updated at once. That's fine if there is no error processing the records. But if there is an error processing some record, the Automation stops processing further records. Because all records captured in this polling interval have already been updated without being processed in actions, you can lose data. Therefore, having a primary key in a table is a good idea.

    SQL Code Examples

    [Note] Note

    These SQL statements are read-only and provided as a reference only. To provide custom SQL statements, select the Get and manage records with custom SQL detection method.

    Example table:

    ID

    ProductID

    CodeEAN

    ProductDesc

    AlreadyPrinted

    1

    CAS0006

    8021228110014

    CASONCELLI ALLA CARNE 250G

    Y

    2

    PAS501

    8021228310001

    BIGOLI 250G

    3

    PAS502GI

    8021228310018

    TAGLIATELLE 250G

    Example of Update SQL statement, if table contains primary index: 

    UPDATE [Table]
SET [AlreadyPrinted] = :UpdateValue
WHERE [ID] = :ID

    ID field in the table is defined as primary index. The construct :ID in the WHERE clause contains the value of field ID in each iteration. For first record, the value of ID is 1, for second record 2, etc. Adding a colon in front of the field name in SQL statement specifies usage of a variable. The field UpdateValue is defined in the trigger configuration using the Update value edit field.

    Example of Update SQL statement, when table does not have primary index defined: 

    UPDATE [Table]
SET [AlreadyPrinted] = :UpdateValue

    If no primary index is defined in the table, all records from the table are updated, after the first record gets processed.

    Get and manage records with custom SQL

    In this case, the creation of SQL statements for record extraction and field updates is entirely up to you. To configure this option, you have to provide a custom SQL statement to acquire records (search SQL statement) and a custom SQL statement to update the records after processing (update SQL statement). Click the Test button to test-execute your SQL statements and see the result on-screen.

    You can use table field values or values of trigger variables as parameters in the WHERE clause in the SQL statement. You would precede the field or variable name using the colon character (:). This instructs Loftware Automation to use the current value of that field or variable.

    SQL Code Examples

    Example table:

    ID

    ProductID

    CodeEAN

    ProductDesc

    AlreadyPrinted

    1

    CAS0006

    8021228110014

    CASONCELLI ALLA CARNE 250G

    Y

    2

    PAS501

    8021228310001

    BIGOLI 250G

    3

    PAS502GI

    8021228310018

    TAGLIATELLE 250G

    Example of Search SQL statement:

    To get the records that haven't already been printed, do the following. The field AlreadyPrinted neither must contain value Y, nor have blank or NULL value.

    SELECT * FROM Table
WHERE AlreadyPrinted <> 'Y' or AlreadyPrinted is NULL

    From the sample table above, two records with ID values 2 and 3 will be extracted. The first record has already been printed and will be ignored.

    Example of Update SQL statement:

    To mark the already printed records with value Y in the AlreadyPrinted field, do the following:

    UPDATE [Table]
SET [AlreadyPrinted] = 'Y'
WHERE [ID] = :ID

    Put colon (:) in front of the variable name in your SQL statement to identify it as a variable. You can use any field from the table for parameters in the WHERE clause. In the example, we are updating the AlreadyPrinted field only for the currently processed record (value of field ID must be the same as the value from the current record). In the similar way, you would refer to other fields in the record as :ProductID or :CodeEAN, or even refer to variables defined inside this database trigger.

    To delete the current record from the table, do the following:

    DELETE FROM [Table]
WHERE [ID] = :ID

    Show SQL statement: Expand this section to see the generated SQL statement and to write your own statement if you have selected the option Get and manage records with custom SQL.

Previewing SQL Execution

To test the execution of SQL sentences and to see what their effect is, click Test in the toolbar of the SQL edit area. The Data Preview section opens in the right-hand pane. Click the Execute button to start the SQL code. If you use values of table field in the SQL statement (with colon (:) in front of the field name), you have to provide test values for them.

[Note] Note

If you have Data Preview open and you have just added some variables to the script, click Test button twice. This closes and opens Data Preview section and updates the list of variables in the preview.

  • Simulate execution: Specifies that all changes made to the database are ignored. The database transaction is reverted so no updates are written to the database.

Execution

The options in Execution specify when does the database updating take place. The update type depends on the Detection Options for the trigger.

  • Before processing actions: Specifies that records are updated before the actions defined for this trigger have started to execute.

  • After processing actions: Specifies that records are updated after the actions defined for this trigger have been executed. Usually you want to update the records after they have been successfully processed.

[Note] Note

If necessary, you can update the records while the actions are still executing. For more information, see section Execute SQL Statement.

Other

Options in the Feedback from the Print Engine section specify communication parameters that allow you to receive print engine feedback.

Cloud_Business.png

  • Supervised printing: Activates synchronous printing mode. Use it whenever you want to send the print job status back to the third party application. For more information, see section Synchronous Print Mode.

Options in the Data Processing section specify if you want to trim the data so that it fits into a variable, or ignore the missing label variables. By default, reports errors and breaks the printing process if you try to save values that are too long in label variables, or try to set values for non-existing label variables.

  • Ignore excessive variable contents: truncates data values that exceed the length of the variable as defined in the label designer to make them fit. This option is in effect if you are setting variable values in filters, from command files, and when you are setting values of trigger variables to label variables of the same name.

    Example 21. Example

    Label variable accepts 5 characters at maximum. With this option enabled, any value longer than 5 characters is truncated to the first 5 characters. If the value is 1234567 ignores digits 6 and 7.


  • Ignore missing label variables: When printing with command files (such as JOB file), the printing process ignores all variables that are:

    • specified in the command file (using the SET command)

    • not defined on the label

    Similar happens if you define assignment area in a filter to extract all name-value pairs, but your label contains fewer variables.

    When setting values of non-existing label variables, reports an error. If this option is enabled, the printing continues.

Options in Scripting section specify scripting possibilities.

  • Scripting language: Selects scripting language for the trigger. All Execute script actions that you use within a single trigger use the selected scripting language.

Options in the Save Received Data section specify the available commands for data that the trigger receives.

  • Save data received by the trigger to file: Enable this option to save the data received by the trigger. The option Variable enables variable file name. Select a variable that contains path and file name.

  • On error save data received by the trigger to file: Enable this option to save the data into the trigger only if an error occurs during the action execution. You might want to enable this option to keep the data that caused the issue ready for troubleshooting.

    [Note] Note

    Make sure you enable the Supervised printing support. If not, cannot detect errors during the execution. For more information, see section Synchronous Print Mode.
    [Note] Note

    saves the received data into a temporary file. This temporary file is deleted right after the trigger execution completes. The internal variable DataFileName points to that file name. For more information, see Internal Variables.

Security

  • Lock and encrypt trigger: Enables trigger protection. If you enable it, the trigger becomes locked and you cannot edit it any longer. This encrypts the actions. Only users with password can unlock the trigger and modify it.