Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

TRIGGERASSIGN

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 execute

Data Type

The data type of the object to which Oracle OLAP assigns the value.

Syntax

TRIGGERASSIGN value

Parameters

value

The value that you want assigned.

Examples

Example 10-166 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:

  1. 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
     
    
  2. 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
    
  3. Assign values to modified_sales.

    modified_sales = sales - 1000
    
  4. 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 = 
    
  5. 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