Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

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

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
ValeneSue
Asked:
ValeneSue
1 Solution
 
jwahlCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now