Creating trigger functions / Assigning triggers

[OliverTr54]

Issue type:

[x] question [ ] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [x] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Hey folks,

I'm currently playing around with NestJS building an API and I am also using TypeORM for the entities and creating the tables - which is all working fine - no issues at all.

But - on my database side ...I'm using Postgres - I would also like to create triggers once the tables are created or synced, e.g. to fill up my audit_log. How can I do this - or for when is this feature planed?

Thanks in advance, Oliver

[leesiongchan]

Related to #1082

[silentrobi]

Did you find any solution for that ?

[alifarooq0]

Any progress on this? Need to create a trigger func + trigger for each row on postgres using typeorm.

[lukemovement]

If anyone is still looking for a solution, this is what I'm using

export class AppModule implements OnApplicationBootstrap {
  constructor(@InjectDataSource() private readonly dataSource: DataSource) {}

  public async onApplicationBootstrap(): Promise<void> {
    await Trigger.drop(this.dataSource);
    await Trigger.sync(this.dataSource);
  }
}

import { DataSource } from "typeorm";

export class Trigger {
  public static readonly TRIGGERS: Array<Trigger> = [];

  public static async drop(dataSource: DataSource): Promise<void> {
    const triggers = await dataSource.query(`
      SELECT TRIGGER_NAME
      FROM information_schema.triggers
      WHERE trigger_schema = DATABASE()
    `);
    for (const trigger of triggers) {
      console.log(`Dropping trigger ${trigger.TRIGGER_NAME}...`);
      await dataSource.query(`DROP TRIGGER ${trigger.TRIGGER_NAME}`);
    }
  }

  public static async sync(dataSource: DataSource): Promise<void> {
    for (const trigger of Trigger.TRIGGERS) {
      console.log(`Creating trigger ${trigger.name} on ${trigger.table}...`);
      await dataSource.query(trigger.getSQL());
    }
  }

  //////////////////////////////////////////

  public readonly name: string;
  public readonly event: "INSERT" | "UPDATE" | "DELETE";
  public readonly state: "BEFORE" | "AFTER";
  public readonly table: string;
  public readonly sql: string;

  constructor({
    name,
    event,
    state,
    table,
    sql,
  }: {
    name: string;
    event: "INSERT" | "UPDATE" | "DELETE";
    state: "BEFORE" | "AFTER";
    table: string;
    sql: string;
  }) {
    this.name = name;
    this.event = event;
    this.state = state;
    this.table = table;
    this.sql = sql;

    Trigger.TRIGGERS.push(this);
  }

  public getSQL(): string {
    return `
      CREATE TRIGGER ${this.name}
      ${this.state} ${this.event} ON ${this.table}
      FOR EACH ROW
      BEGIN
        ${this.sql}
      END;
    `;
  }
}