sqlfirebirdfirebird2.5

How to detect if a trigger is fired for an insert or update?


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?


Solution

  • 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);