TRIGGER command

The TRIGGER command associates a previously-created program to an object and identifies the object event that automatically executes the program; or disassociates a trigger program from the object.

To assign a trigger program to an object, the object must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

Syntax

TRIGGER {event-name [program-name] }... | {DELETE event-name}... | DELETE ALL

where event-name is one of the following:

MAINTAIN

DELETE

PROPERTY

ASSIGN

BEFORE_UPDATE

AFTER_UPDATE

You can use the same keyword many times in a single TRIGGER statement; however, in this case, Oracle OLAP ignores all but the last occurrence of the keyword. See "Multiple Occurrences of the Same Keyword", for details.

Parameters

MAINTAIN

Specifies that the trigger for the program is a Maintain event. A Maintain event is the execution of the MAINTAIN statement. As outlined in Table 8-19, "Subevents for the MAINTAIN Event", the Maintain event has several subevents that correspond to the major keywords of the MAINTAIN command. Exactly when a program triggered by a Maintain event is executed is dependent on the Maintain subevent that triggered the program and the object type for which the Maintain event is defined:.

Programs triggered by Maintain Add and Maintain Merge events on dimensions and composites are executed after the entire MAINTAIN statement executes.
Programs triggered by Maintain Add and Maintain Merge events on dimension surrogates are executed multiple times—once after each value is added or merged.
Programs triggered by other Maintain subevents are executed before the MAINTAIN statement is executed.

DELETE

Specifies that the trigger for the program is a Delete event. A Delete event is a DELETE statement for the object. Oracle OLAP executes the specified program immediately before a DELETE statement deletes the object.

PROPERTY

Specifies that Oracle OLAP executes the specified program in response to a Property event. A Property event is the execution of a PROPERTY statement to create, modify, or delete an object property. A program that is triggered by a Property event is executed before the statement that triggered it.

ASSIGN

Specifies that Oracle OLAP executes the specified program in response to a Assign event. An Assign event is executed when SET assigns values to variable, relation, worksheet object, or a formula. A program that is triggered by SET is executed each time Oracle OLAP assigns a value to the object for which the event was defined. Thus, a program triggered by an Assign event is often executed over and over again as the assignment statements loops through a object assigning values.

UPDATE

When the object has been acquired using ACQUIRE in an analytic workspace that is attached in multiwriter mode, specifies that Oracle OLAP executes the specified program immediately after the object is updated.

Tip:

To specify processing when the entire analytic workspace is updated, create a TRIGGER_AFTER_UPDATE or TRIGGER_BEFORE_UPDATE program.

program-name

The name of the trigger program. When omitted for an event, the event does not trigger an action.

DELETE event-name

Deletes the triggers for the specified object events. Oracle OLAP disassociates the trigger program from the specified object event.

DELETE ALL

Deletes all of the triggers for the specified object. Oracle OLAP disassociates the trigger program from all events for object.

Usage Notes

Multiple Occurrences of the Same Keyword

You can use all of the keywords in a single TRIGGER statement. However, if you use the same keyword twice in a TRIGGER statement, then Oracle OLAP recognized the last occurrence of the keyword; other occurrences are ignored.

For example, assume that you code the following TRIGGER statement.

TRIGGER PROPERTY progname1 PROPERTY progname2 PROPERTY progname3

When executing this TRIGGER statement, Oracle OLAP executes progname3 immediately before a property of the object is created, modified, or deleted; Oracle OLAP does not execute progname1 or progname2.

Examples

Example 10-159 Creating Triggers

Assume that your analytic workspace contains a TEXT dimension named city and that you want to create programs that automatically execute when a MAINTAIN statement executes against city or when a property is created or deleted for city. To create these triggers, you issue the following statements.

"Define the trigger programs
DEFINE trigger_maintain_move_city PROGRAM BOOLEAN
DEFINE trigger_property_city PROGRAM BOOLEAN
"Associate the trigger programs to events for the city dimension
CONSIDER city
TRIGGER PROPERTY trigger_property_city
TRIGGER MAINTAIN trigger_maintain_move_city

Example 10-160 Describing Triggers

Assume that you have created the triggers for city as described in Example 10-159, "Creating Triggers" . Later you want to see the description of the triggers, to do so you cannot issue a DESCRIBE statement for your analytic workspace. Instead, you must issue a FULLDSC statement.

