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.
TRIGGER {event-name [program-name] }... | {DELETE event-name}... | DELETE ALL
where event-name is one of the following:
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.
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.
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.
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.
Specifies that Oracle OLAP executes the specified program in response to an 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 an object assigning values.
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.The name of the trigger program. When omitted for an event, the event does not trigger an action.
Deletes the triggers for the specified object events. Oracle OLAP disassociates the trigger program from the specified object event.
Deletes all of the triggers for the specified object. Oracle OLAP disassociates the trigger program from all events for object.
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.
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 an 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 thetrigger_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