Función y disparador en PostgreSQL para contener cambios.

06 Jun, 2023 Programación 0

Quiero implementar una función y un trigger (sin parámetros) que permitan hacer cambios en una tabla B siempre y cuando se hagan en la tabla D. Lo que quiero decir con cambios son inserciones, actualizaciones o eliminaciones.

Por ejemplo, si elimino, actualizo o inserto un registro en la tabla D, necesito que también se haga automáticamente en la tabla B y me devuelva los nuevos valores agregados.

He diseñado esta tabla D de tal manera que tiene una clave primaria compuesta por 3 valores que obtiene de otras tablas (tabla A, tabla C y tabla D).

Por ejemplo, tengo:

TABLA A
x int (clave primaria)
y carácter
z carácter

TABLA C
a int (clave primaria)
b carácter
c carácter 

TABLA D
x (clave primaria)
a (clave primaria)
dd (clave primaria)
ddd
dddd

TABLA B
x (clave primaria)
a (clave primaria)
dd (clave primaria)
bbb
bbbb

La estructura debería ser algo similar a lo siguiente. He pensado en implementar varios TG_OP en función de varios IF, pero no puedo conseguir un resultado satisfactorio.

CREAR FUNCIÓN función()
RETORNA TRIGGER LENGUAJE plpgsql COMO $$
COMENZAR

DEVOLVER new;
FIN;
$$;

CREAR TRIGGER trigger
DESPUÉS DE ACTUALIZAR EN tabla D
PARA CADA FILA EJECUTAR PROCEDIMIENTO función();

Gracias a todos.

davy.ai

June 6, 2023 at 3:30 pm

Para implementar la función y el desencadenador deseados, puede utilizar una combinación de desencadenadores y una instrucción IF en la función de desencadenador.

Primero, cree el desencadenador para que se active después de cualquier actualización, inserción o eliminación en la tabla D:

CREATE TRIGGER trigger_on_D
AFTER INSERT OR UPDATE OR DELETE ON d
FOR EACH ROW
EXECUTE PROCEDURE sync_trigger_function();

Luego, cree la función de desencadenador sync_trigger_function que modificará la tabla B en consecuencia:

CREATE FUNCTION sync_trigger_function() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    DELETE FROM b WHERE x = OLD.x AND a = OLD.a AND dd = OLD.dd;
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE b SET bbb = NEW.ddd WHERE x = NEW.x AND a = NEW.a AND dd = NEW.dd;
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO b (x, a, dd, bbb)
      VALUES (NEW.x, NEW.a, NEW.dd, NEW.ddd);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

En la función de desencadenador, puede verificar el valor de TG_OP para determinar el tipo de operación que se está realizando (eliminar, actualizar o insertar). Dependiendo de la operación, puede modificar la tabla B en consecuencia.

Por ejemplo, si la función de desencadenador detecta una operación de eliminación, elimina la fila correspondiente de la tabla B en función de los valores de clave principal en OLD. Si detecta una operación de actualización, actualiza la columna bbb en la tabla B en función de los valores de clave principal en NEW. Si detecta una operación de inserción, inserta una nueva fila en la tabla B con los valores de NEW.

Finalmente, puede probar el desencadenador realizando algunas operaciones en la tabla D:

- Inserte una nueva fila en la tabla D
INSERT INTO d (x, a, dd, ddd) VALUES (1, 2, 3, 'nueva fila');

- Actualice una fila existente en la tabla D
UPDATE d SET ddd = 'fila actualizada' WHERE x = 1 AND a = 2 AND dd = 3;

- Elimine una fila de la tabla D
DELETE FROM d WHERE x = 1 AND a = 2 AND dd = 3;

Después de cada operación, revise la tabla B para ver si se ha modificado en consecuencia.

Answer

davy.ai

June 6, 2023 at 3:30 pm
Para implementar la función y el desencadenador deseados, puede utilizar una combinación de desencadenadores y una instrucción IF en la función de desencadenador.

Primero, cree el desencadenador para que se active después de cualquier actualización, inserción o eliminación en la tabla D:
```
CREATE TRIGGER trigger_on_D
AFTER INSERT OR UPDATE OR DELETE ON d
FOR EACH ROW
EXECUTE PROCEDURE sync_trigger_function();
```
Luego, cree la función de desencadenador sync_trigger_function que modificará la tabla B en consecuencia:
```
CREATE FUNCTION sync_trigger_function() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    DELETE FROM b WHERE x = OLD.x AND a = OLD.a AND dd = OLD.dd;
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE b SET bbb = NEW.ddd WHERE x = NEW.x AND a = NEW.a AND dd = NEW.dd;
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO b (x, a, dd, bbb)
      VALUES (NEW.x, NEW.a, NEW.dd, NEW.ddd);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```
En la función de desencadenador, puede verificar el valor de TG_OP para determinar el tipo de operación que se está realizando (eliminar, actualizar o insertar). Dependiendo de la operación, puede modificar la tabla B en consecuencia.

Por ejemplo, si la función de desencadenador detecta una operación de eliminación, elimina la fila correspondiente de la tabla B en función de los valores de clave principal en OLD. Si detecta una operación de actualización, actualiza la columna bbb en la tabla B en función de los valores de clave principal en NEW. Si detecta una operación de inserción, inserta una nueva fila en la tabla B con los valores de NEW.

Finalmente, puede probar el desencadenador realizando algunas operaciones en la tabla D:
```
- Inserte una nueva fila en la tabla D
INSERT INTO d (x, a, dd, ddd) VALUES (1, 2, 3, 'nueva fila');

- Actualice una fila existente en la tabla D
UPDATE d SET ddd = 'fila actualizada' WHERE x = 1 AND a = 2 AND dd = 3;

- Elimine una fila de la tabla D
DELETE FROM d WHERE x = 1 AND a = 2 AND dd = 3;
```
Después de cada operación, revise la tabla B para ver si se ha modificado en consecuencia.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Función y disparador en PostgreSQL para contener cambios.

Answer

davy.ai