Characteristics of Trigger Programs

---

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: 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: Trigger Program Characteristics. Descriptions of these arguments are provided in Table: Arguments Passed to Trigger Programs. Use the ARGUMENT statement 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: Arguments Passed to Trigger Programs for more information and Example: An ASSIGN Trigger on a Variable for an example.) Within the Assign trigger program, you can use aTRIGGERASSIGN statement 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: 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: 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