Triggers in Essbase

The triggers feature provided by Essbase Analytic Services enables efficient monitoring of data changes in a database.

If data breaks rules specified in a trigger. Analytic Services can log relevant information in a file, or, for some triggers, can send an e-mail alert (to a user or system administrator). For example, you might want to notify the sales manager if, in the western region, sales for a month fall below sales for the equivalent month in the previous year
There are two types of triggers:
1.On-update triggers
2.After-update triggers

On-update triggers are activated during the update process, when the block containing the data referenced by the trigger is brought into memory.

After-update triggers are activated after the update transaction is complete.

Note:On-update triggers are supported only on block storage database.

To administer triggers, a user must have Database Designer security privillege. Analytic Services monitors and potentially activates triggers during the following activities:
->Data load
->Calculation
->Lock and Send from Essbase Spreadsheet Add-in (does not apply to aggregate storage databases)

You can specify whether all trigger data values are stored in the spool file or whether only the most current values are stored (for example, use the a log_value parameter on the Maxl create trigger statement or create and replace trigger statement). If the log_value parameter is set to ON, both the new value and old value are logged to the spoolfile. If the log-value parameter is set to OFF, values are not logged to the spool file. the log_value parameter is active only for data load and lock-and-send activities.
Example:
Tracking Inventory Level
Tracks the inventory level for the following product, region and months:
->Colas (product 100)
->Eastern region (market East)
->January, February, and March (the children of Qtr1)

The trigger is activated after the update action is complete. If the inventory of coals in the eastern region falls below 500,000, the example logs an entry in the file Inventory-East.

Create after update trigger Sample.basic.Inventory_east
Where "(crossjoin ({children([qtr1])},
{([Market].[East],[Product].[100],[Inventory].[Ending Inventory])}))"
when [Ending Inventory]<500000 then
spool Inventory_East
end;