Creating Triggers In PostgreSQL

A trigger is an approach through which a database automatically execute a particular event or a function whenever certain operation is performed. Triggers perform wide range of functions like enhancing the performance, replication of data, enhancing and auditing the changes and enforcement and execution of business rule.
Triggers are used before or after any INSERT, UPDATE or DELETE operation, either single modified row or multiple rows. In case a trigger event occurs, the trigger’s function is called at the appropriate time to handle the event.

Let us understand this through an example
I am working on a shipment project, here I have two modules to look after which are

• Shipment order – Orders that are to be shipped.
• Warehouse order – Orders that would be placed on notification of a shipment order.

For both these orders I have tables like these:

TABLE STATUS BEFORE SHIPMENT

SHIPMENT ORDER
ID	PRODUCT NAME	STATUS
1	LAPTOP	ACTIVE

SHIPMENT ORDER ITEMS
ID	SHIPMENT_ORDER_ID	ITEM	STATUS
1	1	LAPTOPTABLE	ACTIVE
2	1	LAPTOP FAN	ACTIVE

WAREHOUSE ORDER
ID	PRODUCT NAME	STATUS
1	LAPTOP	ACTIVE

WAREHOUSE ORDER ITEMS
ID	WAREHOUSE_ORDER_ID	ITEM	STATUS
1	1	LAPTOPTABLE	ACTIVE
2	1	LAPTOP FAN	ACTIVE

Now suppose according to the given example I shipped only laptop table. I want when an order of laptop table is shipped, it’s status should be changed from both the tables (i.e. shipment_order_items, warehouse_order_items) .

After a order my table data should look like this:

TABLE STATUS AFTER SHIPMENT

SHIPMENT ORDER
ID	PRODUCT NAME	STATUS
1	LAPTOP	ACTIVE

SHIPMENT ORDER ITEMS
ID	SHIPMENT_ORDER_ID	ITEM	STATUS
1	1	LAPTOPTABLE	SHIPPED
2	1	LAPTOP FAN	ACTIVE

WAREHOUSE ORDER
ID	PRODUCT NAME	STATUS
1	LAPTOP	ACTIVE

WAREHOUSE ORDER ITEMS
ID	WAREHOUSE_ORDER_ID	ITEM	STATUS
1	1	LAPTOPTABLE	SHIPPED
2	1	LAPTOP FAN	ACTIVE

Now I wanted to use a functionality by the help of which I could change the status of warehouse_order_items automatically on changing the shipment_order_items. So I decided to use triggers to solve this problem.
Here in the shipment order process whenever the changes will be done in a shipment table than through the use of triggers same changes would appear simultaneously in the consequent (Warehouse) table. Similarly whenever we will update the status of a shipment order than the automatic change would appear in the warehouse status.
Here the trigger execution is explained for the UPDATE operation but as mentioned earlier triggers can be used for INSERT and DELETE events also.

Step 1: Create a “shipment_order_items” table.
CREATE TABLE shipment_order_items
(
id serial NOT NULL,
weight real,
weight_type character varying(5) NOT NULL,
description character varying(255),
status character varying(30),
warehouse_order_item_id bigint,
CONSTRAINT warehouse_order_items_pkey

PRIMARY KEY (id )
)

Step 2: Now create an “warehouse_order_items” table
CREATE TABLE warehouse_order_items
(
id serial NOT NULL,
weight real,
weight_type character varying(5) NOT NULL,
description character varying(255),
status character varying(30),
CONSTRAINT warehouse_order_items_pkey PRIMARY KEY (id )
)

We can use triggers for single row or multiple rows. Its up to us when we want to fire a trigger BEFORE or AFTER the event.
The following table summarizes which types of triggers may be used on tables and views:
When Event Row-level Statement-level
BEFORE INSERT/UPDATE/DELETE Tables Tables and views
TRUNCATE – Tables
AFTER INSERT/UPDATE/DELETE Tables Tables and views
TRUNCATE – Tables
INSTEAD OF INSERT/UPDATE/DELETE Views –
TRUNCATE – –

Step 3: Next we create a trigger between “shipment_order_items” table and “warehouse_order_items” table
Before creating a trigger we have to make a procedure and after that we can call that procedure in our trigger.
/*Start Procedure*/
CREATE FUNCTION update_status() RETURNS TRIGGER AS $_$
BEGIN
Update warehouse_order_items SET status = NEW.status WHERE
warehouse_order_items.id = OLD.warehouse_order_item_id;
RETURN OLD; //OLD goes for PREVIOUS value and NEW goes for LATEST entered value
END $_$ LANGUAGE 'plpgsql';
/*End Procedure*/
/*Create trigger*/
CREATE TRIGGER trigger_name AFTER UPDATE ON shipment_order_items
FOR EACH ROW EXECUTE PROCEDURE update_status();
/*End trigger*/

Now, our trigger is set and associated with shipment_order_items table. When its status will update then it will update warehouse_order_items status automatically.