1

I have this trigger (audit trigger, in every table I want to audit).

CREATE FUNCTION changelog_trigger() RETURNS trigger AS $$
    BEGIN
        IF TG_OP = 'INSERT'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, after, "userId"
                ) VALUES (
                TG_TABLE_NAME, TG_OP, row_to_json(NEW), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                );
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, before, after, "userId"
                ) VALUES (
                TG_TABLE_NAME,  TG_OP, row_to_json(OLD), row_to_json(NEW), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                    );
            RETURN NEW;
        ELSIF TG_OP = 'DELETE'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, before, "userId"
                ) VALUES (
                TG_TABLE_NAME, TG_OP, row_to_json(OLD), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                );
            RETURN OLD;
        END IF;
    END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Cities" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Buildings" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Areas" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Rooms" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Flags" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Details" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Files" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "DetailFiles" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Users" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Infrastructures" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Builds" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "DB_Backups" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();

This works perfectly, at least until now. Client wants to also update a record which states the last time anything was updated. I thought about adding a second insert after THEN. Is this the best approach? I need to update, in table Cities, column updatedAt, with the timestamp of the operation.

I thought about doing this but I want to be sure because this is a production server and I'm not a DBA by any stretch.

        IF TG_OP = 'INSERT'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, after, "userId"
                ) VALUES (
                TG_TABLE_NAME, TG_OP, row_to_json(NEW), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                );
            UPDATE "Cities" SET "updatedAt" = current_timestamp() where id = 1;
            RETURN NEW;

Is this correct?

Thanks in advance.

8
  • 1
    Is the trigger defined on the Cities table? Please edit your question and add the complete code of the function and the create trigger statement. (edit your question, please do not put code in comments)
    – user1822
    Feb 11, 2021 at 6:38
  • If the client wants to "update a record which states the last time", why insert?
    – mustaccio
    Feb 11, 2021 at 13:21
  • @mustaccio because it was 2 in the morning. It was a brainfart. I meant update.
    – Odin
    Feb 11, 2021 at 15:27
  • @a_horse_with_no_name I'll edit my question. The triggers are defined in every table, because I want them to trigger on every change on every table. Cities table doesn't have a trigger yet.
    – Odin
    Feb 11, 2021 at 15:28
  • Surely the trigger has to update the updatedAt column on the table it is being called from, and not always that on Cities? In that case you can just do new.updatedAt = now(); anywhere in the trigger body. Feb 11, 2021 at 16:37

0

Browse other questions tagged or ask your own question.