Re: Delete trigger
Noem' Nieto wrote:
> I've tried run this trigger:
>
> create trigger "informix".deleteoperations delete on
> "informix".operaciones referencing old as pre for each row (
> execute procedure
> "informix".modifica_saldo_operaciones(pre.cuentaorigen ,pre.cuentanum
> ,pre.cuentadc ,pre.idproducto ,pre.idoperacion,(pre.importe *
> -1.0000000000000000 ) ,pre.idtipoorden ));
>
>
> where modifica_saldo_operaciones code is:
>
> create procedure modifica_saldo_operaciones(ctaorigen varchar(3),
> ctanum varchar(9),ctadc varchar(2),aidproducto varchar(12),
> aidoperacion int,aimp float,aidtipoorden varchar(10))
> define impsigno float;> if (aidproducto=='0') then
> select importe_segun_clase(clase,signo, aimp) into impsigno
> from tiposordenes
> where idtipoorden=aidtipoorden;
>
> update operaciones
> set saldo=saldo+impsigno
> where cuentaorigen=ctaorigen
> and cuentanum=ctanum
> and cuentadc=ctadc
> and idproducto=aidproducto
> and idoperacion>aidoperacion;
> end if
> end procedure
>
>
> When I run a delete I obtain the next error message:
>
>
> SQL Error (-747): Table or column matches object referenced in
> triggering statement.
>
> Can you help me, please? Thanks in advance.
-747 Table or column matches object referenced in triggering
statement.
This error is returned when a triggered SQL statement acts on the
triggering table, or when both statements are updates, and the column
that is updated in the triggered action is the same as the column that
the triggering statement updates.
Apparently you are updating the same table where you are deleting...
This could lead to conflicts or never ending loops...
Regards.
Fernando Nunes <spam@domus.online.pt> wrote in message news:<3EED9A92.3050104@domus.online.pt>...
> Noem' Nieto wrote:
> > I've tried run this trigger:
> >
> > create trigger "informix".deleteoperations delete on
> > "informix".operaciones referencing old as pre for each row (
> > execute procedure
> > "informix".modifica_saldo_operaciones(pre.cuentaorigen ,pre.cuentanum
> > ,pre.cuentadc ,pre.idproducto ,pre.idoperacion,(pre.importe *
> > -1.0000000000000000 ) ,pre.idtipoorden ));
> >
> >
> > where modifica_saldo_operaciones code is:
> >
> > create procedure modifica_saldo_operaciones(ctaorigen varchar(3),
> > ctanum varchar(9),ctadc varchar(2),aidproducto varchar(12),
> > aidoperacion int,aimp float,aidtipoorden varchar(10))
> > define impsigno float;> > if (aidproducto=='0') then
> > select importe_segun_clase(clase,signo, aimp) into impsigno
> > from tiposordenes
> > where idtipoorden=aidtipoorden;
> >
> > update operaciones
> > set saldo=saldo+impsigno
> > where cuentaorigen=ctaorigen
> > and cuentanum=ctanum
> > and cuentadc=ctadc
> > and idproducto=aidproducto
> > and idoperacion>aidoperacion;
> > end if
> > end procedure
> >
> >
> > When I run a delete I obtain the next error message:
> >
> >
> > SQL Error (-747): Table or column matches object referenced in
> > triggering statement.
> >
> > Can you help me, please? Thanks in advance.
>
>
> -747 Table or column matches object referenced in triggering
> statement.
>
> This error is returned when a triggered SQL statement acts on the
> triggering table, or when both statements are updates, and the column
> that is updated in the triggered action is the same as the column that
> the triggering statement updates.
>
>
> Apparently you are updating the same table where you are deleting...
> This could lead to conflicts or never ending loops...
>
> Regards.
Yes, I'm updating the same table, but not the same record. I need
recalculate the carrying operation's balance :(
> Yes, I'm updating the same table, but not the same record. I need > recalculate the carrying operation's balance :(