| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
The TRIGGER command associates a previously-created program to an object and identifies the object event that automatically executes the program; or a disassociates a trigger program from the object.
In order 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 command to make it the current definition.
|
See also: "Trigger Programs" for a general discussion, and the following statements for more specific information:
|
Syntax
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.
Arguments
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 24-5, "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 afterr 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 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.
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.
Notes
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.
Oracle OLAP does not support recursive triggers. You must set the USETRIGGERS option to NO before you issue the same DML statement within a trigger program that triggered the program itself. For example, assume that you have written a program named TRIGGER_MAINTAIN_ADD that is triggered by MAINTAIN ADD statements. Within the TRIGGER_MAINTAIN_ADD program, you must set the USETRIGGERS option to NO before you issue a MAINTAIN statement.
Trigger programs have certain characteristics depending on the statement that triggers them. Some trigger programs execute before the triggering statement executes; some after. Oracle OLAP passes arguments to programs triggered by some statements, but not others. Oracle OLAP does not change dimension status before most trigger programs execute, but does change dimension status before some MAINTAIN statements trigger program execution. In most cases, you can give a trigger program any name that you choose, but some events require a program with a specific name.
Table 24-1, "Trigger Program Characteristics " lists the OLAP DML statements that trigger programs, the required name of the program (if any), whether or not Oracle OLAP uses values returned by the program, and whether or not Oracle OLAP passes arguments to the program.
Keep the following points in mind when designing trigger programs:
Triggers that execute before the DML statement—For trigger programs that execute before the triggering OLAP DML statement executes, you can define the trigger program as a user-defined function that returns a BOOLEAN value. The value returned by the program determines whether or not Oracle OLAP executes the statement that triggered the execution of the trigger program. When the program returns FALSE, Oracle OLAP does not execute the triggering statement; when it returns TRUE or NA, the triggering statement executes.
Arguments passed to trigger programs—Oracle OLAP passes arguments to some trigger programs. These programs are identified in Table 24-1, "Trigger Program Characteristics ". Descriptions of these arguments are provided in Table 24-2, "Arguments Passed to Trigger Programs". Use the ARGUMENT command to declare these arguments in your program. Use VARIABLE to define program variables for the values. Use the WKSDATA function to retrieve the data type of an argument with a WORKSHEET data type.
Assign trigger programs—Oracle OLAP executes a program triggered by an Assign event each time it 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. With each execution, the value to be assigned is passed as argument1 to the Assign trigger program. (See Table 24-2, "Arguments Passed to Trigger Programs" for more information and Example 24-8, "An ASSIGN Trigger on a Variable" for an example.) Within the Assign trigger program, you can use aTRIGGERASSIGN command to assign a different value than that specified by the assignment statement that triggered the execution of the Assign trigger program.
You can only assign values to a formula when the formula has an Assign trigger defined for it. When you assign a value to a formula with an Assign event, Oracle OLAP executes the trigger program for the event for assigned value and passes the assigned value to the trigger program. The Assign trigger does not change the definition of the formula itself. See Example 24-10, "An ASSIGN Trigger on a Formula" for an example of an Assign trigger on a formula.
Maintain trigger programs and dimension status —In some cases, Oracle OLAP changes the status of the dimension being maintained when a Maintain event triggers the execution of a program. See Table 24-3, "How Programs Triggered by Maintain Events Effect Dimension Status" for details.
Maintain triggers and dimension surrogates—Maintain triggers for dimension surrogates are different than Maintain triggers for other objects. You can only successfully issue a MAINTAIN statement against a dimension surrogate, when the dimension surrogate has a Maintain trigger. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error. Also, for Maintain Add and Maintain Merge triggers, whether or not an argument is passed to the program depends on the object on which the trigger is defined:
For dimension surrogates with a Maintain trigger, Oracle OLAP executes the trigger program one time for each value added or merged and passes that value into the program.
For other objects with a Maintain trigger, Oracle OLAP executes the trigger program only once after the MAINTAIN statement executes and no values are passed into the program
Table 24-1 Trigger Program Characteristics
| Triggering Statement (event) | Program Name | Return Values | Passed Arguments |
|---|---|---|---|
| = command (SET) | No required name | No | Yes |
| AW command |
TRIGGER_AW |
No | No |
| DEFINE |
TRIGGER_DEFINE |
No | No |
| MAINTAIN ADD |
No required name | No | No |
| MAINTAIN DELETE (not ALL) | No required name | Yes | No |
| MAINTAIN DELETE ALL | No required name | Yes | No |
| MAINTAIN MERGE |
No required name | No | No |
| MAINTAIN MOVE |
No required name | Yes | Yes |
| MAINTAIN RENAME |
No required name | Yes | Yes |
| PROPERTY |
No required name | Yes | Yes |
| UPDATE (Update AW) | TRIGGER_AFTER_UPDATE |
No | No |
| UPDATE (Update AW) | TRIGGER_BEFORE_UPDATE |
Yes | No |
| UPDATE (Update Multi) | No required name | No | No |
Table 24-2 Arguments Passed to Trigger Programs
| Event | Argument1 | Argument2 |
|---|---|---|
| Property | When the PROPERTY statement is assigning a property to an object, the name of the property. When the PROPERTY statement is deleting one or more properties, the literal DELETE. (TEXT data type) |
When the value of argument1 is DELETE, the name of the property or the literal ALL. In all other cases, the name of the property. (WORKSHEET data type) |
| Assignment | The value that you want to assign. When you know the data type of the object to which the value is assigned, specify that data type for the argument. When you do not know the actual data type, specify WORKSHEET as the data type of the argument. |
None. Oracle OLAP passes only one argument to the program. |
| Maintain Add | (Dimension surrogates only) The value added. (WORKSHEET data type) | |
| Maintain Rename | The dimension value that you want to rename. (TEXT data type) |
The new name of the dimension member. (WORKSHEET data type) |
| Maintain Merge | (Dimension surrogates only) The value merged. (WORKSHEET data type) | |
| Maintain Move | The position of the dimension value that you want to move. (TEXT data type) |
The literal BEFORE or AFTER. (WORKSHEET data type) |
Table 24-3 How Programs Triggered by Maintain Events Effect Dimension Status
| Event Subevent | Dimension Status Before Program Execution |
|---|---|
| Maintain Add | Status set to dimension values just added. |
| Maintain Delete | Status set to dimension values about to be deleted. |
| Maintain Delete All | Current status is not changed. |
| Maintain Merge | Status set to dimension values just merged. |
| Maintain Move | Status set to dimension values about to be moved. |
| Maintain Rename | Current status is not changed. |
Examples
Example 24-4 Creating Triggers
Assume that your analytic workspace contains a TEXT dimension named city and that you want to create programs that will 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 rigger_maintain_move_city
Example 24-5 Describing Triggers
Assume that you have created the triggers for city as described in Example 24-4, "Creating Triggers" . Later you want to see the description of the triggers, to do so you cannot merely issue a DESCRIBE statement for your analytic workspace. Instead, you must issue a FULLDSC statement.
DEFINE CITY DIMENSION TEXT
TRIGGER MAINTAIN RIGGER_MAINTAIN_MOVE_CITY -
PROPERTY TRIGGER_PROPERTY_CITY
DEFINE TRIGGER_MAINTAIN_MOVE_CITY PROGRAM BOOLEAN
DEFINE TRIGGER_PROPERTY_CITY PROGRAM BOOLEAN
Example 24-6 Deleting Triggers
Assume that you have created the triggers described in Example 24-4, "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 need to issue the following statement.
DELETE TRIGGER_MAINTAIN_MOVE_CITY
Example 24-7 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 command 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 24-8 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 values shown below. The percent_sales variable is empty.
GEOG SALES -------------- ---------- North America 0.59 Europe 9.35 Asia NA
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 will execute each time you assign values to percent_sales.
DEFINE TRIGGER_EQ PROGRAM BOOLEAN
PROGRAM
ARGUMENT datavalue WORKSHEET
show 'description of triggering object = '
DESCRIBE &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 the TRIGGER command. Remember to first issue a CONSIDER command to make the definition for the e 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 argument 2 = 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 argument 2 = description of triggering object = DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG> TRIGGER ASSIGN TRIGGER_EQ calltype = TRIGGER triggering event = ASSIGN triggering subevent = value being assigned = argument 2 =
|
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 24-9 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 will execute 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 24-10 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 SALES VARIABLE DECIMAL <GEOG.D> DEFINE F_MODIFIED_SALES FORMULA DECIMAL <GEOG.D> EQ sales+20
A report of f_modified_sales formula displays the following report that contains the values computed by the formula.
REPORT f_modified_sales
-------------F_MODIFIED_SALES--------------
------------------GEOG.D-------------------
TIME.D Boston Medford San Diego Sunnydale
-------- ---------- ---------- ---------- ----------
Jan76 0.00 1,000.00 2,000.00 3,000.00
Feb76 1,000.00 3,000.00 5,000.00 7,000.00
Mar76 2,000.00 5,000.00 8,000.00 11,000.00
76Q1 NA NA NA 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 <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 program executes 4 times, once for each dimension value of sales.
f_modified_sales = 3 description of triggering object = DEFINE F_MODIFIED_SALES FORMULA DECIMAL <GEOG.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES EQ sales-1000 calltype = TRIGGER triggering event = ASSIGN value being assigned = 3.00 description of triggering object = DEFINE F_MODIFIED_SALES FORMULA DECIMAL <GEOG.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES EQ sales-1000 calltype = TRIGGER triggering event = ASSIGN value being assigned = 3.00 description of triggering object = DEFINE F_MODIFIED_SALES FORMULA DECIMAL <GEOG.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES EQ sales-1000 calltype = TRIGGER triggering event = ASSIGN value being assigned = 3.00 description of triggering object = DEFINE F_MODIFIED_SALES FORMULA DECIMAL <GEOG.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES EQ sales-1000 calltype = TRIGGER triggering event = ASSIGN value being assigned = 3.00
However, as issuing a REPORT statement for f_modified_salesd illustrates, the values calculated by a simple execution of the formula have not changed.
REPORT f_modified_sales GEOG.D F_MODIFIED_SALES ------------ ----------------------------------- Boston 30.00 Medford 32.21 San Diego 33.03 Sunnydale 38.32
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|