Create On-Update Trigger

Create or replace an on-update trigger to track state changes over a selected cube area.

Triggers help you track whether designated constraints are violated during updates (events) in the area, and allow you to specify resultant actions to execute if violations are detected. Minimum permission required: Database Manager.

An on-update trigger is the default type of trigger, even if no type is specified. During a data update process, any cell update that meets a condition specified for the cube area will immediately activate the trigger. On-update triggers are not supported in aggregate storage databases. If you are using an aggregate storage database, you can create after-update triggers.

For more information about the Essbase triggers feature, see the Oracle Essbase Database Administrator's Guide.

Note:

You cannot create or replace a trigger during a calculation, or a data load (including a lock and send).

Syntax

Syntax diagram for create on-update trigger.TRIGGER-NAMECUBE-AREA or MDX-SETCONDITIONACTIONACTION

Use create on update trigger to create a trigger in the following ways:

KeywordDescription

create [on update] trigger

Create a new on-update trigger. The on update keywords are optional; an on-update trigger is created by default.

create or replace [on update] trigger

Create an on-update trigger, or replace an existing trigger of the same name.

log_value OFF

Optional. Log no data values to the trigger spool file. This is the default.

log_value ON

Optional. Log new and old data values to the trigger spool file.

where <cube area>

Define the area of the database to be tracked. Use a valid, symmetric MDX slicer specification.

when <condition>

Define the condition to be tested for using the keyword WHEN followed by a valid MDX conditional expression.

then <action>

Define the action to be taken if the WHEN condition is met. See examples in Examples of Triggers.

else <action>

Optional. Define an action to be taken if the WHEN condition is not met. See examples in Examples of Triggers.

end

The END keyword must terminate every create trigger statement.

Example

create or replace on update trigger Sample.Basic.EastColas
where (Jan, Sales, Actual, [100], East)
when Jan > 20 then spool EastColas_Fail end;

Logs a message in the $ARBORPATH\app\Sample\Basic\trig\EastColas_Fail file.