Reputation: 852
I have a table with a trigger applied to it, that populates an audit table if it changes. I created the original table with just two fields:
CREATE TABLE events(
code serial8 NOT NULL,
event date NOT NULL,
);
And I want a trigger to populate the audit table whenever the event date on this first table is updated:
CREATE TABLE audit(
date_log date,
time_log time,
userid char(20),
event_code int,
action_log text,
old_date date,
new_date date
);
The trigger function:
CREATE OR REPLACE FUNCTION auditing() RETURNS trigger AS $$ BEGIN
INSERT INTO audit(date_log,time_log,userid,event_code,action_log,old_date,new_date)
SELECT current_date,current_time,current_user,code,tg_op,OLD.event, NEW.event FROM events;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
And I set the trigger like this:
CREATE TRIGGER audit_dates
AFTER UPDATE OF event ON events
FOR EACH ROW EXECUTE PROCEDURE auditing();
The problem I am having is that whenever I run an update function such as:
UPDATE events SET event = '2013-03-01' WHERE code = 1;
The original table updates just fine, but the table that should audit this, generates one audit row for each row that the original table had, so if the original table has 200 rows and I update just one of them, 200 new rows are generated in the audit table.
I am trying to think whether this is because I created the trigger as FOR EACH ROW
, but at the same time I have been reading the docs on this and it looks like this is the syntax I need.
What is the right way to get just one new row in the audit table for each time that I update the original table?
Upvotes: 2
Views: 826
Reputation: 22893
You have SELECT ... FROM events
in the trigger, so it's selecting all events. You can just use a simple INSERT ... VALUES
since NEW and OLD are variables.
Upvotes: 2