Postgres triggers on GraphQL mutations

- 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.
- Before the operation.
- After the operation.
- Instead of the operation.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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(); |
mutation deposit {
insert_deposit_activity(objects:[{
account_no: 1,deposit_amount: 1000 }]
) {
affected_rows
}
}
mutation deposit {
insert_deposit_activity(objects:[{
account_no: 1, deposit_amount: 0 }]
) {
affected_rows
}
}
{
"error": {
"exec_status": "FatalError",
"hint": null,
"message": "Deposit amount must be greater than 0",
"status_code": "P0001",
"description": null
}
}
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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(); |
mutation {
insert_note (objects: [{
user_id: 1, title: "my first note", data: "some long note data i have"}]
) {
affected_rows
}
}
mutation {
update_note (where: {id: {_eq: 1}}, _set: {title: "My new note", data: "some other note data"}){
affected_rows
}
}

Related reading