Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Modify trigger - Look

Re: Modify trigger - Look

From: <fitzjarrell_at_cox.net>
Date: Fri, 13 Jul 2007 06:46:48 -0700
Message-ID: <1184334408.984677.72560@g4g2000hsf.googlegroups.com>


On Jul 13, 8:21 am, Alex Bibiano <a..._at_bibiano.es> wrote:
> This is the script I want execute:
>
> CREATE OR REPLACE TRIGGER "TRI_NICHOS"
> BEFORE DELETE OR INSERT OR UPDATE
> ON dbo.nichos
> REFERENCING NEW AS NEW OLD AS OLD
> FOR EACH ROW
> DECLARE
> num_emp_old VARCHAR (2);
> cod_alm_old VARCHAR (6);
> cod_art_old VARCHAR (10);
> num_emp_new VARCHAR (2);
> cod_alm_new VARCHAR (6);
> cod_art_new VARCHAR (10);
> contador NUMBER;
> stock NUMBER (11, 2);
> v_gestiona_nichos_almacen BOOLEAN;
> v_gestiona_stock BOOLEAN;
> v_nichos_disponible BOOLEAN;
>
> CURSOR comprueba_gestiona_nichos_cur (p_cod_alm stalmace.cod_alm
> %TYPE) IS
> SELECT 1
> FROM stalmace
> WHERE NVL (stalmace.gestiona_nichos, 'N') = 'S'
> AND stalmace.cod_alm = p_cod_alm;
>
> comprueba_gestiona_nichos_rec comprueba_gestiona_nichos_cur
> %ROWTYPE;
>
> CURSOR comprueba_stock_cur (p_cod_art articulo.cod_art%TYPE) IS
> SELECT 1
> FROM articulo
> WHERE NVL (articulo.tipo_stock, 'N') = 'S'
> AND articulo.cod_art = p_cod_art;
>
> comprueba_stock_rec comprueba_stock_cur%ROWTYPE;
>
> CURSOR comprueba_nicho_disponible_cur (
> p_num_emp nichos_disponibles.num_emp%TYPE
> ,p_cod_alm nichos_disponibles.cod_alm%TYPE
> ,p_cod_nicho nichos_disponibles.cod_nicho%TYPE) IS
> SELECT 1
> FROM nichos_disponibles
> WHERE nichos_disponibles.num_emp = p_num_emp
> AND nichos_disponibles.cod_alm = p_cod_alm
> AND nichos_disponibles.cod_nicho = p_cod_nicho;
>
> comprueba_nicho_disponible_rec comprueba_nicho_disponible_cur
> %ROWTYPE;
> BEGIN
> IF UPDATING
> OR DELETING THEN
> OPEN comprueba_stock_cur (:OLD.cod_art);
>
> FETCH comprueba_stock_cur
> INTO comprueba_stock_rec;
>
> v_gestiona_stock := comprueba_stock_cur%FOUND;
>
> CLOSE comprueba_stock_cur;
>
> IF v_gestiona_stock THEN
> OPEN comprueba_gestiona_nichos_cur (:OLD.cod_alm);
>
> FETCH comprueba_gestiona_nichos_cur
> INTO comprueba_gestiona_nichos_rec;
>
> v_gestiona_nichos_almacen := comprueba_gestiona_nichos_cur
> %FOUND;
>
> CLOSE comprueba_gestiona_nichos_cur;
>
> IF NOT v_gestiona_nichos_almacen THEN
> raise_application_error (-20000
> ,'El almacén no gestiona
> ubicaciones');
> END IF;
>
> -- Validamos que la ubicación existe
> OPEN comprueba_nicho_disponible_cur (:OLD.num_emp
> ,:OLD.cod_alm
> ,:OLD.cod_nicho);
>
> FETCH comprueba_nicho_disponible_cur
> INTO comprueba_nicho_disponible_rec;
>
> v_nichos_disponible := comprueba_nicho_disponible_cur%FOUND;
>
> CLOSE comprueba_nicho_disponible_cur;
>
> IF NOT v_nichos_disponible THEN
> raise_application_error (-20007
> ,'La ubicación introducida no
> existe');
> END IF;
>
> UPDATE st_stocks
> SET stock_fisi = NVL (stock_fisi, 0) - :OLD.cantidad_total
> WHERE num_emp = :OLD.num_emp
> AND cod_alm = :OLD.cod_alm
> AND cod_art = :OLD.cod_art;
>
> IF SQL%NOTFOUND THEN
> INSERT INTO st_stocks
> (num_emp
> ,cod_alm
> ,cod_art
> ,stock_fisi)
> VALUES (:OLD.num_emp
> ,:OLD.cod_alm
> ,:OLD.cod_art
> ,-:OLD.cantidad_total);
> END IF;
>
> DELETE st_stocks
> WHERE num_emp = :OLD.num_emp
> AND cod_alm = :OLD.cod_alm
> AND cod_art = :OLD.cod_art
> AND stock_fisi = 0;
> END IF;
> END IF;
>
> IF INSERTING
> OR UPDATING THEN
> OPEN comprueba_stock_cur (:NEW.cod_art);
>
> FETCH comprueba_stock_cur
> INTO comprueba_stock_rec;
>
> v_gestiona_stock := comprueba_stock_cur%FOUND;
>
> CLOSE comprueba_stock_cur;
>
> IF v_gestiona_stock THEN
> OPEN comprueba_gestiona_nichos_cur (:NEW.cod_alm);
>
> FETCH comprueba_gestiona_nichos_cur
> INTO comprueba_gestiona_nichos_rec;
>
> v_gestiona_nichos_almacen := comprueba_gestiona_nichos_cur
> %FOUND;
>
> CLOSE comprueba_gestiona_nichos_cur;
>
> IF NOT v_gestiona_nichos_almacen THEN
> raise_application_error (-20000
> ,'El almacén no gestiona
> ubicaciones');
> END IF;
>
> -- Validamos que la ubicación existe
> OPEN comprueba_nicho_disponible_cur (:NEW.num_emp
> ,:NEW.cod_alm
> ,:NEW.cod_nicho);
>
> FETCH comprueba_nicho_disponible_cur
> INTO comprueba_nicho_disponible_rec;
>
> v_nichos_disponible := comprueba_nicho_disponible_cur%FOUND;
>
> CLOSE comprueba_nicho_disponible_cur;
>
> IF NOT v_nichos_disponible THEN
> raise_application_error (-20007
> ,'La ubicación introducida no
> existe');
> END IF;
>
> UPDATE st_stocks
> SET stock_fisi = NVL (stock_fisi, 0) + :NEW.cantidad_total
> WHERE num_emp = :NEW.num_emp
> AND cod_alm = :NEW.cod_alm
> AND cod_art = :NEW.cod_art;
>
> IF SQL%NOTFOUND THEN
> INSERT INTO st_stocks
> (num_emp
> ,cod_alm
> ,cod_art
> ,stock_fisi)
> VALUES (:NEW.num_emp
> ,:NEW.cod_alm
> ,:NEW.cod_art
> ,:NEW.cantidad_total);
> END IF;
>
> DELETE st_stocks
> WHERE num_emp = :NEW.num_emp
> AND cod_alm = :NEW.cod_alm
> AND cod_art = :NEW.cod_art
> AND stock_fisi = 0;
> END IF;
>
> IF :NEW.cantidad_total < 0 THEN
> raise_application_error
> (-20001
> ,'No se permite stock negativos en un
> nicho.');
> END IF;
> END IF;
> END tri_stajustes;

I can presume you've created a user named 'DBO'? Oracle is not SQL Server.

You MAY need to restart the database server, drop the original trigger and create this one after the original no longer exists.

Without seeing output from V$LOCKED_OBJECT when this is run I can't tell you what could be blocking this process.

David Fitzjarrell Received on Fri Jul 13 2007 - 08:46:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US