#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Trigger NEW problems inserting


    Hi all
    anyone know how to fix this?

    I would like to delete all data match the md5() criteria.
    the function
    CREATE OR REPLACE FUNCTION dup_trigger()
    RETURNS trigger AS
    $BODY$
    BEGIN
    DELETE FROM target_data WHERE
    dup_id=md5(NEW.type_id::text||NEW.country_id::TEXT||NEW."Date"::text);
    INSERT INTO scriptcase.target_data VALUES (NEW.*,md5(NEW.type_id::text||NEW.country_id::TEXT||NEW."Date"::text));
    Return NEW;
    END;
    $BODY$
    LANGUAGE plpgsql;

    and the error message:

    [Err] ERROR: record "new" has no field "type_id"
    CONTEXT: SQL statement "DELETE FROM target_data WHERE
    OLD.dup_id=md5(NEW.type_id::text||NEW.country_id::TEXT||NEW."Date"::text)"
    PL/pgSQL function dup_trigger() line 2 at SQL statement

    please help I'm stuck

    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    15
    Rep Power
    0

    Wink


    Hi,

    Since you didn't show the structure of your table, I think the column type_id is not existing on the table where you put your trigger.


    Please see this example.

    CREATE TABLE aav (id int, name text);
    INSERT INTO aav SELECT 1,'drew';

    CREATE TABLE aav2 (id int, name text, type_id int);
    INSERT INTO aav2 SELECT 1,'drew', 1;


    CREATE OR REPLACE FUNCTION aav_update() RETURNS TRIGGER AS $$
    BEGIN
    IF TG_OP='UPDATE' THEN
    UPDATE aav2 SET name=a.name from aav a where a.id=aav2.id and a.id=new.type_id;
    END IF;
    END;
    $$LANGUAGE PLPGSQL;

    CREATE TRIGGER aav_trigger_update AFTER UPDATE ON aav FOR EACH ROW execute procedure aav_update();

    UPDATE aav SET name='andrew' WHERE id=1;
    ERROR: record "new" has no field "type_id"

    It returned error because type_id is not existing on table aav where I created the trigger.

    Hope this gives you an idea.

IMN logo majestic logo threadwatch logo seochat tools logo