Como evitar el insert del before insert.

[jgrc]

Buenas tardes a todos y gracias por su ayuda.

Tengo que realizar la siguiente acción, cuando el usuario inserte un nuevo registro, debo de checar si este existe, en caso de existir solo debo actualizar ciertos campos, de lo contrario inserto el registro.

Entonces para lo anterior ocupe un trigger con la función before insert, el problema es cuando actualiza porque resulta que realiza la actualización pero al mismo tiempo me genera el registro. quise evitar la acción del insert con un raise application error que efectivamente me evita el insert, pero no realiza el update.

este es mi codigo:

Código SQL:

CREATE OR REPLACE TRIGGER tr_polizasprueba
BEFORE INSERT ON polizasprueba
REFERENCING NEW AS NEW FOR each ROW
  DECLARE
  existe INTEGER;
BEGIN
   SELECT COUNT(*) INTO existe FROM polizasprueba
     WHERE TIPOPOLIZA = :NEW.TIPOPOLIZA AND FOLIO = :NEW.FOLIO AND to_char((FECHA),'yyyy') = to_char((:NEW.FECHA),'yyyy');
     IF existe >0 THEN 
        UPDATE admin.polizasprueba
        SET CLASE = :NEW.CLASE,IDDIARIO = :NEW.IDDIARIO,CONCEPTO = :NEW.CONCEPTO,
            SISTEMAORIGEN = :NEW.SISTEMAORIGEN,IMPRESA = :NEW.IMPRESA,IDUSUARIO = :NEW.IDUSUARIO,
            FECHAALTA = :NEW.FECHAALTA,BALANCEADA = :NEW.BALANCEADA
        WHERE TIPOPOLIZA = :NEW.TIPOPOLIZA AND FOLIO = :NEW.FOLIO AND to_char((FECHA),'yyyy') = to_char((:NEW.FECHA),'yyyy');
        RAISE_APPLICATION_ERROR(-20000, 'no quiero realizar el insert');
     END IF;
END;
 

De hecho intente conel rollback y me genera el mismo resultado. tienen alguna consejo? gracias.

[morway]

Forma parte todo de la misma transacción. Si haces un RAISE finalmente no se realiza el COMMIT.

Si en cualquier caso quieres que el update se realice de modo independiente al insert deberías realizarlo encapsulado dentro de un PROCEDURE con el PRAGMA AUTONOMOUS_TRANSACTION. De ese modo al ser una transacción diferente si que podrás hacerlo.

Investiga sobre eso y si tienes dudas plantéalas, te responderé encantado.

Un saludo.

[jgrc]

Gracias morway por tu ayuda, mira quedo asi:

Código SQL:

CREATE OR REPLACE TRIGGER tr_polizasprueba
BEFORE INSERT ON polizasprueba
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
  existe INTEGER;
BEGIN
   SELECT COUNT(*) INTO existe FROM polizasprueba
     WHERE TIPOPOLIZA = :NEW.TIPOPOLIZA AND FOLIO = :NEW.FOLIO AND to_char((FECHA),'yyyy') = to_char((:NEW.FECHA),'yyyy');
     IF existe >0 THEN 
        UPDATE polizasprueba
        SET CLASE = :NEW.CLASE,IDDIARIO = :NEW.IDDIARIO,CONCEPTO = :NEW.CONCEPTO,
            SISTEMAORIGEN = :NEW.SISTEMAORIGEN,IMPRESA = :NEW.IMPRESA,IDUSUARIO = :NEW.IDUSUARIO,
            FECHAALTA = :NEW.FECHAALTA,BALANCEADA = :NEW.BALANCEADA
        WHERE TIPOPOLIZA = :NEW.TIPOPOLIZA AND FOLIO = :NEW.FOLIO AND to_char((FECHA),'yyyy') = to_char((:NEW.FECHA),'yyyy');
       RAISE_APPLICATION_ERROR('No registrar el insert');
        procerror;
     END IF;
END;
 

solo agregueas lineas en rojo y en la sección de procedimientos, quedo asi:

Código SQL:

CREATE OR REPLACE PROCEDURE procerror IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('rollback ejecutado');
END procerror;
 

solo que a la hora de compilarlo me marca estos errores:

Error(17,9): PL/SQL: Statement ignored
Error(17,9): PLS-00306: número o tipos de argumentos erróneos al llamar a 'RAISE_APPLICATION_ERROR'


voy a intentar encontrarlos errores y ejecutarlo, pero mas o menos es así como me recomendaste?

