CODEWITHSUNDEEP
sqldatabasepostgresql
SteveDA
SteveDA

Reputation: 13

Tracking Changes on insert or update or delete on a table for PostgreSQL using trigger

I am creating a table to track changes however it is returning the following error:

"(psycopg2.InternalError) control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function change_trigger()"

Whenever I perform any of the following actions : insert, change or update

Please see below table and trigger function

Create table for logging changes

Create schema logging;
Create table logging.history(
            id serial,
            tstamp timestamp default now (),
            schemaname text,
            tabname text, 
            operation text, 
            who text default current_user,
            new_val json,
            old_val json);

Create function

CREATE function
change_trigger()
RETURNS trigger as $$
BEGIN
        IF TG_OP = 'Insert'
        THEN
            Insert into logging.history (tabname, schemaname, operation, new_val)
            Values (TG_TABLE_NAME,TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
            RETURN NEW;
        ELSIF TG_OP = 'Update'
        THEN
            Insert into logging.history (tabname, schemaname, operation, new_val, old_val)
            Values (TG_TABLE_NAME,TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD));
            RETURN NEW; 
        ELSIF TG_OP = 'Delete'
        THEN
            Insert into logging.history (tabname, schemaname, operation, old_val)
            Values (TG_TABLE_NAME,TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
            RETURN OLD;
    END IF;
    END;
    $$ LANGUAGE 'plpgsql';

Link Trigger function to table:

CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON mytemp

   FOR EACH ROW EXECUTE PROCEDURE change_trigger();

For example, when I perform the following:

INSERT into mytemp(column1)
    VALUES ('id2323900')

the error returns as:

control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function change_trigger()

Upvotes: 1

Views: 2961

Answers (1)

Дмитрий Миляев
Дмитрий Миляев

Reputation: 56

Names of tg_op are ('INSERT', 'UPDATE', 'DELETE') not ('Insert', 'Update', 'Delete') - case sensitive, OR change TG_OP = 'Insert' to TG_OP ilike('insert'). And I would add 'else' statement at the end with notice:

ELSE 
    NOTICE RAISE 'Unknown tg_op';
    RETURN OLD;
END IF;

Upvotes: 4

Related Questions