Trigger to keep track of table changes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger to keep track of table changes

ALT SHN
I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach:

    CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS
    $BODY$
    BEGIN
    IF TG_OP = 'DELETE' THEN
    INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
    VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
    RETURN old;
   
    ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
    VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
    RETURN old;
   
    ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
    VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
    RETURN old;
    END IF;
    END;
    $BODY$
    LANGUAGE plpgsql;

    CREATE TRIGGER history_taxon
    AFTER INSERT OR UPDATE OR DELETE ON taxon
    FOR EACH ROW EXECUTE PROCEDURE taxon_history();

However when something changes in the `taxon` table, no record is added to the `taxon_history` table. I also don´t get any error message so I am in the dark on why nothing is happening. What am I doing wrong?

--
---------------------------------------------------------------


Sociedade de História Natural
Departamento de Informa
ção Geográfica
Polígono Industrial do Alto do Amial
Pav.H02 e H06
Reply | Threaded
Open this post in threaded view
|

Re: Trigger to keep track of table changes

ALT SHN

n the end I solved this through 'Divide and conquer' - I split the trigger into three parts and now it works (though I am not sure yet why the original trigger does not work):

    CREATE OR REPLACE FUNCTION taxon_history_delete() RETURNS trigger AS 
$BODY$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION taxon_history_update() RETURNS trigger AS 
$BODY$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('UPDATE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION taxon_history_insert() RETURNS trigger AS 
$BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
        RETURN new;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

create TRIGGER taxon_history_delete
Before DELETE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_delete();

create TRIGGER taxon_history_update
Before UPDATE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_update();

create TRIGGER taxon_history_insert
AFTER INSERT ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_insert();

ALT SHN <[hidden email]> escreveu no dia sábado, 22/06/2019 à(s) 11:33:
I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach:

    CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS
    $BODY$
    BEGIN
    IF TG_OP = 'DELETE' THEN
    INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
    VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
    RETURN old;
   
    ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
    VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
    RETURN old;
   
    ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
    VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
    RETURN old;
    END IF;
    END;
    $BODY$
    LANGUAGE plpgsql;

    CREATE TRIGGER history_taxon
    AFTER INSERT OR UPDATE OR DELETE ON taxon
    FOR EACH ROW EXECUTE PROCEDURE taxon_history();

However when something changes in the `taxon` table, no record is added to the `taxon_history` table. I also don´t get any error message so I am in the dark on why nothing is happening. What am I doing wrong?

--
---------------------------------------------------------------


Sociedade de História Natural
Departamento de Informa
ção Geográfica
Polígono Industrial do Alto do Amial
Pav.H02 e H06


--
---------------------------------------------------------------


Sociedade de História Natural
Departamento de Informa
ção Geográfica
Polígono Industrial do Alto do Amial
Pav.H02 e H06


[hidden email]
www.shn.pt
www.alt-shn.blogspot.com