Launching Workflow from a Trigger – Part 2

The previous post looked at how to launch a workflow from a database trigger, using the CreateProcess and StartProcess (or the LaunchProcess) APIs. However, I’m really against invoking workflows using the APIs any more – this relies on hard-coding in a number of places that can be potentially difficult to change. If you ever wanted your code to communicate with a different workflow, or with more than one workflow, or to run some additional PL/SQL, or just to be able to switch it off for a while, then you need to make changes to either the workflow or to the code that launches it. This logic could be embedded in a number of different places, some of which you might not be aware of – you should raise a business event instead, and then you can easily change the processing logic through event configuration.

But, I digress – this post is meant to be talking about raising a business event from a trigger. As we saw yesterday, there is a restriction which stops you calling Workflow code from a trigger, since a number of savepoints are issued by the workflow engine. If you try to raise a business event directly from a trigger, you end up in the same situation – there are savepoints that are issued which are not allowed. Even if you try to force the event manager to process the subscriptions in a deferred session, you still hit the same problem. So, how can you do this?

The savepoint issue is only an issue if you are trying to raise an event to the manager locally, i.e. via PL/SQL. If you raise the event externally via an AQ, then there is no savepoint issued, so you can do it from the trigger.

There are a few points to be careful of when doing this though:

1. In order to raise the event from a trigger, you will need to manually build up the WF_EVENT_T ADT to enqueue for processing. If you use the RAISE API to raise the event via PL/SQL, this is all done automatically for you (or you can do it manually if need be), but for raising the event externally this needs to be done by your code.

2. In order to process the event, you need to schedule an inbound agent listener to pick it up – if this is not scheduled to run periodically, then the event will never get raised to the event manager for processing. This means that you will have a delay in processing the business event between the trigger firing and the agent listener processing the queue. If you need the event to be fired immediately, then you cannot do it via raising an event, due to this latency.

3. The message is not enqueued onto the queue until the code that fired the trigger does the commit. This ensures transactional integrity throughout – if the controlling session rollsback, then the message is not visible on the queue. [This only applies if you are using the standard seeded queues, or custom queues where the message is only visible on commit. It is not advisable to use queues where the message can be processed before comitting]

So let’s look at exactly how to do it. Here’s a code sample:

10   BEGIN
20      v_event_name        := 'xx.matt.searle.test.event';
30  := 'ORG_ID';
40      v_parameter_t.value := v_org_id;
50      v_parameter_list.extend;
60      v_parameter_list(1) := v_parameter_t;
70  := 'ITEM_NO';
80      v_parameter_t.value := v_segment1;
90      v_parameter_list.extend;
100     v_parameter_list(2) := v_parameter_t;
110     v_event_key := :NEW.LAYER_ID||'-'||TO_CHAR(ln_inv_layer_id)||'-'||TO_CHAR(GetNextEventKey);
120     enqueue_event ( p_event_name       => v_event_name
130                  , p_event_key        => v_event_key
140                  , p_event_parameters => v_parameter_list
150                  , p_msg_id           => v_message_id );
160  END;

Lines 30 and 40 sets the name and value of a parameter to ORG_ID and a value in v_org_id variable. This is then stored in a variable of type WF_PARAMETER_T.
Line 50 adds a new record to the table of parameters.
Line 60 then adds the parameter v_parameter_t to the table of parameters v_parameter_list which is defined as a variable of type WF_PARAMETER_LIST_T as record 1 in the table. When the workflow is initiated, the ORG_ID item attribute will be populated with the value from the parameter. If there is no ORG_ID attribute, then one is created on the fly for you.
Lines 70 to 100 repeat this for the second parameter to pass into the workflow.
Line 110 generates a unique event key, using a function called getNextEventKey to pick a number off a sequence
Lines 120 to 150 will call a procedure to enqueue the message onto an Oracle AQ and return the message ID of the new message on the queue.

The full code (including the enqueue procedure) can be found here.

If you want to make the procedure more generic / resuable, you could include passing a list of the parameters into the procedure and then add these to the event payload. The key thing to note when using this sample is that you need to extend the table for each new parameter, and then make sure that you are setting the right record in the table. You could include something like

v_parameter_count := 0;  := 'ORG_ID';
v_parameter_t.value := v_org_id;
v_parameter_count := v_parameter_count + 1;
v_parameter_list(v_parameter_count) := v_parameter_t;
to ensure that you don’t get the count of the records in the table wrong, and that you increment the count whenever you extend the table. I’ve not done it that way, but what you choose to do with the code from here is up to you!


By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.