Oracle - Merge dentro de un trigger

   
Vista:

Merge dentro de un trigger

Publicado por Mario (9 intervenciones) el 08/04/2014 15:33:29
Hola a todos,

Esta es la tabla que voy a utilizar para el trigger:

CREATE TABLE "grace_period" (
"id" NUMBER(11) PRIMARY KEY NOT NULL,
"id_user" NUMBER(20) NOT NULL,
"date_limit" DATE NOT NULL,
"active" NUMBER(11),
"created_at" DATE NOT NULL,
"updated_at" DATE
);

Y lo que me gustaría hacer es crear un TRIGGER BEFORE INSERT que comprobara si la nueva entrada ya contiene ese "id_user" o no.

Si existe "id_user" entonces hacer una actualización de la columna "active" de ese "id_user" y si no existe, deberá insertar una nueva fila.

¿Podría alguien ayudarme? es la primera vez que trato de hacer una fusión en un disparador y un merge.

Gracias

Mario
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Merge dentro de un trigger

Publicado por luis pablo luis2409@gmail.com (232 intervenciones) el 08/04/2014 16:34:09
Hola Mario,
Puedes ser mas exacto en tu pregunta.

Cual es el problema que se te presenta?
Te aparece algún mensaje de error?

Sé más preciso en la duda que quieres despejar para poder orientarte.

Saludos


Luis
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por Mario (9 intervenciones) el 08/04/2014 17:40:03
Hola Luis,

Este es el trigger que no me funciona:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE TRIGGER "user_grace_changes"
BEFORE INSERT ON "grace_period"
FOR EACH ROW
BEGIN
 
MERGE INTO "grace_period" t1
  USING (SELECT new."id_user", new."date_limit", new."active"
           FROM "grace_period") t2
     ON (t1."id_user" = t2."id_user")
   WHEN MATCHED THEN
     UPDATE SET t1."active" = t2."active"
   WHEN NOT MATCHED THEN
     INSERT( t1."id_user", t1."date_limit", t1."active" )
       VALUES( t2."id_user", t2."fecha", t2."active" );
 
END;


Lo he sacado deste merge, el cual si funciona:

1
2
3
4
5
6
7
8
9
MERGE INTO "grace_period" t1
  USING (SELECT 456 as "id_user", to_date('2014-04-09 13:00:00','YYYY-MM-DD HH24:MI:SS') as "fecha", 1 as "active"
           FROM dual) t2
     ON (t1."id_user" = t2."id_user")
   WHEN MATCHED THEN
     UPDATE SET t1."active" = t2."active"
   WHEN NOT MATCHED THEN
     INSERT( t1."id_user", t1."date_limit", t1."active" )
       VALUES( t2."id_user", t2."fecha", t2."active" );


Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por Mario (9 intervenciones) el 09/04/2014 12:15:31
Buenas!

Conseguí crear el trigger el cual es este:

CREATE OR REPLACE TRIGGER "user_grace_changes"
BEFORE INSERT ON "grace_period"
FOR EACH ROW
BEGIN

MERGE INTO "grace_period" t1
USING dual
ON (t1."id_user" = :new."id_user")
WHEN MATCHED THEN
UPDATE SET t1."active" = :new."active"
WHEN NOT MATCHED THEN
INSERT( t1."id_user", t1."date_limit", t1."active" )
VALUES( :new."id_user", :new."date_limit", :new."active" );

END;



Pero cuando hago un insert sobre esa tabla, recibo los siguienets errores:

rror SQL: ORA-04091: la tabla PLATAFORMA.grace_period está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "PLATAFORMA.user_grace_changes", línea 3
ORA-04088: error durante la ejecución del disparador 'PLATAFORMA.user_grace_changes'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.


Saludos!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por miguel (8 intervenciones) el 09/04/2014 13:26:01
Hola,
me parece q lo q ocurre es q estas modificando la misma tabla q gatillo al trigger, osea q el trigger podria estar generando otra ejecucion del mismo trigger.
saludos,
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por Mario (9 intervenciones) el 09/04/2014 15:04:29
Buenas!

Yo lo que quiero es que cuando vaya a crearse un registro nuevo, el trigger compruebe si ya existe y si es asi que update el ya existente y sino que haga el insert.

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por luis pablo (232 intervenciones) el 09/04/2014 21:44:18
Hola Mario, no puedes hacer algo sobre la misma tabla en la cual se dispara el trigger, por este motivo oracle lo detecta como error y te notifica que la tabla esta mutando, "la tabla esta cambiando en ese instante" .

Te paso esta nota :

Primero comento un poco qué es una “tabla mutante“. Se considera “mutante” una tabla que está siendo modificada, por ejemplo la tabla sobre la que el propio trigger se ha disparado por causa de un update/insert/delete.

O lo que es lo mismo, dentro de un trigger no podemos hacer referencia a la misma tabla que lo ha disparado, ya que, al estar siendo modificada, Oracle no puede asegurar una visión “consistente” de sus datos. En caso de hacerlo aparece este error.

Correcto, ¿y qué podemos hacer? Pues hay varias soluciones:

Hasta la versión 10gR2 podíamos:

Cambiar el código y no usar triggers: Podría ser que se pudiese solucionar modificando el diseño del modelo de datos o reprogramando una parte del codigo.

Usar un trigger “AFTER”: En “AFTER” los cambios en la tabla ya estan “consumados” y podemos acceder a una visión “consistente” de ésta.

Usar “PRAGMA AUTONOMOUS_TRANSACTION”: Nuestro trigger se ejecuta en una transacción diferente, y por tanto vemos la tabla en modo “consistente”. No es muy recomendable usar este sistema, ya que por ejemplo:

Si queremos realizar varios cambios seguidos dentro de la misma transacción no tendremos acceso a ellos (es una transacción diferente cada vez)
O si el trigger falla y se hace rollback tampoco nos enteraremos (no deshará la transacción en que se ha disparado el trigger)
A partir de la versión 11gR1, a las anteriores opciones podemos añadir:

Usar “COMPOUND TRIGGERS”: En la versión 11gR1 ha aparecido un nuevo tipo de trigger llamado “Compound Triggers“. En estos triggers podemos realizar cálculos previos para definir un “estado” que después es accesible durante la ejecución del trigger.

En resumen, que los cálculos que queramos hacer sobre la tabla afectada por el trigger los haremos previamente, guardando los valores necesarios en variables a las que accederemos posteriormente (evitando el acceso directo a la tabla y por tanto el problema). No nos soluciona todos los casos de tablas mutantes pero sí una parte importante de ellos.

El ejemplo que viene en la documentación es bastante bueno, por lo que os lo referencio directamente: Example 9-4 Compound Trigger that Avoids Mutating-Table Error

PD: No dejéis de estudiar qué otras ventajas ofrecen los “compound triggers”, pues la solución a las tablas mutantes sólo es una consecuencia de sus funcionalidades


Otra nota de interés :

Using Compound Triggers to Avoid Mutating-Table Error

You can use compound triggers to avoid the mutating-table error (ORA-04091) described in Trigger Restrictions on Mutating Tables.

Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.

Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-4. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

Example 9-4 Compound Trigger that Avoids Mutating-Table Error

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;

TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;

BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;

AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;




Saludos


Luis
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por luis pablo (232 intervenciones) el 09/04/2014 21:58:49
Mario, cuando dices :

"Yo lo que quiero es que cuando vaya a crearse un registro nuevo, el trigger compruebe si ya existe y si es asi que update el ya existente y sino que haga el insert."


Esto lo he realizado con un procedimiento de BD.

algo asi:

Create or replace procedure pr_registra_Cliente ( p_codigo cliente.codigo%type,
p_nombre cliente.nombre%type,
p_email cliente.email%type ) is

nCodigo cliente.codigo%type;
begin

update cliente
set nombre = p_nombre
email = p_email
where codigo= p_codigo;



if sql%notfound then
----Aqui genereamos el codigo para el nuevo cliente

select nvl(max(codigo) ,0)+1
into nCodigo
from cliente;

----
insert into cliente(codigo)
values ( nCodigo, p_nombre, p_email ) ;

end if;

commit;


end ;


Puedes probar con un procedimiento .....

Espero esto solucione tu requerimiento

Saludos


Luis
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Merge dentro de un trigger

Publicado por miguel (8 intervenciones) el 10/04/2014 13:46:08
Buenas,
Entonces el flujo del trigger deberia ser:


1. Verifico que el registro exista
1.1 Sí no existe, entonces no hago nada, ya que el registro sera insertado de igual manera luego que finalice el trigger.
1.2 Sí existe, entonces realizo el update (ojo!, no merge), y ademas, debo cancelar la insercion en curso (que será generada luego que finalice el triger, por el insert primario)


Ahora, me parece el merge, deberia ejecutarlo el proceso primario que esta ejecutando la insercion del nuevo registro, y no un trigger.

Saludos,
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar