Hello - I've been working on a trigger that I want to use to update a field on a table(tisfc951) if that same field on another table(tdsls040) is changed. There isn't a direct link between these tables so I have to query two other tables to get the order number I need to get the correct record(s) updated in my final table(tisfc951). There can be more than one record in my final table to be updated. I'm thinking I need to use a cursor but I'm having difficulty because to populate the cursor I need two of the :new.fields and I'm seeing cursors need to be created outside of the BEGIN/END right? I've posted the non-cursor code I've been working with. I hope this explanation is understandable - any help would be great!!

NikkiZ



CREATE OR REPLACE TRIGGER "BAAN"."UPDATE_BC_JOBS" AFTER
UPDATE OF "T$EONO" ON "BAAN"."TTDSLS040795" FOR EACH ROW
DECLARE

    ws_eono VARCHAR2(30);
    ws_cprj VARCHAR2(6);
    ws_pdno NUMBER(6);
    ws_orno NUMBER(6);


BEGIN

    ws_eono := :new.t$eono;
    ws_orno := :new.t$orno;

    SELECT Lines.t$cprj, Pdnohead.t$pdno
    INTO ws_cprj, ws_pdno
    FROM ttdsls041795 Lines, ttisfc001795 Pdnohead
    WHERE Lines.t$orno = :new.t$orno
    AND Lines.t$cprj = Pdnohead.t$cprj;

LOOP
      UPDATE ttisfc951795
    SET t$eono = ws_eono
    WHERE t$pdno = ws_pdno;
END LOOP;

END;

I think you can declare parameterized cursor in declaration block and open it up in trigger block. Pass :new values as parameters to it and loop thru. i haven't checked the following code for syntax errors since oracle is not available with me now. Hope this would of help to you.

CREATE OR REPLACE TRIGGER "BAAN"."UPDATE_BC_JOBS" AFTER
UPDATE OF "T$EONO" ON "BAAN"."TTDSLS040795" FOR EACH ROW
DECLARE

    ws_eono VARCHAR2(30);
    ws_cprj VARCHAR2(6);
    ws_pdno NUMBER(6);
    ws_orno NUMBER(6);
    CURSOR C(Ordno lines.t$orno%type) IS SELECT Lines.t$cprj, Pdnohead.t$pdno
              FROM ttdsls041795 Lines, ttisfc001795 Pdnohead
              WHERE Lines.t$orno = ordno
              AND Lines.t$cprj = Pdnohead.t$cprj;

BEGIN
    ws_eono := :new.t$eono;
    OPEN C(:new.t$orno);
    LOOP
       EXIT WHEN SQL%NOTFOUND;
       FETCH C INTO ws_pdno;
       UPDATE ttisfc951795
       SET t$eono = ws_eono
       WHERE t$pdno = ws_pdno;
    END LOOP;
END;