Menu
I have a table named awards. How can I mount a Trigger in PostgreSQL where each insert in the table awards updates a different table?
709
Syntax. CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ]; Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.
Old and new values are available in both BEFORE and AFTER row triggers. A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired).
Triggers have special INSERTED and DELETED tables to track "before" and "after" data. So you can use something like IF EXISTS (SELECT * FROM DELETED) to detect an update. You only have rows in DELETED on update, but there are always rows in INSERTED . Look for "inserted" in CREATE TRIGGER.
Here we have two tables named table1 and table2. Using a trigger I'll update table2 on insertion into table1.
Create the tables
CREATE TABLE table1 ( id integer NOT NULL, name character varying, CONSTRAINT table1_pkey PRIMARY KEY (id) ) CREATE TABLE table2 ( id integer NOT NULL, name character varying ) The Trigger Function
CREATE OR REPLACE FUNCTION function_copy() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO table2(id,name) VALUES(new.id,new.name); RETURN new; END; $BODY$ language plpgsql; The Trigger
CREATE TRIGGER trig_copy AFTER INSERT ON table1 FOR EACH ROW EXECUTE PROCEDURE function_copy(); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
