For each of the SQL fields listed above, the SQL expression can either be one of the following:
- Full select statement, starting with "SELECT". If the SQL is to include dynamic information from the triggered event record, values can be referenced by surrounding the attribute name with double-pipe characters. This will accommodate use cases where other Change Notification events or a trigger event on the triggered repository have modified one or more attributes that are referenced in the SQL. The SQL statement should retrieve a superset of columns such that the same SQL can be used for each target, source, or condition that access the same record. If the SQL is for a link, it must return the InternalRecordId for the record. If the SQL is for a value, it must return one more columns which are referenced by the restricted name field.
- Stored procedure invocation, starting with "EXEC". Dynamic information can be included as parameters to the stored procedure in the same manner as the full select statement.
- JOIN/WHERE clauses, starting with "JOIN" relies on the SELECT and FROM clauses will be generated automatically. The FROM clause will reference the snapshot view associated to the repository to which the triggered event is assigned. This view will use the alias 'v". To join with additional tables, the SQL should start with: "JOIN <tableOrView) v2 ON v2.<column> = v.<column>….". If the SQL expression is to include a WHERE clause, dynamic values from the triggered record can be referenced by surrounding each attribute name with double-pipe characters.
- WHERE clause, starting with "WHERE". The trigger repository can be referenced with the "v" alias. Dynamic values from the triggered record can be referenced by surrounding each attribute name with double-pipe characters.
Data from the changed record can be included in the statement through named references (denoted by the attribute name surrounded by double pipe-characters). These references are resolved before the SQL statement is run. If the value needs to be treated as a character value in the query, it must be surrounded by single quotes.
For example, if the EnterWorks data model has a Product repository linked to an Item repository by an alphanumeric ID and changes to Item records need to record the Product Group Name (from the Product record), the following SQL can be used to retrieve that information:
SELECT Product_Group_Name FROM _PIM_Product_Staging WHERE Product_ID = '||Product ID||'In addition to referencing any attribute in the monitored repository, the SQL may also contain the date and time stamp of when the change was made by specifying the reserved reference ||CURRENT_DATETIME||. This is resolved to the current date and time in the format yyyyMMddHHmmssSS. Alternative formats can be specified by using the Java class SimpleDateFormat's notation. For example, to only include the date in mm/dd/yyyy format, the reference must be ||CURRENT_DATETIMEMM/dd/yyyy||.