| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
Within a program triggered by an Assign event for an object, assigns a value to the triggering object.
Note:
The USETRIGGERS option must be set to its default value of
TRUE for a TRIGGERASSIGN to executeSee:
"Trigger Programs".Data Type
The data type of the object to which Oracle OLAP assigns the value.
Syntax
TRIGGERASSIGN value
Arguments
The value that you want assigned.
Examples
Example 10-165 Assigning an Alternative Value using an Assign Trigger
Assume that you have objects with the following descriptions in your analytic workspace.
DEFINE GEOG.D DIMENSION TEXT DEFINE TIME.D DIMENSION TEXT DEFINE TIME.PARENTREL RELATION TIME.D <TIME.D> DEFINE SALES VARIABLE DECIMAL <GEOG.D TIME.D> DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
Assume also that you have populated the sales variable with the values shown in the following report, but that you have not yet populated the modified_sales variable.
-----------------------SALES-----------------------
----------------------GEOG.D-----------------------
TIME.D Boston Medford San Diego Sunnydale
------------ ------------ ------------ ------------ ------------
Jan76 1,000.00 2,000.00 3,000.00 4,000.00
Feb76 2,000.00 4,000.00 6,000.00 8,000.00
Mar76 3,000.00 6,000.00 9,000.00 12,000.00
76Q1 NA NA NA NA
Now you want to assign values to the modified_sales variable using various expressions, however, you want to ensure that the values never are less than or equal to 1,000. You can assure this processing by taking the following steps:
Create the following program that checks for values less than or equal to 1000 condition.
DEFINE TRIGGER_ASSIGN_MODIFIED_SALES PROGRAM
PROGRAM
ARGUMENT datavalue DECIMAL
IF datavalue LE 1000
THEN TRIGGERASSIGN 1000
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 passed to program = ' datavalue)
SHOW ' '
END
Issue the following statements to add an Assign trigger to the modified_sales variable. The trigger_assign_modified_sales program is the trigger program.
CONSIDER modified_sales TRIGGER ASSIGN trigger_assign_modified_sales
Assign values to modified_sales.
modified_sales = sales - 1000
This statement triggers the execution of the trigger_assign_modified_sales program for each value that Oracle OLAP assigns.
description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 0.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 1,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 2,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 3,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 1,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 3,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 5,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 7,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 2,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 5,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 8,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = 11,000.00 description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program = description of triggering object = DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D> TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES calltype = TRIGGER triggering event = ASSIGN triggering subevent = value passed to program =
The following report of modified_sales shows that all values are at least 1,000.
------------------MODIFIED_SALES-------------------
----------------------GEOG.D-----------------------
TIME.D Boston Medford San Diego Sunnydale
------------ ------------ ------------ ------------ ------------
Jan76 1,000.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
