PostgreSQL Trigger Tutorial with EMP Table Examples

by SathiyaMoorthy on October 6, 2010

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

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 15 comments… read them below or add one }

1 Balakrishnan Mariyappan October 7, 2010 at 7:49 am

Good refresh!!!

2 Morgan B. June 3, 2011 at 2:45 pm

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.

3 Puu2 June 5, 2011 at 6:39 am

you must execute this :
INSERT INTO emp_table (empid, empname, salary) values (101, ‘sathiya’, ’3000′);
without ‘#’..

4 Jay Johnson August 9, 2012 at 2:36 am

Hi there, thank you for this, i tried it but got this error: column new.empid does not exist.

5 Nasir Uddin September 8, 2012 at 8:02 am

This article was very helpful for me.
Thanks

6 Jason C November 10, 2012 at 11:05 pm

Very helpful. Always great to see a simple example that is easily followed.

Thanks!

7 Punith Reddy.T February 21, 2013 at 1:54 pm

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

8 Punith Reddy.T February 21, 2013 at 2:24 pm

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

9 suresh March 1, 2013 at 1:06 am

i tried this above trigger program and it was executed successfully in my pgadmin

10 Anonymous April 18, 2013 at 2:01 am

it shows error tg_op doesnot exist

11 Lukasz August 5, 2013 at 5:29 am

Great ! Thanks !

12 dinesh joshi August 10, 2013 at 5:43 am

thank u ..!

13 syd September 11, 2013 at 10:26 am

thanks ramesh, good work

14 John November 5, 2013 at 10:53 am

Thanks. Much appreciated: An example that actually works. Awesome.

15 Ramu Mathi November 5, 2014 at 4:42 am

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.

Leave a Comment

Previous post:

Next post: