0

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();
sub
  • 527
  • 1
  • 7
  • 24

1 Answers1

1

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;
Syed
  • 76
  • 7
  • I tried DISABLE and ENABLE.. but that doesn't help... but dropping and creating both the trigger and procedure(function) worked.. – sub Jul 29 '18 at 20:25
  • 1
    Yes, the point is that the dependent objects should be recompiled. By dropping and creating, the definition of the trigger was updated and the database engine cache is overwritten. It's always better to have a process to determine any dependent objects of the most critical table. There're some good idea here https://stackoverflow.com/questions/4462908/find-dependent-objects-for-a-table-or-view – Syed Jul 29 '18 at 20:35