Interesting 5 Functions and 5 Triggers With PostgreSQL

In this post, I will take you through the process of creating triggers and functions. I prepeared 5 examples of different interesting triggers that would make you understand how triggers work. A trigger is a special type of stored procedure that fires automatically based on an event (e.g. Insert, update, delete, etc) occurring on a specific table or view.

In my previous posts of teaching data analysis with SQL, you will find me referring to a dvdrental Database. I will be using 2 tables from that database, the customer table and the rental table:

customer(customer_id, first_name, last_name, address_id)

rental(rental_id, rental_date, customer_id, inventory_id)

I will create a reservation table in the dvdrental Database. This table will be used to reserve DVD’S for customers.

I will alter the reservation table and add primary key and foreign keys.

Once the DVD becomes available for rent, a rental record should be added to the rental table and the reservation record should be removed form the reservation table.

I will add a reservation record in the reservation table. Customer with id = 1 will reserve a dvd with an inventory_id = 12.

If I select from the reservation table, I will find a new row in the reservation table.

I will start now taking you through creating 5 triggers and 5 functions that will be executed within those triggers.

Example 1: I would like to create a trigger that will delete a customer’s reservation record once the customer’s rents the DVD. In this case, I will create a trigger on the rental table (ON rental) that will be triggered when a row gets inserted (AFTER INSERT) in the rental table. The trigger will execute delete_reserve() function. The delete_reserve() function will be responsible of deleting the reservation record once the customer has rented the reserved DVD (EXECUTE PROCEDURE delete_reserve()).

I will use the following syntax:

The delete_reserve() will be executed within a trigger(returns TRIGGER). The function will delete the reservation row where the customer_id and the inventory_id in reservation is equivalent to those inserted in the rental table. The new inserted row can be accessed from (NEW) as NEW.customer_id and NEW.inventory_id.

The function at the end has:

RETURN NULL, because I would like to ignore the result since it is a trigger that gets triggered on an AFTER event.

I will add a row now to the rental table, of a customer_id = 1 and inventory_id = 12. That operation should result in firing the “delete_reservation” trigger because it is set to trigger after an insert happens on the rental table.

If the trigger worked properly, this row should have been deleted and we should find the table empty.

As you see above, the reservation row that was inserted before is no longer there because the customer is renting the DVD already.

Example 2: I want to use a trigger to prevent a customer for reserving more than 3 DVD’s. In order to do that, I will create a trigger on the reservation table (ON reservation) that will be triggered before a row gets inserted (BEFORE INSERT) in the reservation table. The trigger will execute count_dvd() function (EXECUTE PROCEDURE count_dvd()). The count_dvd() function will be responsible for checking if the customer has already reserved 3 DVDs.

I will insert a reservation record for customer_id =1, four times.

I will insert the first row in reservation table:

I will insert the second row in reservation table:

I will insert the third row in reservation table:

On an atempt to insert a forth DVD reservation for customer_id 1, the trigger will raise an error “This customer already is reserving 3 DVD, that is enough for now”.

The following error will be raised:

Example 3: I want to create a trigger to prevent removing any customer’s information, if the customer has rentals in the rental table not returned yet. In order to do that, I will create a delete_customer trigger on the customer table (ON customer) that will be triggered before a row gets deleted (BEFORE DELETE) from the customer table. The trigger will execute check_rental()function (EXECUTE PROCEDURE check_rental()). The check_rental() will be responsible for checking if the customer has rented DVD(s) not returned yet.

Attempting to delete a customer from the customer table, will trigger the delete_customer trigger. The delete_customer trigger will execute the check_rental() function. The check_rental() function will check whether the customer has any DVD(s) on rent. If the customer is not renting any DVDs his information will be deleted, otherwise his record won’t be removed.

The following error will be raised:

Example 4: I want to create a trigger to prevent updating the inventory_id in the reservation table with an inventory_id that belongs to store_id 2. I will create a check_update trigger on the reservation table (ON reservation) that will be triggered before a row gets updated (BEFORE UPDATE). The trigger will execute the check_inventory_update function if the inventory_id is updated (WHEN (OLD.inventoy_id IS DISTINCT FROM NEW.inventoy_id)). The check_inventory_update() will be responsible to not allow updating an inventory_id with inventory_ids for store_id’s 2.

The following is an attempt to update an inventory_id with an inventory_id that belongs to store_id 2.

The following error will be raised:

Example 5: I would like create a trigger that will help me keep track of all operations performed on the reservation table. I want to record whether an insert, delete or update occurred on the reservation table. The first step that I will do is to create a table to record the operations, I will call the table reservation_audit. I will store the first letter of the operation (I for Insert, U for Update and D for Delete), the timestamp of the operation, and the values of the operation.

Here is the definition of the reservation_audit:

I will create a reservation_audit trigger on the reservation table (ON reservation) that will be triggered after Insert, Update or Delete (AFTER INSERT OR UPDATE OR DELETE).

The trigger will call the process_reservation_audit function. The process_reservation_audit() will be responsible to record in the reservation_audit table the transaction performed whether it is an Insert, Update or a Delete.

I will insert a record in the reservation table:

I will select from the reservation and the reservation_audit tables. We will see a new record in the reservation table. We will alo see that a new record is added in the reservation_audit table indicating that an insert (I) operation has occurred in the reservation table.

I will delete a record from the reservation table.

A row is removed from the reservation table. A new record is added in the reservation_audit table indicating that a delete (D) operation has occurred on the reservation table.

I will update a record in the reservation table.

A row is updated in the reservation table. A record is added in the reservation_audit table indicating that an update (U) operation has occurred on the reservation table.

I hope you found the post interesting and that I was able to demonstrate the subject in a good and clear way.