Postgres triggers on GraphQL mutations
Tags: graphql, tutorial, postgres, triggers
Published: 2019-12-22 05:59:05

---
title: Postgres triggers on GraphQL mutations
published: true
date: 2018-08-30 00:00:00 UTC
tags: graphql, tutorial, postgres, triggers
canonical_url: https://blog.hasura.io/postgres-triggers-on-graphql-mutations-682bf48db023/
---

![](https://blog.hasura.io/content/images/downloaded_images/postgres-triggers-on-graphql-mutations-682bf48db023/1-BPEyAMkUQVEY5FnvmAPBRw.jpeg)<figcaption>Postgres trigger on a GraphQL mutation in action</figcaption>

## TL;DR

Setup Postgres functions and triggers to perform backend operations like validation and inserting/updating other related data, whenever some inserts/updates happens on tables. Now when you use GraphQL mutations, [Postgres triggers](https://hasura.io/docs/latest/graphql/core/guides/postgres/triggers.html) run, performing the required backend operations.

## Introduction

There are cases where you would want run some custom backend function after an insert happens.

Some examples of this are:

1. Perform some validation on the server-side. For e.g. in a banking application, validate that deposits cannot be than 0 or lesser.
2. Insert some related data in a single transaction. For e.g. in a note taking application, create a revision whenever a note is updated.

## What is a Postgres trigger ?

A trigger is associated with a table or view and is fired whenever an event occur. The ‘events’ are INSERT, DELETE, UPDATE or TRUNCATE.

Trigger will eventually call a function which will automatically be invoked when a specific event occurs.

Triggers can be invoked in the following scenarios:

- Before the operation.
- After the operation.
- Instead of the operation.

## Validation on the server-side

Consider the following data model for a banking-like application:

`customer` — `id, name`

`account_savings` — `account_no, customer_id, is_active, created_at`

`deposit_activity` — `account_no, deposit_amount, deposit_time`

`withdraw_activity` — `account_no, withdraw_amount, withdraw_time`

So to demonstrate the first scenario, we will perform basic validation before an insert into `deposit_activity` happens. We want to ensure that insert doesn’t happen, if the `deposit_amount` is ≤ 0 or if the account is inactive.

Let’s create a trigger. You can copy-paste this in the “SQL” tab of Hasura console:

{% gist https://gist.github.com/ecthiender/f25e3bd1fe254a0ed20b63b0bc8d1224.js %}

Now to to make deposits into an account, we can just use this insert mutation, and the trigger will make sure that the amount is valid (≥0) and the account is not an inactive account.  

```
mutation deposit {
  insert_deposit_activity(objects:[{
    account_no: 1,deposit_amount: 1000 }]
  ) {    
    affected_rows  
    }
}
```

Making an insert where the account is inactive or amount \< 0 will throw a Postgres exception:  

```
mutation deposit {
  insert_deposit_activity(objects:[{
    account_no: 1, deposit_amount: 0 }]
  ) {
    affected_rows
  }
}
```

Results in:...

```
{
  "error": { 
    "exec_status": "FatalError",
    "hint": null,
    "message": "Deposit amount must be greater than 0",
    "status_code": "P0001",
    "description": null
  }
}
```

## Insert related data

Consider the following data model:

`user` — `id, username`

`note` — `id, user_id, title, data, created_at, updated_at`

`note_revision` — `note_id, created_at, title, data`

Whenever a user updates a note, we will insert into `note_revision` before updating the `note` table.

Create the following trigger:

{% gist https://gist.github.com/ecthiender/f472f606428847a56c305cc3edcc0779.js %}

Now let’s create a note:  

```
mutation {
  insert_note (objects: [{
    user_id: 1, title: "my first note", data: "some long note data i have"}]
  ) {
    affected_rows
  }
}
```

Now when we update that note:  

```
mutation {
  update_note (where: {id: {_eq: 1}}, _set: {title: "My new note", data: "some other note data"}){
     affected_rows
    }
}
```

A new row in the `note_revision` table will be created:

![](https://blog.hasura.io/content/images/downloaded_images/postgres-triggers-on-graphql-mutations-682bf48db023/1-0_48KQo9CzDH6eiUCmtgyQ.png)

## Conclusion

Postgres triggers can be used effectively to trigger operations/functions on the backend.