FAQ
Hi all, I've got a table with two columns (say column A and column B).
The B data type is date and its default value is CURRENT_TIMESTAMP.
When I make an update on col A I would like to update also B with a
new current_timestamp value, so I created a trigger that fires after
an update on A but I obtain an error, in fact when the trigger tries
to update col B it says that it's impossible because the table is
updating col A.
How can I solve this problem?

Thanks, Simone Saravalli

Search Discussions

  • Giovanni Cuccu at May 5, 2006 at 12:33 pm
    Ciao Simone,

    you can update the column values using Pl/SQL code not a SQL command
    here is an example very close to your requirements:

    create or replace trigger NtfRcpt_lastUpdate
    before insert or update on Notifications_Recipients
    for each row
    declare

    local variables here
    begin
    :new.Last_Update:=sysdate;
    end check_enabled;
    /
    Hope it helps,
    Giovanni
    On 5/5/06, Simone Saravalli wrote:
    Hi all, I've got a table with two columns (say column A and column B).
    The B data type is date and its default value is CURRENT_TIMESTAMP.
    When I make an update on col A I would like to update also B with a
    new current_timestamp value, so I created a trigger that fires after
    an update on A but I obtain an error, in fact when the trigger tries
    to update col B it says that it's impossible because the table is
    updating col A.
    How can I solve this problem?

    Thanks, Simone Saravalli
    --
    http://www.freelists.org/webpage/oracle-l

    --
    --------------------------------------------------------------------
    Another free oracle resource profiler
    http://sourceforge.net/projects/oraresprof/
    Now version 0.9
    --
    http://www.freelists.org/webpage/oracle-l
  • David Sharples at May 5, 2006 at 12:33 pm
    full code, table descriptions and probably most useful would be the error
    please
    On 05/05/06, Simone Saravalli wrote:

    Hi all, I've got a table with two columns (say column A and column B).
    The B data type is date and its default value is CURRENT_TIMESTAMP.
    When I make an update on col A I would like to update also B with a
    new current_timestamp value

    --
    http://www.freelists.org/webpage/oracle-l
  • Simone Saravalli at May 5, 2006 at 12:42 pm
    Forwarded message ----------
    From: Simone Saravalli
    Date: 5-mag-2006 14.40
    Subject: Re: updating problems
    To: Ghassan Salem

    2006/5/5, Ghassan Salem :
    Simone,
    Can you post the code of your trigger?
    Yes, this is an example of the table I want to update:

    create table tab (

    a number not null,
    b date default current_timestamp
    );

    and then the trigger

    create or replace trigger my_trig

    after update of a on tab for each row
    begin

    update tab set b = current_timestamp where a=:new.a;
    end;
    /

    Now I've written:

    create or replace trigger my_trig

    after update of a on tab for each row
    begin

    update tab set :new.b = current_timestamp where a = :new.a;
    end;
    /

    but the trigger gets some compilation errors. Is it correct?

    Thanks, Simone Saravalli
  • Igor Neyman at May 5, 2006 at 12:48 pm
    First, you should do this in "before" trigger.
    Second, you don't need "update" statement in the trigger (just do an
    "assignment").


    create or replace trigger my_trig

    before update of a on tab for each row
    begin

    :new.b = current_timestamp;
    end;
    /

    And third, read some oracle docs.

    Igor

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Simone Saravalli
    Sent: Friday, May 05, 2006 8:42 AM
    To: oracle-l
    Subject: Fwd: updating problems

    Forwarded message ----------
    From: Simone Saravalli
    Date: 5-mag-2006 14.40
    Subject: Re: updating problems
    To: Ghassan Salem

    2006/5/5, Ghassan Salem :
    Simone,
    Can you post the code of your trigger?
    Yes, this is an example of the table I want to update:

    create table tab (

    a number not null,
    b date default current_timestamp
    );

    and then the trigger

    create or replace trigger my_trig

    after update of a on tab for each row
    begin

    update tab set b = current_timestamp where a=:new.a;
    end;
    /

    Now I've written:

    create or replace trigger my_trig

    after update of a on tab for each row
    begin

    update tab set :new.b = current_timestamp where a = :new.a;
    end;
    /

    but the trigger gets some compilation errors. Is it correct?

    Thanks, Simone Saravalli

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 5, '06 at 12:14p
activeMay 5, '06 at 12:48p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase