Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
910 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

688 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