Triggers and Rules

In the previous chapter, we talked about server-side programming. In this chapter, we will use the concepts introduced in the previous chapter to manage the programming of events in PostgreSQL. The first thing we need to address is what an event in PostgreSQL actually is. In PostgreSQL, possible events are given by the SELECT/INSERT/UPDATE, and DELETE statements. There are also events related to data definition language (DDL) operations; we will talk about those events in Chapter 17, Event Triggers.

In PostgreSQL, there are two ways to handle events:

• Rules
• Triggers

In this chapter, we will explore both of these ways and address when it is more appropriate to use one of them rather than the other. As a starting point, we can generally say that rules are usually simple event handlers, while triggers are more complex event handlers. Triggers and rules are often...

Exploring rules in PostgreSQL

As mentioned earlier, rules are simple event handlers. At the user level, it is possible to manage all the events that perform write operations, which are as follows:

• INSERT
• DELETE
• UPDATE

The fundamental concept behind rules is to modify the flow of an event. If we are given an event, what we can do when certain conditions occur is as follows:

• Do nothing and then undo the action of that event.
• Trigger another event instead of the default one.
• Trigger another event in conjunction with the default.

So, given a write operation, for example, an INSERT operation, we can perform one of these three actions:

• Cancel the operation.
• Perform another operation instead of the INSERT.
• Execute the INSERT and simultaneously perform another operation.

Understanding the OLD and NEW variables

Before we start working with rules and then with triggers, we need to understand the concept of the OLD and NEW variables.

The OLD and NEW variables represent the state of the row...

Managing triggers in PostgreSQL

In the previous section, we talked about rules. In this section, we will talk about triggers, what they are, and how to use them. We need to start by understanding what triggers are; if we have understood what rules are this should be simple. In the previous section, we defined rules as simple event handlers, now we can define triggers as complex event handlers. For triggers, as for rules, there are NEW and OLD records, which assume the same meaning for triggers as they did for rules. For triggers, the manageable events are INSERT / DELETE / UPDATE and TRUNCATE. Another difference between rules and triggers is that with triggers it is possible to handle INSERT / UPDATE / DELETE / and TRUNCATE events before they happen or after they have happened. With triggers, we can also use the INSTEAD OF option, but only on views.

So we can manage the following events:

• BEFORE INSERT/UPDATE/DELETE/TRUNCATE
• AFTER INSERT/UPDATE/DELETE/TRUNCATE
• INSTEAD OF INSERT/UPDATE...

Event triggers

Rules and triggers act as Data Manipulation Level (DML) statements, which means they are triggered by something that changes the data but not the data layout or the table properties. PostgreSQL provides so-called event triggers, which are particular triggers that fire on DDL (Data Definition Level) statements. The purpose of the event trigger is therefore to manage and react to events that are going to change the data structure rather than the data content. Triggers can be used in many ways to enforce specific policies across your databases.

Once fired, an event trigger receives an event and a command tag, both of which are useful for introspection and providing information about what fired the trigger. In particular, the command tag contains a description of the command (for example, CREATE or ALTER), while the event contains the category that fired the trigger, in particular, the following:

• ddl_command_start and ddl_command_end indicate respectively the beginning and...

Summary

In this chapter, we covered the topic of triggers and rules. We explored rules and triggers using some identical examples. We established that rules are simple event handlers and triggers are complex event handlers.

We introduced the concept of trigger variables:

• NEW
• OLD
• TG_OP

As well as data-manipulation-based triggers, we briefly introduced the PostgreSQL event triggers that allow developers and database administrators to have more control over firing and executing functions.

We have come to understand that triggers are extremely complex event handlers. In this chapter, we started to show the power of these tools made available to the PostgreSQL DBA; in the next chapter, we will talk about partitioning and we will utilize the topics covered in this chapter.

In the next chapter, we will return to talking about triggers and we will use triggers to create a certain type of partitioning present in PostgreSQL.

References

• PostgreSQL 12 Rules on INSERT, UPDATE, and DELETE official documentation: https://www.PostgreSQL.org/docs/12/rules-update.html
• PostgreSQL 12 trigger functions official documentation: https://www.PostgreSQL.org/docs/12/plpgsql-trigger.html
• PostgreSQL 12 ALTER TRIGGER official documentation: https://www.PostgreSQL.org/docs/12/sql-altertrigger.html
• PostgreSQL 12 DROP TRIGGER official documentation: https://www.PostgreSQL.org/docs/12/sql-droptrigger.html
• PostgreSQL 12 event trigger official documentation: https://www.postgresql.org/docs/12/functions-event-triggers.html
• PostgreSQL 11 Server-Side Programming – Quick Start Guide: https://www.packtpub.com/big-data-and-business-intelligence/postgresql-11-server-side-programming-quick-start-guide