table level triggers .- ERROR 4082


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: table level triggers .- ERROR 4082

  1. #1
    Duarte Canuto Guest

    table level triggers .- ERROR 4082


    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;


  2. #2
    Boris Milrud Guest

    Re: table level triggers .- ERROR 4082


    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;
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center