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; however 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 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 used to update accumulators and to modify or delete records that belong to...

Technical requirements

Before starting, remember to start the Docker container named chapter_08, as shown below:

$ bash run-pg-docker.sh chapter_08
postgres@learn_postgresql:~$ psql -U forum forumdb

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, in the same transaction, perform another operation.

Understanding the OLD and NEW variables

Before we start working with rules and then with triggers, we need to...

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

Event triggers

Rules and triggers act as 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 statements. The purpose of the event trigger, therefore, is to manage and react to events that will 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 the completion of the DDL command...

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 the 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 to do so.

Verify your knowledge

• What is the NEW record?

  The NEW record is the record that is going to be processed before an INSERT statement or an UPDATE statement, for example:

  insert into mytable(id,city_name) values (1,'New York')
  

  	NEW.id = 1
  	NEW.city_name = 'New York'
  

  See the section Exploring rules in PostgreSQL for more details.

• Can we execute an INSERT on two tables in a single transaction using rules?

  Yes, we can; we can make it using the ALSO clause. See the section Exploring rules in PostgreSQL for more details.

• Can we make all the things we do with rules using triggers?

  Yes, we can; by using triggers, we can make all the things we do with rules and more. See the section Managing triggers in PostgreSQL for more details.

• Can we know if a trigger has been fired from an INSERT event, from an update EVENT, or from a DELETE event?

  Yes, we can, using the TG_OP variable...

References

• PostgreSQL rules on the INSERT, UPDATE, and DELETE official documentation: https://www.PostgreSQL.org/docs/current/rules-update.html
• PostgreSQL trigger functions official documentation: https://www.PostgreSQL.org/docs/current/plpgsql-trigger.html
• PostgreSQL ALTER TRIGGER official documentation: https://www.PostgreSQL.org/docs/current/sql-altertrigger.html
• PostgreSQL DROP TRIGGER official documentation: https://www.PostgreSQL.org/docs/current/sql-droptrigger.html
• PostgreSQL EVENT TRIGGER official documentation: https://www.postgresql.org/docs/current/functions-event-triggers.html

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku