/
external-select.ad
41 lines (32 loc) · 1.95 KB
/
external-select.ad
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
==== External Select
Occasionally, you may find that you need to capture and save away a piece of data present in another table when a trigger is firing.
This data is typically needed for the purposes of determining where to 'route' the data to once routing takes place. Each trigger
definition contains an optional "external select" field which can be used to specify the data to be captured. Once captured, this
data is available during routing in DATA 's external_data field.
For these cases, place a SQL select statement which returns the data item you need for routing in external_select.
See <<Trigger Variables>> for a list of variables available for use.
IMPORTANT: The external select SQL must return a single row, single column
ifdef::pro[]
.Sample External Select SQL that returns STORE_ID based on the ORDER_ID captured in the trigger.
[source, SQL]
====
select STORE_ID
from order
where order_id=$(curTriggerValue).$(curColumnPrefix)order_id
====
endif::pro[]
ifndef::pro[]
.Sample Trigger With External Select SQL that returns STORE_ID based on the ORDER_ID captured in the trigger.
[source, SQL]
====
insert into SYM_TRIGGER
(trigger_id,source_table_name,channel_id,external_select, last_update_time,create_time)
values ('orderlineitem', 'orderlineitem','orderlineitem',
'select STORE_ID from order where order_id=$(curTriggerValue).$(curColumnPrefix)order_id',
current_timestamp, current_timestamp);
====
endif::pro[]
WARNING: External select SQL statements should be used carefully as they will cause the trigger to run the additional SQL each time the trigger fires.
TIP: Using an external select on the trigger is similar to using the 'subselect' router. The advantage of this approach over the 'subselect' approach
is that it guards against the (somewhat unlikely) possibility that the master Order table row might have been deleted before routing has taken place.
This external select solution also is a bit more efficient than the 'subselect' approach.