Creating Database Event Activity

The Database Event enables you to schedule a process flow to be triggered when a record is inserted, updated or deleted in a database table.

Prerequisites

Ensure that you have created Database Info activity.

Steps to create a Database Event Activity

Click Configure > EVENTS > Database Event.
Click CREATE Database Target.
On Create Database Event window, enter the name and description of the new Database Source in the Name and Description textboxes respectively.
Select the database info activity from the drop-down list of Database Info.
You can create the database event definition by entering an SQL Query or a database trigger command. By default, SQL Query option is selected. Enter the query in the SQL Query field. For example:
select * from PurchaseOrder where processingStatus='Ready'

Here:
processingStatus is a field in the source database table, which stores the status of the records. For example: If any record is already processed or not. This is important to make sure that same record should not be processed again and again.

You can use any existing field for this purpose or you can add an additional field.
In the above example, only those records, whose processingStatus is Ready, are picked for processing.

In the database event, the table name used in the From part of the query always be same as the query used in the database schema. Also, make sure that aliasing table names, etc. must be same in both Event and Schema.
Select Trigger For All Records check box to trigger this event for all the records in the selected database.
Select Check Condition check box to trigger the process flow based on a condition. The result of the query will be compared with a conditional value, and the process flow will be triggered if the values match. This option is disabled if you have selected Trigger For All Records check box.
1. Select the operator for the query from the Operator.
2. Type the value to be compared to the query in the Value. The query will return only one record. If the query returns multiple records then only first record is accepted. If the query returns one record then it will compare the value of the first field with the value specified in the Value field. If the value matches then the process flow is triggered. If the value does not match then system logs an error. It does not trigger the process flow.
  
  If Check Condition check box is not selected then process flow is triggered each time a row is returned.
  In case, Trigger For All Records check box is selected, then for all the records, only one process flow is triggered processing all the records.
Click Execute Update Query check box to update the records that are picked by the event for processing.
1. Type the update query in the Update Query. For example:
  update PurchaseOrder set processingStatus='%%Pass%%'
  
  The update query is executed for each record based on the select query in the previous step. Database Event executes update immediately after picking up the record to update the column storing the status of the record. This ensures the records are not picked up again. For example in the query given above, the processingStatus will be updated to Pass.
2. Enter the where condition. For example :
  where id='%%id%%'
  
  Where condition is used with Update Query to update only those records that satisfy this where condition. It can be any column or set of columns that make the record unique. In the above example id is a column in the database which uniquely identifies the records and %%id%% is the value of this column in the selected record.
Alternately, You can enter the database trigger command in the SQL Trigger field.

Following is the format of trigger used to trigger the process flow:
<Trigger Text>
INSERT INTO dbeventtriggertable VALUES ('Query =<WHERE CLAUSE>');
END <trigger name> ;

Edit the parts, which are within < >. You can define a 'Where' clause that indicates the row that is updated. When the command is parsed, it will return the updated row from the database source.
Do not delete the Insert query.

<trigger name> after the END tag should be used for Oracle only. In case of SQL server, <trigger name> is not needed.

Following is the example of the trigger used for SQL Server :

create trigger Trigger_test on emp for
insert,update
as
declare @empname varchar(20)
begin
set @empname=(select empname from inserted);
INSERT INTO dbeventtriggertable VALUES ('Query =WHERE empname=''' @empname'''');

END ;
Following is the example of the trigger used for Oracle:
CREATE OR REPLACE TRIGGER Trigger_test
AFTER INSERT OR UPDATE ONEmp FOR EACH ROW BEGIN INSERT INTO dbeventtriggertable VALUES ('Query = where rowid=||:new.rowid); END Trigger_test;

Here:
Trigger_test is name of the trigger.
Emp is the name of the user table on which insert or update operation has to be done.
dbeventtriggertable is the name of the temporary table used. Do not change it.
Type the name of Trigger in the SQL Trigger Name. This option is enabled if you have selected SQL Trigger option.
Select the date from which Database event will start triggering in the Event Start Date. The date must be in MM/dd/yyyy format. Click calendar icon and select the required date from the calendar.
Select the start time when the event will start triggering from the Time.
Select the date on which Database event will stop triggering in the Event Expiry Date. The date must be in MM/dd/yyyy format. Click the calendar icon and select the required date from the calendar.
Select the time when the event will stop triggering from the Time.

Select the type through which event will trigger - Simple or Cron. If you select Simple then the event will trigger as per the specified time interval. If you select Cron then the event will trigger as per the specified date and time.

If you select Simple then type the time interval in the Polling Frequency. Enter the digit in the Frequency and then select the unit of time from the Duration.

We recommended minimum Polling Frequency to be more than 60 seconds.

If you select Cron then select the day when the event will trigger from the Firing Days. Define the frequency of execution in the Firing Schedule. For information about Firing Schedule, click Help or refer to Cron Expression.

Days	Description
All Days	The event will fire on all days (Mon to Sun) of the week.
Business Days	The event will fire from Monday to Friday excluding holidays.
Week Days	The event will fire from Monday to Friday even if there are any holidays.

Expand Advanced Properties. All items in Advance Properties are displayed.
Enter the recipient(s) email address in the textbox Notification Receiver Email-ID(s). You can add multiple email address by adding comma after each email address.
Select Trigger in Sequence check box to trigger the Event in sequential manner. If you do not select the Trigger in Sequence check box then the associated Event will trigger concurrently.
If you want to enable misfire handling for your JMS event then, select the Run Once option from the Action on Misfire Event drop-down list. If you do not wish to enable misfire handling for your file event then, select the Ignore Misfire option.

The Run Once option will run only one collective instance of all the misfires for a particular event. Whereas the Ignore Misfire option will not run a misfired event.

The default value of Action on Misfire Event field is set to Run Once for all of the earlier events.
Click Save.