Ah, it's been a while I wrote about something. Been a busy year, so much to do, and so much learnt.
In this article, I will try to explain what a database trigger is. When to use it, and a practical example of it.
Definition:
A database trigger is a procedural code that is automatically executed in response to certain events on a particular table or view on a database.
Usage:
Recently, I was working on a discord bot, at some point in the development processes, we needed a way to notify the discord users, of the status of their transaction. Because we had a transactions table, with a status column, one way i could do this was to use a database trigger.
I am sure there are other solutions to this problem, but this was the solution I went with.
Setup:
So I set up a trigger to listen for UPDATE events in the transactions table. Here is the code required for doing this.
CREATE FUNCTION notify_transaction_status_change() RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify('transaction_status_changed', json_build_object(
'operation', TG_OP,
'record', row_to_json(NEW)
)::text
);
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
In the above code, we are creating a database function, that would reference the trigger named, transaction_status_changed.
Below is the script for the trigger.
CREATE TRIGGER transaction_status_changed
AFTER UPDATE
ON transactions
FOR EACH ROW
EXECUTE PROCEDURE notify_transaction_status_change()
The trigger gets called after every update on the transactions table, for each row. So if you have 10 rows on the transactions table, if an update is performed on them, this trigger would run for each.
Now the next thing to do would be to listen for this event triggers, get the payload from the event and do what whatever with it, in our case, we will be notifying the user who owns the transaction.
We would need to create a connection to the database. With this connection created, the application would have to listen for events.
Find the script I used in achieving this below;
function doConnectionSetupStuff(connection: any, knex: any, client: Client) {
connection.query('LISTEN transaction_status_changed');
connection.on('notification', (msg: { channel: string; payload: string }) => {
const jsonResponse = JSON.parse(msg.payload);
//notify user when status of transaction is confirmed
if (jsonResponse.record.status === 'confirmed') {
notifyUsers(jsonResponse, client);
}
});
connection.on('end', () => {
reconnectClient(knex, client);
});
connection.on('error', (err: any) => {
logger.error(err);
});
}
const PostgresNotificationListener = (knex: Knex, client: Client): void => {
knex.client
.acquireRawConnection()
.then((connection: any) => {
logger.info('Listening for events in Database');
doConnectionSetupStuff(connection, knex, client);
})
.catch((e: any) => {
logger.error(e);
});
};
function reconnectClient(knex: any, client: Client) {
const fn = setInterval(() => {
try {
knex.client
.acquireRawConnection()
.then((connection: any) => {
doConnectionSetupStuff(connection, knex, client);
clearInterval(fn);
})
.catch((e: any) => {
logger.error(e);
});
console.log('connected to DB');
} catch (e) {
logger.error(e);
}
}, 3000);
}
PostgresNotificationListener();
At the time of this writing, I was making use of [Knex], (http://knexjs.org/), PostgreSQL and Typescript.
The script creates a connection to the database, and when this connection ends, it reconnects the script to the database, so that it's constantly listening for trigger events.
And when these events are received and certain conditions are met, the payload gets cleaned up and presented to a user or be used in making a logical decision within the application.
There are plenty other use-cases for database triggers. One other use could be enforcing rules for certain columns in a table.
** Quick note:
if you are making use of Knex, here is how to setup the trigger and function in your table migration.
import * as Knex from 'knex';
export async function up(knex: Knex): Promise<void> {
// Create table
await knex.schema.createTable('transactions', table => {
table.increments();
table.string('type').notNullable();
table.uuid('sender'); // Sender id (or null if type = withdrawal)
table.uuid('recipient'); // Recipient id (or null if type = deposit)
table.string('withdraw_address');
table.decimal('amount', 18, 8).notNullable();
table.string('hash'); // Transaction hash if type = deposit or withdrawal
table.string('status').defaultTo('pending');
table.timestamp('created_at', { useTz: false }).defaultTo(knex.fn.now());
table.timestamp('updated_at', { useTz: false }).defaultTo(knex.fn.now());
});
// Create function/trigger
await knex.raw(
`
CREATE FUNCTION notify_transaction_status_change() RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify('transaction_status_changed', json_build_object(
'operation', TG_OP,
'record', row_to_json(NEW)
)::text
);
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
`,
);
// Assign trigger
await knex.raw(`
CREATE TRIGGER transaction_status_changed
AFTER UPDATE
ON transactions
FOR EACH ROW
EXECUTE PROCEDURE notify_transaction_status_change()
`);
}
export async function down(knex: Knex): Promise<void> {
await knex.raw('DROP TRIGGER IF EXISTS transaction_status_changed ON transactions');
await knex.raw('DROP FUNCTION IF EXISTS notify_transaction_status_change CASCADE');
await knex.schema.dropTableIfExists('transactions');
}
Triggers are a powerful feature that can improve the efficiency of any application using a database. I hope someone finds it useful like I did.
If you have any questions feel free to ask on the comment section or reach out to me on Twitter @devopsjay
cover image from https://www.digitalocean.com/community/tutorials/understanding-relational-databases
Top comments (0)