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

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..