Triggers

An example of adding triggers to my ORM system

Triggers in databases allow automatic execution of specific actions when certain events occur, such as inserting, updating, or deleting records. In my ORM system, triggers can be created using the @Trigger decorator.

Example 1: Trigger for the tasks Table

In this example, the trigger fires before updating a record in the tasks table. The trigger executes a function that checks or modifies data before the update.

import {Trigger, TriggerEventsTypes, TriggerTimingsTypes, Table} from "@myroslavshymon/orm";

@Trigger({
    name: 'before_update_task',
    event: TriggerEventsTypes.UPDATE,
    timing: TriggerTimingsTypes.BEFORE,
    triggerFunction: `
        BEGIN
            IF NEW.is_completed THEN
                NEW.completed_at = CURRENT_TIMESTAMP;
            END IF;
            RETURN NEW;
        END;
    `,
    triggerFunctionName: 'set_completion_time'
})
@Table({name: 'tasks'})
export class Tasks {
    // поля таблиці tasks
}

Example 2: Trigger for the users Table

In this example, the trigger after_delete_user is set to fire after a record is deleted from the users table. This trigger uses a class with methods that return functions to handle the post-deletion logic.

import {Trigger, TriggerEventsTypes, TriggerTimingsTypes, Table} from "@myroslavshymon/orm";
import {Functions} from "./functions";

@Trigger({
    name: 'after_delete_user',
    event: TriggerEventsTypes.DELETE,
    timing: TriggerTimingsTypes.AFTER,
    functionName: 'logUserDeletion',
    functions: Functions,
    triggerFunctionName: 'log_user_deletion'
})
@Table({name: 'users'})
export class Users {
    // поля таблиці users
}

export class Functions {
    logUserDeletion() {
        return `
            BEGIN
                INSERT INTO audit_log (action, user_id, timestamp)
                VALUES ('DELETE', OLD.user_id, CURRENT_TIMESTAMP);
                RETURN OLD;
            END;
        `;
    }
}

In this example, the after_delete_user trigger executes the logUserDeletion method from the Functions class, which logs an entry in the audit trail after a user is deleted from the users table.

Triggers are a powerful tool for automating certain processes in the database, enhancing control and security when working with data. My ORM system provides flexibility in creating and managing triggers, allowing both direct function definitions in text form and using methods from separate classes for a more structured approach.