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

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.