DEFINE CITY DIMENSION TEXT
TRIGGER MAINTAIN TRIGGER_MAINTAIN_MOVE_CITY -
        PROPERTY TRIGGER_PROPERTY_CITY
 
DEFINE TRIGGER_MAINTAIN_MOVE_CITY PROGRAM BOOLEAN
 
DEFINE TRIGGER_PROPERTY_CITY PROGRAM BOOLEAN

Example 10-161 Deleting Triggers

Assume that you have created the triggers described in Example 10-159, "Creating Triggers" . Now you want to delete the MAINTAIN trigger for city. To delete this trigger you issue the following statements.

CONSIDER city
TRIGGER DELETE MAINTAIN

When you issue a FULLDSC statement, you confirm that the MAINTAIN trigger for city has been deleted although the trigger_maintain_move_city program remains.

DEFINE CITY DIMENSION TEXT
TRIGGER PROPERTY TRIGGER_PROPERTY_CITY
 
DEFINE TRIGGER_MAINTAIN_MOVE_CITY PROGRAM BOOLEAN
 
DEFINE TRIGGER_PROPERTY_CITY PROGRAM BOOLEAN

To actually delete the trigger_maintain_move_city program you must issue the following statement.

DELETE TRIGGER_MAINTAIN_MOVE_CITY

Example 10-162 A MAINTAIN Trigger Program

Assume that you have a dimension with the following definition in your analytic workspace.

DEFINE CITY DIMENSION TEXT

To create a Maintain trigger for city, you take the following steps:

Define the trigger program as a user-defined function. It can have any name that you want. The following statement defines a program named trigger_maintain_city.
```
DEFINE trigger_maintain_city PROGRAM BOOLEAN
 
```

Specify the content of the program.

PROGRAM
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
RETURN TRUE
END

Issue a TRIGGER statement to associate the trigger program with the city dimension as a program to be executed when a Maintain event occurs. Remember to use a CONSIDER statement to make the definition for city the current definition.
```
CONSIDER city
TRIGGER MAINTAIN TRIGGER_MAINTAIN_CITY
```

When you issue a FULLDSC statement to see a full description of your analytic workspace, you can see the definition of city (including its Maintain trigger) and the trigger_maintain_city program.

DEFINE CITY DIMENSION TEXT
TRIGGER MAINTAIN TRIGGER_MAINTAIN_CITY
 
DEFINE TRIGGER_MAINTAIN_CITY PROGRAM BOOLEAN
PROGRAM
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
RETURN TRUE
END

As illustrated in the following statements and output, when you issue MAINTAIN statements for city, the trigger_maintain_city program executes.

MAINTAIN city ADD 'Boston' 'Houston' 'Dallas'

calltype = TRIGGER
triggering event = MAINTAIN
triggering subevent = ADD
 
REPORT city
 
CITY
--------------
Boston
Houston
Dallas
 
MAINTAIN city MOVE 'Dallas' to 2

calltype = TRIGGER
triggering event = MAINTAIN
triggering subevent = MOVE
 
REPORT city
 
CITY
--------------
Boston
Dallas
Houston

Example 10-163 An ASSIGN Trigger on a Variable

Assume that your analytic workspace contains objects with the following definitions.

DEFINE geog DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <geog>
DEFINE percent_sales VARIABLE INTEGER <geog>

The sales variable contains the following values.

GEOG             SALES
-------------- ----------
North America        0.59
Europe               9.35
Asia                   NA

The percent_sales variable is empty.

Assume that you want specialized processing of values when you assign values to percent_sales. To handle this processing automatically, you can create a Assign trigger program for percent_sales by taking the following steps:

Create a trigger program that executes each time you assign values to percent_sales.

DEFINE TRIGGER_EQ PROGRAM BOOLEAN
PROGRAM
ARGUMENT datavalue WORKSHEET
show 'description of triggering object = ' 
FULLDESCRIBE &TRIGGER(NAME)
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
SHOW JOINCHARS ('value being assigned = ' datavalue)
SHOW '  '
END

Add an assign trigger to percent_sales using a TRIGGER statement. Remember to first issue a CONSIDER statement to make the definition for the percent_sales variable the current definition.
```
CONSIDER percent_sales
TRIGGER ASSIGN TRIGGER_EQ
```

Assign values to percent_sales.

percent_sales = (sales/TOTAL(sales))*100

Assigning values to percent_sales triggers the execution of the trigger_eq program and produces the following output lines.

