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

Tweet

Share

 Comment

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