Question: I have created triggers in Oracle database. But, I’m new to PostgreSQL. Can you explain me how to create a trigger in Postgres with proper examples?
Answer: A trigger can be used to execute a function when an event (insert, or delete, or update) occurs in a table. Let us review how to create a trigger with an example.
1. PostgreSQL Create Demo Tables
Create emp_table, and backup_tbl as shown below.
CREATE TABLE emp_table ( empid int, empname name, salary int ); CREATE TABLE backup_tbl ( empid int, empname name, salary int, operation varchar(25) );
For some postgreSQL commands, refer to our earlier 15 Advanced postgreSQL Commands article.
2. PostgreSQL Create language
Create language plpgsql which is required for trigger.
CREATE LANGUAGE plpgsql;
3. Create PostgreSQL Trigger Function
This function will be invoked before the insert, delete or update operation. It does the following:
- Before delete operation, it inserts the old data into backup_tbl.
- Before update operation, it inserts the old data into backup_tbl.
- Before insert operation, it inserts the new data into backup_tbl.
CREATE FUNCTION ins_function() RETURNS trigger AS '
BEGIN
IF tg_op = ''DELETE'' THEN
INSERT INTO backup_tbl(empid, empname, salary, operation)
VALUES (old.empid, old.empname, old.salary, tg_op);
RETURN old;
END IF;
IF tg_op = ''INSERT'' THEN
INSERT INTO backup_tbl(empid, empname, salary, operation)
VALUES (new.empid, new.empname, new.salary, tg_op);
RETURN new;
END IF;
IF tg_op = ''UPDATE'' THEN
INSERT INTO backup_tbl(empid, empname, salary, operation)
VALUES (old.empid, old.empname, old.salary, tg_op);
RETURN new;
END IF;
END
' LANGUAGE plpgsql;
4. Create PostgreSQL Trigger
Using the following create trigger sql command, create a trigger which will invoke the function ‘ins_function’ after insert, delete or update operation.
CREATE TRIGGER audit_ins AFTER INSERT OR DELETE OR UPDATE
ON emp_table FOR each ROW
EXECUTE PROCEDURE ins_function();
Make sure to backup the postgreSQL database using pg_dump and psql command.
5. Test the PostgreSQL Trigger
Try inserting sample data into the emptable, which will automatically insert the data to the backup_table as per the trigger logic.
# INSERT INTO emp_table (empid, empname, salary) values (101, 'sathiya', '3000'); INSERT 0 1 # SELECT * from backup_tbl ; empid | empname | salary | operation -------+---------+--------+----------- 101 | sathiya | 3000 | INSERT (1 row)
Try updating the data in emptable, which will automatically insert the old data to the backup_tbl as per the trigger logic.
# UPDATE emp_table SET salary = '2500' where empid = '101'; UPDATE 1 # SELECT * from backup_tbl ; empid | empname | salary | operation -------+---------+--------+----------- 101 | sathiya | 3000 | INSERT 101 | sathiya | 3000 | UPDATE (2 rows)
Try deleting the data in emptable, which will automatically insert the old data to the backup_tbl as per the trigger logic.
# DELETE FROM emp_table WHERE empid = '101'; DELETE 1 # SELECT * from backup_tbl ; empid | empname | salary | operation -------+---------+--------+----------- 101 | sathiya | 3000 | INSERT 101 | sathiya | 3000 | UPDATE 101 | sathiya | 2500 | DELETE
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
Good refresh!!!
Hi, i am trying your example, but i can’t test the PostgreSQL trigger, this happens:
when i execute : # INSERT INTO emp_table (empid, empname, salary) values (101, ‘sathiya’, ‘3000’);
********** Error **********
ERROR: syntax error at or near “#”
SQL state: 42601
Character: 1
Please, can you help me? i followed all your steps.
you must execute this :
INSERT INTO emp_table (empid, empname, salary) values (101, ‘sathiya’, ’3000′);
without ‘#’..
Hi there, thank you for this, i tried it but got this error: column new.empid does not exist.
This article was very helpful for me.
Thanks
Very helpful. Always great to see a simple example that is easily followed.
Thanks!
Hi,
Thanks for your nice explanation.
Can you provide some explanation for the variable “tg_op” in trigger function which you used to know the event occurred on the table.
Thanks and Regards,
Punith Reddy.T
Hi,
I got it 🙂
pg_op is an PostgreSQL_operation variable which by default holds the data about the operation on a table 🙂
Thanks and Regards,
Punith Reddy.T
i tried this above trigger program and it was executed successfully in my pgadmin
it shows error tg_op doesnot exist
Great ! Thanks !
thank u ..!
thanks ramesh, good work
Thanks. Much appreciated: An example that actually works. Awesome.
Hi EveryOne,
Can you guys please help me out from my clarification.
CREATE TRIGGER audit_ins AFTER INSERT OR DELETE OR UPDATE
ON emp_table FOR each ROW
EXECUTE PROCEDURE ins_function();
Here we are calling procedure instead of functions. Actually ins_function() is function right ? then we are calling Procedure.
Thanks,
Ramu.
Thank you very much…great job!!!
Thanku very much..
nice explanation.