Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: calling a procedure within a trigger

    Hi all,
    In order to create this trigger, i've created a function to calculate and return business days.
    ---------------------------------------------------------------------------
    FUNCTION CME.CASE_1 (start_date IN CME_ROUT.BFDATE%TYPE, end_date IN CME_ROUT.RETDATE%TYPE)
    ---------------------------------------------------------------------------

    then, i've created a view that calls the above function as follows
    ---------------------------------------------------------------------------
    VIEW CME.TESTCASE_1 (DOC, BUS) AS
    SELECT a.docket, SUM(CME.CASE_1(b.BFDATE, b.RETDATE)) AS BUS
    from CME.CME_MASTER a, CME.CME_ROUT b
    where a.masid = b.masid and
    a.closed is not null and
    bfdate is not null and
    b.retdate > b.bfdate
    group by a.docket
    ---------------------------------------------------------------------------

    Since i can't call the above view into a trigger, i'll need to create a procedure and in turn call that procedure into the trigger.

    This is what i need to do.
    i will need to compare if DOC from the view is equal to the DOC from CME_MASTER table, then if so i would need to put the BUS from the view into field5 in the CME_MASTER table.

    this is what i have for procedure, i'm not sure if this is correct nor how to call this procedure into the trigger and make it work.
    -------------------------------
    procedure CME.CREATE_PROC
    AS bus1 number;
    begin
    select b.bus
    into bus1 from CME.CME_MASTER a, CME.TESTCASE_1 b
    where a.doc = b.doc;
    end;
    ------------------------------

    Please help.
    Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure what you're really trying to achieve, but may be this will give you some ideas:
    Code:
    CREATE TRIGGER t
         NO CASCADE BEFORE UPDATE OF bus ON CME.CME_MASTER 
         REFERENCING NEW AS newm OLD AS oldm
         FOR EACH ROW
         BEGIN ATOMIC
            SET newm.field5 = coalesce(
                 (select doc from CME.TESTCASE_1 where bus = newm.bus), 
                 oldm.field5
            );
         END
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    I'm trying to create a trigger so that the value in field5 appears automatically in the application. the application is not capable of performing any calculations. therefore, i am trying to create a trigger where the users want business days to be calculated with some restrictions. That's why i have created the function and the view.

    TRIGGER calling PROCEDURE calling VIEW calling FUNCTION.
    I'm sure there is an easier way. ???

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That depends on what is going to trigger your trigger.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •