Simple Trigger Example in PostgreSQL

When I was looking for the syntax to do a trigger in PostgreSQL recently I was confused because of the complexity of the examples, even the PostgreSQL documentation. It was thorough, but all I needed was a quick example. (Microsoft’s MSDN has a bad habit of doing this as well) So after I got one working, I thought I’d post to help anybody else that needed a simple trigger to update on an insert. To begin, to create a trigger in PostgreSQL, you will need two parts, the procedure and the trigger itself.

The Procedure

CREATE OR REPLACE FUNCTION process_example_procedure() RETURNS TRIGGER AS $example_table$
	BEGIN 
		IF (TG_OP = 'INSERT') THEN			
			UPDATE second_table SET 
				name = NEW.name
				WHERE primary_id = NEW.primary_id ;
		END IF;
		RETURN NEW;
	END;
$example_table$ LANGUAGE plpgsql

This will create the procedure. Next we will create the trigger

The Trigger

CREATE TRIGGER example_trigger AFTER INSERT ON example_table
	FOR EACH ROW EXECUTE PROCEDURE process_example_procedure();

There you have it! A simple update on insert trigger in PostgreSQL. Good luck and happy coding!