The Distributed SQL Blog

Thoughts on distributed databases, open source and cloud native

Getting Started with PostgreSQL Triggers in a Distributed SQL Database

VP Developer Relations

Triggers are a basic feature that all monolithic SQL systems like Oracle, SQL Server and PostgreSQL have supported for many years. They are very useful in a variety of scenarios ranging from simple audit logging, to advanced tasks like updating remote databases in a federated cluster. In this blog, we’ll look at examples of INSERT, UPDATE and INSTEAD OF triggers in Yugabyte DB.

What’s Yugabyte DB? It is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. Yugabyte’s SQL API (YSQL) and drivers are PostgreSQL wire-protocol compatible.

It’s simple to get up and running with a local cluster of Yugabyte DB by following the instructions in our quickstart. Also, we should note that no other truly distributed SQL database like Google Spanner or CockroachDB currently support any type of trigger.

What is a database trigger?

A trigger is a function that automatically gets executed when certain conditions are met or an event specified by the user occurs. The actions executed within a trigger could be anything ranging from insertion of data to the database, updating and deleting data rows, and more. Triggers are typically tied to a database table and created at table creation time. In short, a trigger behaves like an IF condition in C/C++ wherein if the aforementioned condition is true, then and only then, will the instructions inside the condition get executed.

Why use triggers?

Let’s say we have an employees table with employee and salary details. In this simple scenario, triggers can be useful in automating redundant tasks such as updating audit logs whenever an employee’s personal details or salary are updated. In this case, manually updating the logging table and populating it with before and after values allows for input errors and potentially malicious manipulation. Triggers can be used to automate this task which is hugely beneficial if the updates happen frequently and audit logging must be done immediately, without allowing an opportunity for tampering.

Example: INSERT and UPDATE Triggers

Insert and update triggers work as the name would suggest. The trigger if fired whenever an INSERT or UPDATE occurs on a table.

In the example below we’ll create two tables: employees and trigger_logs. Then create two triggers which update the trigger_logs table when either a new entry is added to the employees table or if the salary status of a particular employee is changed.

First let’s create the employees and trigger_logs table:

Next, let’s create the logging function:

Now, let’s create two triggers. One that creates an audit trail when an INSERT happens in the employees table, and one when data in the table is updated.

Next, let’s make an insert to the employees table.

When we then select data out of the employees table we can see that Paul’s employee details have been correctly inserted.

If we view the data in the trigger_logs table we can see the entry concerning Paul getting inserted into the table.

Next, we’ll update Paul’s record and set his salary status from “pending” to “paid” using the UPDATE statement below.

Let’s verify that his salary status did indeed change.

Now, when we query the trigger_logs table to ensure the update was logged:

Example: INSTEAD OF Triggers

The INSTEAD OF trigger is used only for INSERT, UPDATE, or DELETE on a view. They are called “INSTEAD OF” triggers because the database executes the trigger “instead of” running the triggering statement. Let’s look at a quick example of them in action.

First, let’s create a simple view that includes the ten employees whose id is between 1 and 10 of which Paul is a member.

Now, let’s create a function that prohibits the updating the salary of employees.

Next, create the INSTEAD OF trigger which will be invoked when someone tries to update the salary of an employee who belongs to the ten_employees view.

Finally, let’s insert some data into the employees table to test whether or not is is possible to change the salary of an employee by updating the view. In this case we are going to attempt to give Paul a new salary of $100,000,000.

As you can see, the trigger on the view effectively prevented the change in salary.

Trigger Types Currently Not Supported

The following trigger types are currently not supported, but you can track them via these GitHub issues:

What’s Next?

  • Compare Yugabyte DB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
  • Get started with Yugabyte DB on macOS, Linux, Docker, and Kubernetes.
  • Contact us to learn more about licensing, pricing or to schedule a technical overview.

Related Posts

VP Developer Relations