Trigger Examples (note: MS Word does not preserve tabs when converting a document to html; please excuse the messiness of the code).

 

  1. How to call an alert from within a trigger

    2.  

    --------------------------------

     

    declare

    alert_result number;

     

    begin

    alert_result := show_alert('insert your alert name here');

    if (alert_result = alert_button1) then

    {insert code here};

    elsif (alert_result = alert_button2) then

    {insert code here};

    else

    {insert code here}

    end if;

    end;

     

    ---------------------------------

     

  2. Using A Function to Populate Pull-down Lists Rather than a List of Values (this is a more elegant solution to the standard LOV). Note: the following function assumes a single code table for multiple codes. How to implement this will be discussed in class and a more detailed example will be given in the lab next week or the week after. You can call this function either from within a procedure or a trigger. For example, if you have many list items in your form, you may want to write a procedure that initializes all list items. This procedure in turn would be called from a when-new-form-instance trigger.

 

-----------------------------------------------------

-- Function Init_Codes_Multi

-- Written by: Andreas M. Olligschlaeger

-- Date Written: October 1996

-- Last Modified:

-- Purpose: Populates a list item from a code table

-- with multiple code fields. Returns 0 on

-- success, -1 on failure. Parameter V_TABLE

-- is the name of the table containing the

-- codes, V_KEY is the primary key of the

-- code table, V_ITEM is the list item in a

-- block that is to be populated, and V_FIELD

-- is the type of code desired. Note that this

-- function assumes that the code table has a

-- column named description (containing the

-- description of the code) as well a column

-- called FIELD_CODE, which contains the type

-- of code for this record. The function also

-- assumes that have already created an alert

-- called DATABASE_ERROR. You can substitute

-- your own if you wish.

-----------------------------------------------------

 

FUNCTION INIT_CODES_MULTI(V_TABLE IN VARCHAR2, V_KEY IN VARCHAR2,

V_ITEM IN VARCHAR2, V_FIELD IN VARCHAR2)

RETURN INTEGER IS

 

GROUP_ID RECORDGROUP;

LIST_ID ITEM;

V_TEST NUMBER;

 

BEGIN

GROUP_ID := CREATE_GROUP_FROM_QUERY('VECTOR',

'SELECT DESCRIPTION, TO_CHAR('||V_KEY||') FROM '||V_TABLE||

' WHERE FIELD_CODE = '''||V_FIELD||''' ORDER BY DESCRIPTION');

IF ID_NULL(GROUP_ID) THEN

V_TEST := SHOW_ALERT('DATABASE_ERROR');

RETURN -1;

END IF;

V_TEST := POPULATE_GROUP('VECTOR');

LIST_ID := FIND_ITEM(V_ITEM);

IF ID_NULL(LIST_ID) THEN

V_TEST := SHOW_ALERT('DATABASE_ERROR');

RETURN -1;

END IF;

POPULATE_LIST(LIST_ID,GROUP_ID);

DELETE_GROUP(GROUP_ID);

RETURN 0;

END;

 

Return to Main Page