Solved

Create an Oracle Function within Report builder.  I need to create a before trigger and then a after trigger with in rdf

Posted on 2006-07-21
3
906 Views
Last Modified: 2013-12-12
I need to create a before trigger that looks at a table to see if a row exists if it does exists then it needs to check a field to see if the field is a valid code.

If a valid code is found then the rdf / report can run.  If not a valid code then the report should fail.


after trigger if a valid code is found then the report runs then after the report runs then a after trigger is fired that
changes the valid code to a invalid code.

How can I do this within the report.
0
Comment
Question by:ValeneSue
3 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 500 total points
ID: 17165941
if there are no possibilities to make the checks before report is started, you could place a trigger like this in the BEFORE PARAMETER FORM trigger (the trigger is the first one to be executed when report starts):

DECLARE
    v_cnt NUMBER;
BEGIN
    --
    -- here we check if row exists:
    --
    SELECT COUNT(*)
    INTO v_cnt
    FROM <your_table>
    WHERE <your_conditions>;
    --
    IF v_cnt > 0 THEN
        -- data found; do nothing:
        NULL;
    ELSE
        -- no data found; raise error and exit report:
       SRW.MESSAGE(100, 'No data found ... exit report ...');
       RAISE SRW.PROGRAM_ABORT;
    END IF;
END;

in the AFTER REPPORT TRIGGER place a trigger like this:

BEGIN
    UPDATE <your_table> -- OR INSERT/DELETE ....
    SET <your_column> = '<your_value>'
    WHERE <your_condition>;
    --
    COMMIT;
    --
END;

good luck!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question