Working with Event Triggers in BIP 11g

We can call database function from the bip reports using the Event Triggers facility in the Data model.
Note: the function return type should be BOOLEAN.

1. Create a package (EVT_TRIGGER_PKG) in the database and define one function (SHOW_DATA) with return type as BOOLEAN.

--Package Specification
CREATE OR REPLACE
PACKAGE EVT_TRIGGER_PKG AS
PARAM_1 NUMBER;
PARAM_2 VARCHAR2(100);

FUNCTION SHOW_DATA(CAT_ID NUMBER,CAT_NAME VARCHAR2)RETURN BOOLEAN;

END EVT_TRIGGER_PKG;

--Package Body
CREATE OR REPLACE
PACKAGE BODY EVT_TRIGGER_PKG AS

PARAM_1 NUMBER;
PARAM_2 VARCHAR2(100);

FUNCTION SHOW_DATA(CAT_ID NUMBER,CAT_NAME VARCHAR2)RETURN BOOLEAN AS
BEGIN
INSERT INTO CATEGORY VALUES(CAT_ID,CAT_NAME);
COMMIT;
RETURN TRUE;
END SHOW_DATA;

END EVT_TRIGGER_PKG;

Here show_data() function inserting a row in the table whenever it triggered from BIP.

2.Now come to Publisher ,create one datamodel with SQL datasets ,let say
SELECT * FROM CATEGORY
And give the Oracle DB Default Package as EVT_TRIGGER_PKG,then this package will be displayed in the Event Triggers Section in the BIP.
3.Create 2 parameters (PARAM_1,PARAM_2) input type as text.These parameters must be exist in the default package as Global Variables
4.Create a BeforeDataTrigger by calling EVT_TRIGGER_PKG.SHOW_DATA(:PARAM_1,:PARAM_2)


5. Save the Datamodel and view the xml by giving the input parameters
6. In xml output you can see the input values which are stored in Category table through the function and fetched to BIP xml (report).Since it is a Before Data Trigger,
so here the Before Data trigger execution process is