sqldatabasepostgresqltriggerspostgresql-triggers

How to solve returned row structure does not match the structure of the triggering table?

I have altered the transaction table column of varchar datatype's length from 50 to 100. Once I altered the length of the column.. trigger from parent table started failing.. Once I drop and create trigger, delete from master table function everything works fine.. Not sure what is the issue.. any justification?

Detail: Returned type character varying(50) does not match expected type character varying(100) in column 15.

CREATE FUNCTION customer_delete_master()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
     AS $BODY$

DECLARE
    r customer%rowtype;
BEGIN
    DELETE FROM ONLY customer where customer_id = new.customer_id returning * into r;
    RETURN r;
end;

$BODY$;

Update 1:

CREATE TABLE customer (
  customer_id character varying(10),
  firstname character varying(100),
  lastname character varying(50),
  CONSTRAINT customer_pkey PRIMARY KEY (customer_id)
);
    
CREATE TRIGGER after_insert_customer_trigger 
AFTER INSERT ON customer FOR EACH ROW
EXECUTE PROCEDURE customer_delete_master();
Solution

  • Any time a table having dependent objects like triggers & views is altered, the dependent objects should be recompiled. This is because the database engine accesses the dependent object's definition directly from the cache. For example in case of triggers, they can be disabled and enabled before and after the parent table is altered. Or you can just execute the alter trigger again.

    Disable:

    ALTER TABLE mytable DISABLE TRIGGER mytrigger;

    Enable:

    ALTER TABLE mytable ENABLE TRIGGER mytrigger;