table level triggers .- ERROR 4082

[Duarte Canuto]

Hi.
I Want to know if a field 'Quantity' exceeds the 'minimal stock' in a product
at warehouse and I am doing :

--get data into new vars
--if quantity exceeds insert into a auxiliar table with some of the new
values.

but this trigger raises an error "( ORA - 4082 ) NEW or OLD references not
allowed in table level trigger"

I'm not updating or inserting table data in the trigger, so i don´t understand
what is wrong.
can You Help me, please ?

the trigger is here :

CREATE or replace TRIGGER stockmin_tr
AFTER INSERT or update ON system.liamaarm
DECLARE
Aquant system.liamaarm.quant%TYPE;
AStock system.liamaarm.stockmin%TYPE;
BEGIN
/*** verificação da quantidade em relação ao stock mínimo ***/
SELECT (l.quant,l.stockmin,l.codigo,l.key,l.data,l.hora,l.armazem
,l.codprod,l.quant,l.speed,l.bastidor,l.cacifo, l.descricao, l.tipo)

INTO (:new.quant,new.stockmin,:new.codigo,:new.key,:new.data,:new.hora:new.armazem
,:new.codprod,:new.quant,:new.speed,:new.bastidor,:new.cacifo, :new.descricao,:new.tipo)

FROM system.liamaarm l
WHERE l.key = :new.key;
/*** Se o stock for menor que a quantidade ...***/
IF ((:new.stockmin < :new.quant) or (:new.stockmin = 0)) THEN
begin
insert into lialarme
(COD_ALERT,
ALERTA,
CODIGO,
KEY,
CLIENTEDE,
DATA,
HORA,
ARMAZEM,
CODPROD,
QUANT,
SPEED,
BASTIDOR,
CACIFO,
DESCRICAO,
TIPO)
values
(liamalarme_sq.nextval,
'Quantidade é menor que o stock mínimo',
liamaarm.CODIGO,
liamaarm.KEY,
liamaarm.CLIENTEDE,
sysdate,
trunc((SYSDATE - TRUNC(sysdate)) * 24)
liamaarm.ARMAZEM,
liamaarm.CODPROD,
liamaarm.QUANT,
liamaarm.SPEED,
liamaarm.BASTIDOR,
liamaarm.CACIFO,
liamaarm.DESCRICAO,
liamaarm.TIPO);
/** hour := TRUNC((SYSDATE - TRUNC(SYSDATE)) * 24); **/
end;
END IF;
END;

[Boris Milrud]

Duarte,

You cannot reference before (ld) and after (:new) values in the table-level
trigger, which makes perfect sense: it fires once per table, not per row.
So, if you update/insert 10 rows, which row you are trying to get a values
from while you are using old: and new: references?!

You can use it only in row-level trigger. Trigger type is specified in the
trigger declaration with FOR EACH ROW clause:

CREATE or replace TRIGGER stockmin_tr
AFTER INSERT or update ON system.liamaarm
FOR EACH ROW
...

Boris Milrud.

"Duarte Canuto" <duarte.canuto@local.online.pt> wrote:
>
>Hi.
>I Want to know if a field 'Quantity' exceeds the 'minimal stock' in a product
>at warehouse and I am doing :
>
> --get data into new vars
> --if quantity exceeds insert into a auxiliar table with some of the new
>values.
>
>but this trigger raises an error "( ORA - 4082 ) NEW or OLD references
not
>allowed in table level trigger"
>
>I'm not updating or inserting table data in the trigger, so i don´t understand
>what is wrong.
>can You Help me, please ?
>
>the trigger is here :
>
>CREATE or replace TRIGGER stockmin_tr
> AFTER INSERT or update ON system.liamaarm
> DECLARE
> Aquant system.liamaarm.quant%TYPE;
> AStock system.liamaarm.stockmin%TYPE;
> BEGIN
> /*** verificação da quantidade em relação ao stock mínimo ***/
> SELECT (l.quant,l.stockmin,l.codigo,l.key,l.data,l.hora,l.armazem
> ,l.codprod,l.quant,l.speed,l.bastidor,l.cacifo, l.descricao, l.tipo)
>
> INTO (:new.quant,new.stockmin,:new.codigo,:new.key,:new.data,:new.hora:new.armazem
> ,:new.codprod,:new.quant,:new.speed,:new.bastidor,:new.cacifo, :new.descricao,:new.tipo)
>
> FROM system.liamaarm l
> WHERE l.key = :new.key;
> /*** Se o stock for menor que a quantidade ...***/
> IF ((:new.stockmin < :new.quant) or (:new.stockmin = 0)) THEN
> begin
> insert into lialarme
> (COD_ALERT,
> ALERTA,
> CODIGO,
> KEY,
> CLIENTEDE,
> DATA,
> HORA,
> ARMAZEM,
> CODPROD,
> QUANT,
> SPEED,
> BASTIDOR,
> CACIFO,
> DESCRICAO,
> TIPO)
> values
> (liamalarme_sq.nextval,
> 'Quantidade é menor que o stock mínimo',
> liamaarm.CODIGO,
> liamaarm.KEY,
> liamaarm.CLIENTEDE,
> sysdate,
> trunc((SYSDATE - TRUNC(sysdate)) * 24)
> liamaarm.ARMAZEM,
> liamaarm.CODPROD,
> liamaarm.QUANT,
> liamaarm.SPEED,
> liamaarm.BASTIDOR,
> liamaarm.CACIFO,
> liamaarm.DESCRICAO,
> liamaarm.TIPO);
> /** hour := TRUNC((SYSDATE - TRUNC(SYSDATE)) * 24); **/
> end;
> END IF;
> END;
>