Re: After Update Trigger

dwgabel_at_telus.net wrote:
> Hi, I'm kind of new to oracle (PLSql) and I'm trying to accomplish this
>
> a row in a table is created (REQUEST table) the column is
> Null.(P_AND_I_LEAD_ID).
> later in the workflow the P and I lead is assigned and their ID is
> placed in the column, my goal is to have a trigger to add the sysdate
> to another column in the same table (MY_P_AND_I_ASSINGED_DATE;
>
> I'm just wanting to confirm that this statement won't update ALL of the
> rows.
>
> CREATE OR REPLACE TRIGGER p_and_i_assigned_date_trigger
> AFTER UPDATE OF p_and_i_lead_id
> ON request
> FOR EACH ROW
> WHEN (old.p_and_i_lead_id = NULL )
> BEGIN
> UPDATE request SET my_p_and_i_assigned_date = (select sysdate from
> dual);
> END;
>
> Anyone mind helpling me ?
>
> DG
>

Look at using the ":new" designator in your trigger code. For instance, you can do something similar to the following:

CREATE OR REPLACE TRIGGER p_and_i_assigned_date_trigger

    AFTER UPDATE OF p_and_i_lead_id
    ON request
    FOR EACH ROW
    WHEN (old.p_and_i_lead_id = NULL )
DECLARE
    curr_date_time DATE;
BEGIN
    SELECT sysdate INTO curr_date_time FROM dual;     :new.my_p_and_i_assigned_date := curr_date_time; END;
/

Something like that off the top of my head....

Have a look in the Oracle docs for the :new and :old references. Look in the PL/SQL documentation.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown