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