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.
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.