description of triggering object = 
DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG>
TRIGGER ASSIGN TRIGGER_EQ
calltype = TRIGGER
triggering event = ASSIGN
triggering subevent = 
value being assigned = 6

description of triggering object = 
DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG>
TRIGGER ASSIGN TRIGGER_EQ
calltype = TRIGGER
triggering event = ASSIGN
triggering subevent = 
value being assigned = 94

description of triggering object = 
DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG>
TRIGGER ASSIGN TRIGGER_EQ
calltype = TRIGGER
triggering event = ASSIGN
triggering subevent = 
value being assigned =

Note:

From the output you can see that Oracle OLAP called the trigger_eq program three times—each time it assigned a value to percent_sales.

When you issue REPORT commands for sales and percent_sales you can see the result of the calculations. The percent_sales variable contains values that are the percent of sales for each continent.

GEOG                  SALES
-------------- --------------------
North America                  0.59
Europe                         9.35
Asia                             NA
 
GEOG              PERCENT_SALES
-------------- --------------------
North America                     6
Europe                           94
Asia                             NA

Example 10-164 Setting Values in an ASSIGN Trigger Program

Assume that you have the following objects in your analytic workspace.

DEFINE GEOGRAPHY DIMENSION TEXT WIDTH 12
LD Geography Dimension Values
 
DEFINE PRODUCT DIMENSION TEXT WIDTH 12
LD Product Dimension Values
 
DEFINE TIME DIMENSION TEXT WIDTH 12
LD Time Dimension Values
 
DEFINE CHANNEL DIMENSION TEXT WIDTH 12
LD Channel Dimension Values
 
DEFINE F.MARGIN FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
LD Margin
EQ f.sales-f.costs
 
 
DEFINE F.COSTS VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>
LD Costs
 
DEFINE F.SALES VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>
LD Sales

Note that f.costs, f.sales, and f.margin all have the same dimensions.

Now you add an Assign trigger to f.margin that executes a program named t.margin. The definition of f.margin is modified to the following definition.

DEFINE F.MARGIN FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
LD Margin
TRIGGER ASSIGN T.MARGIN
EQ f.sales-f.costs

Now you actually write the t.margin program. When an expression is assigned to the f.margin formula, the program uses this value to compute new values for f.costs and f.sales.

DEFINE T.MARGIN PROGRAM
PROGRAM
ARG newVal DECIMAL       " The value passed to the program by the Assign trigger
VARIABLE t.valDiff DECIMAL     " Difference between newVal and old value
VARIABLE t.costInc DECIMAL     " Amount the difference makes to costs
"show the value of newVal
SHOW 'newVal = ' NONL
SHOW newVal
" Compute the difference between the current value and the new one
t.valDiff = newVal - f.margin
" Now increase costs proportional to their existing amounts
t.costInc = (newVal - f.margin) * (f.costs/f.sales)
" Adjust the values of sales and costs to get the new value
SET1 f.costs = f.costs + t.costInc
 
SET1 f.sales =  f.sales + t.valDiff + t.costInc
 
SHOW geography NONL
SHOW ' ' NONL
SHOW product NONL
SHOW ' ' NONL
SHOW channel NONL
SHOW ' ' NONL
SHOW time NONL
SHOW ' f.costs = 'NONL
SHOW f.costs NONL
SHOW ' f.sales = 'NONL
SHOW f.sales
END

Now assume that you issue the following LIMIT statements to identify a subset of data and issue a REPORT statement to report on the values of f.margin.

LIMIT t0.hierdim TO 'STANDARD'
LIMIT time TO t0.levelrel EQ 'L2'
LIMIT geography TO FIRST 1
LIMIT channel TO FIRST 1
LIMIT product TO FIRST 5
REPORT DOWN time ACROSS product: f.margin
 
GEOGRAPHY: WORLD
CHANNEL: TOTALCHANNEL
               -----------------------F.MARGIN-----------------------
               -----------------------PRODUCT------------------------
TIME           TOTALPROD   AUDIODIV  PORTAUDIO    PORTCD     PORTST
-------------- ---------- ---------- ---------- ---------- ----------
Q1.96          54,713,974 29,603,546  5,379,661  2,480,914  1,615,708
Q2.96          63,919,784 34,594,087  6,331,848  2,869,265  1,931,785
Q3.96          58,303,490 31,543,152  5,792,725  2,616,515  1,795,701
Q4.96          71,197,892 38,383,878  7,059,581  3,163,804  2,232,880
Q1.97          55,489,723 29,989,262  5,368,237  2,491,475  1,607,344
Q2.97          41,687,908 22,532,979  4,070,725  1,855,992  1,245,161

Now you issue the following assignment statement that increase the value of f.margin by 10% and report it

f.margin = f.margin * 1.1

The execution of this assignment statement triggers the execution of the Assign trigger program named t.margin. The output of that program follows.

newVal = 60,185,371.40
WORLD TOTALPROD TOTALCHANNEL Q1.96 f.costs = 1,298,474.00 f.sales = 61,483,840.00
newVal = 32,563,900.67
WORLD AUDIODIV TOTALCHANNEL Q1.96 f.costs = 664,226.90 f.sales = 33,228,130.00
newVal = 5,917,626.67
WORLD PORTAUDIO TOTALCHANNEL Q1.96 f.costs = 97,976.04 f.sales = 6,015,603.00
newVal = 2,729,005.43
WORLD PORTCD TOTALCHANNEL Q1.96 f.costs = 34,301.53 f.sales = 2,763,307.00
newVal = 1,777,278.95
WORLD PORTST TOTALCHANNEL Q1.96 f.costs = 25,160.72 f.sales = 1,802,440.00
newVal = 70,311,762.13
WORLD TOTALPROD TOTALCHANNEL Q2.96 f.costs = 1,504,051.00 f.sales = 71,815,820.00
newVal = 38,053,495.70
WORLD AUDIODIV TOTALCHANNEL Q2.96 f.costs = 768,788.10 f.sales = 38,822,280.00
newVal = 6,965,032.86
WORLD PORTAUDIO TOTALCHANNEL Q2.96 f.costs = 114,558.20 f.sales = 7,079,591.00
newVal = 3,156,191.20
WORLD PORTCD TOTALCHANNEL Q2.96 f.costs = 39,256.88 f.sales = 3,195,448.00
newVal = 2,124,963.02
WORLD PORTST TOTALCHANNEL Q2.96 f.costs = 29,780.54 f.sales = 2,154,744.00
newVal = 64,133,838.86
WORLD TOTALPROD TOTALCHANNEL Q3.96 f.costs = 1,350,733.00 f.sales = 65,484,570.00
newVal = 34,697,467.06
WORLD AUDIODIV TOTALCHANNEL Q3.96 f.costs = 691,887.10 f.sales = 35,389,360.00
newVal = 6,371,997.63
WORLD PORTAUDIO TOTALCHANNEL Q3.96 f.costs = 103,203.70 f.sales = 6,475,202.00
newVal = 2,878,166.40
WORLD PORTCD TOTALCHANNEL Q3.96 f.costs = 35,358.18 f.sales = 2,913,525.00
newVal = 1,975,270.68
WORLD PORTST TOTALCHANNEL Q3.96 f.costs = 27,339.77 f.sales = 2,002,611.00
newVal = 78,317,681.06
WORLD TOTALPROD TOTALCHANNEL Q4.96 f.costs = 1,618,915.00 f.sales = 79,936,590.00
newVal = 42,222,265.94
WORLD AUDIODIV TOTALCHANNEL Q4.96 f.costs = 826,923.40 f.sales = 43,049,190.00
newVal = 7,765,539.34
WORLD PORTAUDIO TOTALCHANNEL Q4.96 f.costs = 123,269.50 f.sales = 7,888,809.00
newVal = 3,480,184.35
WORLD PORTCD TOTALCHANNEL Q4.96 f.costs = 41,998.90 f.sales = 3,522,183.00
newVal = 2,456,168.00
WORLD PORTST TOTALCHANNEL Q4.96 f.costs = 33,357.19 f.sales = 2,489,525.00
newVal = 61,038,695.03
WORLD TOTALPROD TOTALCHANNEL Q1.97 f.costs = 1,423,963.00 f.sales = 62,462,660.00
newVal = 32,988,187.65
WORLD AUDIODIV TOTALCHANNEL Q1.97 f.costs = 679,477.80 f.sales = 33,667,660.00
newVal = 5,905,060.56
WORLD PORTAUDIO TOTALCHANNEL Q1.97 f.costs = 158,854.40 f.sales = 6,063,915.00
newVal = 2,740,622.56
WORLD PORTCD TOTALCHANNEL Q1.97 f.costs = 53,144.41 f.sales = 2,793,767.00
newVal = 1,768,078.14
WORLD PORTST TOTALCHANNEL Q1.97 f.costs = 40,784.62 f.sales = 1,808,863.00
newVal = 45,856,698.46
WORLD TOTALPROD TOTALCHANNEL Q2.97 f.costs = 1,070,465.00 f.sales = 46,927,160.00
newVal = 24,786,276.35
WORLD AUDIODIV TOTALCHANNEL Q2.97 f.costs = 512,435.60 f.sales = 25,298,710.00
newVal = 4,477,797.64
WORLD PORTAUDIO TOTALCHANNEL Q2.97 f.costs = 118,791.70 f.sales = 4,596,590.00
newVal = 2,041,591.56
WORLD PORTCD TOTALCHANNEL Q2.97 f.costs = 39,287.77 f.sales = 2,080,879.00
newVal = 1,369,677.57
WORLD PORTST TOTALCHANNEL Q2.97 f.costs = 30,038.08 f.sales = 1,399,716.00

Example 10-165 An ASSIGN Trigger on a Formula

The way Oracle OLAP handles assigning values to a formula varies depending on whether or not the formula has an Assign trigger as part of its definition.

Assume your analytic workspace contains objects with the following definitions and values.

DEFINE geog.d DIMENSION TEXT
DEFINE time.d DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <time.d geog.d>
DEFINE f_modified_sales FORMULA DECIMAL <time.d geog.d>
  EQ sales+20

A report of sales, shows the base values.

-------------------SALES-------------------
               ------------------TIME.D-------------------
GEOG.D          Jan2004    Feb2004    Mar2004      2004
-------------- ---------- ---------- ---------- ----------
Boston               4.00       4.66       5.91       NA
Medford              4.37       5.80       4.45       NA
San Diego            4.97       5.95       4.75       NA
Sunnydale            5.85       5.26       4.08       NA

A report of f_modified_sales formula displays the following report that contains the values computed by the formula.

-------------F_MODIFIED_SALES--------------
               ------------------TIME.D-------------------
GEOG.D          Jan2004    Feb2004    Mar2004      2004
-------------- ---------- ---------- ---------- ----------
Boston              24.00      24.66      25.91         NA
Medford             24.37      25.80      24.45         NA
San Diego           24.97      25.95      24.75         NA
Sunnydale           25.85      25.26      24.08         NA

The f_modified_sales formula does not presently have an Assign trigger on it. Consequently, as illustrated in the following code, any attempt to assign values to f_modified_sales results in an error.

f_modified_sales = 3
ORA-34142: You cannot assign values to a FORMULA.

To create an Assign trigger on f_modified_sales take the following steps:

Define the trigger program

DEFINE TRIGGER_ASSIGN_MODIFIED_SALES PROGRAM
PROGRAM
ARGUMENT datavalue NUMBER
SHOW 'description of triggering object = '
DESCRIBE &TRIGGER(NAME)
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('value being assigned = ' datavalue)
SHOW '  '
END

Add the Assign trigger to the definition of the formula using the following statements.

CONSIDER f_modified_sales
TRIGGER ASSIGN trigger_assign_modified_sales

Issuing a FULLDSC f_modified_sales statement displays the new complete definition for f_modified_sales.

DEFINE F_MODIFIED_SALES FORMULA DECIMAL <TIME.D GEOG.D>
TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
EQ sales+20

Now when you issue the following statement to assign a value to f_modified_sales, an error does not occur. Instead, the trigger_assign_modified_sales trigger program executes 16 times, once for each dimension value of sales.

f_modified_sales = 3
 
description of triggering object = 
DEFINE F_MODIFIED_SALES FORMULA DECIMAL <TIME.D GEOG.D>
TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
EQ sales+20
calltype = TRIGGER
triggering event = ASSIGN
value being assigned = 3.00
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
... 
description of triggering object = 
...

However, as issuing a REPORT statement for f_modified_sales illustrates, the values calculated by a simple execution of the formula have not changed.

report f_modified_sales

               -------------F_MODIFIED_SALES--------------
               ------------------TIME.D-------------------
GEOG.D          Jan2004    Feb2004    Mar2004      2004
-------------- ---------- ---------- ---------- ----------
Boston              24.00      24.66      25.91         NA
Medford             24.37      25.80      24.45         NA
San Diego           24.97      25.95      24.75         NA
Sunnydale           25.85      25.26      24.08         NA