Skip to content
Advertisement

PostgreSQL, add row to table when a row is created in another table

I am trying to create a trigger function, to create a new row, in a table, when a value is modified or created in another table. But the problem is that I need to insert in the other table, the primary key that provoked the trigger function. Is there a way to do it?

Basically, when an insert or update will be done in table 1, I want to see in table 2 a new row, with one field filed with the value of the primary key of the row in table1 that provoked the trigger.

begin
    INSERT INTO resultados_infocorp(id_user, Procesado)
    VALUES (<PRIMARY_KEY>,false)
    RETURN NEW;
End;

This is because if Procesado is false, thank to the id_user I will make some validations, but the ID of the user is necesary and I cant do it from the backend of my project, because I have many db inputs.

enter image description here

PD: The primary key of the new table is a sequence, this is the reason why I am not passing this arg.

CREATE TRIGGER resultados_infocorp_actualizar
    AFTER INSERT OR UPDATE OF id_user, fb_id, numdocumento, numtelefono, tipolicencia, trabajoaplicativo
    ON public.usuarios
    FOR EACH ROW
    EXECUTE PROCEDURE public.update_solicitudes_infocorp();

Advertisement

Answer

You have not shown the trigger definition. Still if you want the PK value then something like:

INSERT INTO resultados_infocorp(id_user, Procesado)
    VALUES (NEW.pk_fld,false)

Where pk_fld is the name of your PK field. Take a look here:

https://www.postgresql.org/docs/current/plpgsql-trigger.html

for what is available to a trigger function. For the purpose of this question the important part is:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement