Getting Started with PostgreSQL Triggers in a Distributed SQL Database

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:

CREATE TABLE employees 
             ( 
               id INT PRIMARY KEY NOT NULL, 
               NAME TEXT NOT NULL, 
               age     INT NOT NULL, 
               address CHAR(50), 
               salary  REAL, 
               salary_status TEXT NOT NULL 
             );

CREATE TABLE trigger_logs 
             ( 
	         PRIMARY KEY (emp_id, entry_timestamp),
               emp_id INT NOT NULL, 
               entry_timestamp TEXT NOT NULL, 
               salary_status TEXT NOT NULL 
             );

Next, let’s create the logging function:

CREATE OR REPLACE FUNCTION triggerlogfunc() returns TRIGGER AS $example_table$ 
BEGIN 
INSERT INTO trigger_logs 
              ( 
                emp_id, 
                entry_timestamp, 
                salary_status 
              ) 
              VALUES 
              ( 
                new.id, 
                CURRENT_TIMESTAMP, 
                new.salary_status 
              );

RETURN new;
END;
$example_table$ language plpgsql;

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.

CREATE TRIGGER example_trigger after 
INSERT ON employees FOR each row 
EXECUTE PROCEDURE triggerlogfunc();

CREATE TRIGGER example_trigger1 after 
UPDATE OF salary_status 
ON employees FOR each row 
EXECUTE PROCEDURE triggerlogfunc(); 

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

INSERT INTO employees 
            ( 
              id, 
              NAME, 
              age, 
              address, 
              salary, 
              salary_status 
            ) 
            VALUES 
            ( 
              1, 
              'Paul', 
              32, 
              'California', 
              20000.00, 
              'Pending' 
            ); 

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

SELECT * FROM employees;

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

SELECT * FROM trigger_logs;

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

UPDATE employees 
SET    salary_status='Paid' 
WHERE  id=1;

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

SELECT * FROM employees;

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.

CREATE VIEW ten_employees AS SELECT * FROM employees WHERE id BETWEEN 1 AND 10;

SELECT * FROM ten_employees;

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

CREATE FUNCTION triggerfunc2() 
returns TRIGGER language plpgsql 
SET search_path TO PUBLIC AS $ 
BEGIN 
  NEW.salary = OLD.salary; 
  return NEW; 
end; 
$;

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.

CREATE TRIGGER trigger2 instead OF 
UPDATE
ON ten_employees FOR each row 
EXECUTE PROCEDURE triggerfunc2(); 

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.

UPDATE ten_employees SET salary = '100000000' WHERE name = 'Paul';

SELECT * FROM employees;

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:

• Event triggers
• Deferrable constraints
• Referencing clause for triggers

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.