Trigger on column returns error 747
Hi guys
I'm creating a trigger on a table. When a column of this table is updated, if
the new value is terminated by 5 , I want to add it the value 2.
So, below are the trigger and the stored procedure it calls to do the job
When I update the table, the trigger is run and I get the following error
message
747: Table or column matches object referenced in triggering statement
The error comes because the column that is updated in the triggered action is
the same as the column that the triggering statement updates.
IS there a workaround?
Any idea?
Thanks
create trigger upd_wkscr update of prixd1a001 on wkscrreferencing old as pre new as post
for each row
(execute procedure update_wkscr (post.code,post.prixd1a001));
create procedure update_wkscr (lcode char(11),prix float)if ( mod (prix,5) = 0) then
if (mod (prix , 10) <>0) then
let prix = prix +2;
update wkscr set prixd1a001=prix where code=lcode;
end if;
end if;
end procedure
This will surely not work as it will end up in "never end loop"...
I would recommend to add another column (calc_prixd1a001) into the same table
to store your calculated values.
update wkscr set calc_prixd1a001=prix where code=lcode;
Thanks,
Dharmendra Sharma
> To: ids@iiug.org> From: mdia@accovia.com> Subject: Trigger on column returns
error 747 [8256]> Date: Thu, 18 Jan 2007 22:09:47 -0500> > > Hi guys > I'm
creating a trigger on a table. When a column of this table is updated, if >
the new value is terminated by 5 , I want to add it the value 2. > So, below
are the trigger and the stored procedure it calls to do the job > When I
update the table, the trigger is run and I get the following error > message >
> 747: Table or column matches object referenced in triggering statement > >The error comes because the column that is updated in the triggered action is
> the same as the column that the triggering statement updates. > > IS there a
workaround? > Any idea? > > Thanks > > create trigger upd_wkscr update of
prixd1a001 on wkscr > referencing old as pre new as post > for each row > >
(execute procedure update_wkscr (post.code,post.prixd1a001)); > > create
procedure update_wkscr (lcode char(11),prix float) > if ( mod (prix,5) = 0)
then > > if (mod (prix , 10) <>0) then > > let prix = prix +2; > > update
wkscr set prixd1a001=prix where code=lcode; > > end if; > end if; > end
procedure > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Try amazing new 3D maps
http://maps.live.com/?wip=51