gracias.

[jc3000]

Antes que nada, un consejillo.

Cuando te salta un error, si no sabes de que vá, lo tecleas en el google y te das un rulo por los resultados que te saque, te aseguro que en muchas ocasiones es mas rápido que postearlo en un foro y si no ves nada que te lo aclare, entonces es el momento de postear en los foros.

Y ahora..... el error que te está dando es por que le tienes que meter un código a la descripción, ls sintaxis correcta sería ésta :

Código SQL:

RAISE_APPLICATION_ERROR(-20001, 'No registrar el insert');
 

Entonces al hacer el raise, te mostraría algo similar a esto :

Código SQL:

ORA-20001 No registrar el INSERT
 

[morway]

Hola, de nuevo compañero.

Pensaba que el update que hacías era para otra tabla. Lo que pretendes, a mi modo de ver parece demasiado rebuscado. desde el trigger BEFORE INSERT no tiene sentido forzar un UPDATE y cancelar el INSERT. Seguramente tendrás problemas por MUTATING del registro que estás insertando/actualizando.

Deberías plantearte encapsular esa funcionalidad que necesitas en un procedimiento que haga el INSERT o UPDATE según corresponda.

Ejemplo:

Código SQL:

CREATE OR REPLACE PROCEDURE P_GUARDAR_POLIZA( P_POLIZA IN POLIZASPRUEBA%ROWTYPE ) IS
  --
  v_NUMERO_POLIZAS NUMBER ;
  --
BEGIN
  --
  SELECT COUNT(*) 
   INTO v_NUMERO_POLIZAS
     ...
     ...
 
  IF V_NUMERO_POLIZAS > 0 THEN
    --
    UPDATE ...........
    --
  ELSE
    --
    INSERT .......
    --
  END IF;
  --
END;
 

Ago así, espero que te sirva.

Un saludo.

[jgrc]

Gracias morway por tu consejo, de hecho hice esto con anterioridad y me funciono perfectamente:

Código SQL:

MERGE INTO POLIZASPRUEBA pp
USING (
SELECT 1 IDPOLIZA
,DATE '04-01-12' FECHA
,1 TIPOPOLIZA
,100 CLASE
,00 IDDIARIO
,'abcdefg' CONCEPTO
,1 SISTEMAORIGEN
,2 IMPRESA
,7 IDUSUARIO
,'11/12/2012' FECHAALTA
,1 BALANCEADA
,00000032 FOLIO
FROM dual
) p
ON (pp.TIPOPOLIZA = p.TIPOPOLIZA AND pp.FOLIO =p.FOLIO AND to_char((pp.FECHA),'yyyy') = to_char((p.FECHA),'yyyy'))
WHEN NOT MATCHED THEN
INSERT VALUES (p.IDPOLIZA,p.FECHA,p.TIPOPOLIZA,p.CLASE,p.IDDIARIO,p.CONCEPTO,p.SISTEMAORIGEN,
p.IMPRESA,p.IDUSUARIO,p.FECHAALTA,p.BALANCEADA,p.FOLIO)
WHEN MATCHED THEN
UPDATE SET pp.CLASE = p.CLASE,pp.IDDIARIO = p.IDDIARIO,pp.CONCEPTO = p.CONCEPTO,
pp.SISTEMAORIGEN = p.SISTEMAORIGEN,pp.IMPRESA = p.IMPRESA,pp.IDUSUARIO = p.IDUSUARIO,
pp.FECHAALTA = p.FECHAALTA,pp.BALANCEADA = p.BALANCEADA;
 

lo malo esque el merge into no funciona en los triggers, y mis superiores quieren que esto se haga cuando se ejecuta el insert, sabes como puedo meterlo a parte del before insert, mucho te lo agradecería, digo la funcion se hacerla igual que el procedimiento, pero a que horas los llamo?

gracias

[morway]

Deberías llamar al procedimiento desde el punto de la aplicación en el cual insertas en polizaprueba y en lugar de hacer un MERGE, un INSERT o UPDATE, llamar al proceso que lo gestiona.

Código SQL:

DECLARE
  v_POLIZA  POLIZAPRUEBA%ROWTYPE ;
BEGIN
  v_POLIZA.IDPOLIZA     := 1 ;
  v_POLIZA.TIPOPOLIZA := 1;
  v_POLIZA.CLASE         := 100 ;
  ...
  ...
  ...
  P_GUARDAR_POLIZA( P_POLIZA => v_POLIZA);
END;
 

Un saludo