CODEWITHSUNDEEP
postgresqltriggersdatabase
Patrick
Patrick

Reputation: 73

postgresql-using trigger to fire

I want to create a trigger so that whenever I make a change (Update or Delete) it should copy the old data to a new table (with same template).

I tried this code:

create table restrictions(ID int,name text);

insert into restrictions values(122,'suresh');

select * from restrictions;

create table restrictions_deleted(ID int,name text);// this is my duplicate table for keeping information of all updations.

CREATE OR REPLACE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
       INSERT INTO restrictions_deleted VALUES(OLD.ID, OLD.name);
       RETURN OLD;
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER moveDeleted
BEFORE DELETE ON restrictions 
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();

delete from restrictions where ID=122;
select * from restrictions_deleted;

This code is capable of recording all the deleted data into duplicate table. But I want to do same for updates also.

Any suggestion, any idea?

Upvotes: 0

Views: 150

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28521

First - in a trigger function you need to RETURN NEW; instead of RETURN OLD;.

Second - change the trigger to BEFORE DELETE OR UPDATE.

Last - it is better to have AFTER DELETE OR UPDATE for a logging trigger. This way it wont do useless work, when the change is rolled back.

BTW here is a good example of logging/audit trigger.

UPDATE:

The function will look like:

CREATE OR REPLACE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO restrictions_deleted VALUES(OLD.ID, OLD.name);
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO restrictions_deleted VALUES(OLD.ID, OLD.name);
        RETURN OLD;
    END IF;
    END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions