≡ Menu

PostgreSQL Trigger Tutorial with EMP Table Examples

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

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

{ 17 comments… add one }

  • Balakrishnan Mariyappan October 7, 2010, 7:49 am

    Good refresh!!!

  • Morgan B. June 3, 2011, 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.

  • Puu2 June 5, 2011, 6:39 am

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

  • Jay Johnson August 9, 2012, 2:36 am

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

  • Nasir Uddin September 8, 2012, 8:02 am

    This article was very helpful for me.
    Thanks

  • Jason C November 10, 2012, 11:05 pm

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

    Thanks!

  • Punith Reddy.T February 21, 2013, 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

  • Punith Reddy.T February 21, 2013, 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

  • suresh March 1, 2013, 1:06 am

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

  • Anonymous April 18, 2013, 2:01 am

    it shows error tg_op doesnot exist

  • Lukasz August 5, 2013, 5:29 am

    Great ! Thanks !

  • dinesh joshi August 10, 2013, 5:43 am

    thank u ..!

  • syd September 11, 2013, 10:26 am

    thanks ramesh, good work

  • John November 5, 2013, 10:53 am

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

  • Ramu Mathi November 5, 2014, 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.

  • Mohd Imran February 27, 2015, 1:25 am

    Thank you very much…great job!!!

  • simi March 19, 2015, 2:56 am

    Thanku very much..
    nice explanation.

Leave a Comment