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

The trigger will fail as you can't update the table you are updating in a trigger.
If you intend to set that column of the *current* record, you need to use
select sysdate into :new.p_and_i_assigned_date from dual; in a *before* update trigger (as you can't change anything after update).
BTW: on it's own your update will affect *all* records, by lack of a where clause.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA