postgresql-using trigger to fire

Question

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?

And you're sure you want to replicate a whole table on an update operation? Have you considered to use a data model which records the historic information you need to know? — class stacker
– class stacker, Commented Mar 12, 2013 at 9:35
@ClassStacker The current trigger wont replicate whole table. It will only cope the current row. — Ihor Romanchenko
– Ihor Romanchenko, Commented Mar 12, 2013 at 10:00
@ClassStacker Please let me know if there is some more effective alternative as you said above. — Patrick
– Patrick, Commented Mar 12, 2013 at 10:13
It really all depends on your requirements (plus the specific data model you have chosen for your solution) and your infrastructure. But as @IgorRomanchenko pointed out, I... erm... read your post too quickly before I made the above comment. Still, I often end up marking records as deleted or expired if I want historic information. Storage space, performance, and access to historic information are key factors for the decision. — class stacker
– class stacker, Commented Mar 12, 2013 at 10:34

Ihor Romanchenko · Accepted Answer · 2013-03-12 15:04:27Z

1

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;

edited Mar 12, 2013 at 15:04

answered Mar 12, 2013 at 9:58

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Patrick Over a year ago

i worked with this what you told, however i want both update and delete with the same trigger.kindly provide a suggestion

Ihor Romanchenko Over a year ago

@user2160090 The AFTER DELETE OR UPDATE will work with both UPDATE and DELETE. Read the manual here for fusher information.

Patrick Over a year ago

however i tried but not working for both,if using the code for update means we have to give NEW, for delete we have to give as OLD, how to give the both using trigger. please refer the code as given above function.

Patrick Over a year ago

i tried it is working so good, thank you so much for the quick response

Collectives™ on Stack Overflow

postgresql-using trigger to fire

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related