![](https://res.cloudinary.com/practicaldev/image/fetch/s--UKxORtQV--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://blog.hasura.io/content/images/downloaded_images/postgres-triggers-on-graphql-mutations-682bf48db023/1-BPEyAMkUQVEY5FnvmAPBRw.jpeg)
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 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:
- Perform some validation on the server-side. For e.g. in a banking application, validate that deposits cannot be than 0 or lesser.
- 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:
-- Create the function | |
CREATE FUNCTION insert_deposit() | |
RETURNS trigger AS $BODY$ | |
DECLARE active_account BOOLEAN; | |
BEGIN | |
IF NEW."deposit_amount" <= 0 THEN | |
RAISE EXCEPTION 'Deposit amount must be greater than 0'; | |
END IF; | |
SELECT a.is_active INTO active_account FROM "account_savings" a WHERE a.account_no = NEW."account_no"; | |
IF active_account != TRUE THEN | |
RAISE EXCEPTION 'Account must be active'; | |
END IF; | |
RETURN NEW; | |
END; | |
$BODY$ LANGUAGE plpgsql; | |
--- Create the trigger with the above function | |
CREATE TRIGGER insert_deposit BEFORE INSERT OR UPDATE ON "deposit_activity" FOR EACH ROW EXECUTE PROCEDURE insert_deposit(); |
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:
-- the function to call when the trigger is invoked | |
CREATE FUNCTION trigger_on_note_revision() | |
RETURNS TRIGGER | |
LANGUAGE PLPGSQL AS $BODY$ | |
BEGIN | |
-- Create revision only if node's subject or body columns have changed | |
IF OLD.title <> NEW.title OR OLD."data" <> NEW."data" THEN | |
INSERT INTO note_revision (note_id, created_at, title, "data") | |
VALUES (OLD.id, OLD.updated_at, OLD.title, OLD."data"); | |
NEW.updated_at = now(); | |
END IF; | |
-- Return the NEW record so that update can carry on as usual | |
RETURN NEW; | |
END; $BODY$; | |
-- create the trigger | |
CREATE TRIGGER trigger_note_revision | |
BEFORE UPDATE | |
ON note | |
FOR EACH ROW | |
EXECUTE PROCEDURE trigger_on_note_revision(); |
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:
Conclusion
Postgres triggers can be used effectively to trigger operations/functions on the backend.
Top comments (0)