1

I need to make a table in my database that has all of the history of all DML statements that occur in a given table. The LOG table is like this:

id event ts record
1 update 2020-01-01 record1
2 delete 2020-01-02 record2

LOG table has a sequence for auto-incrementing the ID column and TS default is NOW.

Do I need to create a sequence for every statement first or I can just create triggers for AFTER a DML statement is fired?

CREATE TRIGGER UPDATE
  ACTIVE AFTER INSERT OR UPDATE
  ON TableA
AS
BEGIN
  INSERT INTO LOG (event,record)
     SELECT record FROM TableA; # what I need to add here to set the event = UPDATE,INSERT or DELETE?
END

Can I use something like this in my triggers with a statement to add an event values?

EDIT: This is the working solution thanks to @Mark Rotteveel and @Arioch'The:

CREATE TRIGGER INSERT_UPDATE FOR TableA
ACTIVE AFTER INSERT OR UPDATE
AS
BEGIN
  IN AUTONOMOUS TRANSACTION DO
    INSERT INTO LOG(event,record)
        VALUES (CASE WHEN inserting THEN 'INSERTED' WHEN updating THEN 'UPDATED' END, new.record);
END
Tony
  • 618
  • 12
  • 27
  • 2
    since it is about logging you might want to do it `in autonomous transaction` so the log record would not disappear in case some error happens and rolls back the logging transaction – Arioch 'The Mar 22 '21 at 18:40
  • @Arioch'The Only if it makes sense for the logging of the update event to be retained even if the actual update itself was rolled back. – Mark Rotteveel Mar 24 '21 at 11:35

1 Answers1

2

To detect the type of event, you can use the INSERTING or UPDATING (or DELETING) context variables.

As an aside, do not use SELECT record FROM TableA in a trigger on TableA, instead use the NEW context (or OLD for a delete).

The solution would look something like this:

insert into LOG (event, record) 
  values (case when :inserting then 'insert' when :updating then 'update' end, new.record);
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197