When I update, insert or delete to one table, I need a trigger to delete and possibly insert into another table. The trigger I have works for insert and update, but returns an error when I do a delete.
The is no explicit link between the tables, so I can't do 'DELETE CASCADE' or anything like that.
Here is the trigger:
CREATE OR REPLACE FUNCTION event_tweet() RETURNS TRIGGER AS $textetweet_textetweet$ DECLARE new_id varchar; new_date date; BEGIN DELETE FROM textetweet_textetweet ; IF(TG_OP='INSERT') THEN DELETE FROM textetweet_textetweet WHERE link='/ievent/' || NEW.id; INSERT INTO textetweet_textetweet (date,title,content,link,creation) values (NEW.date,'new event','new event details','/ievent/' || NEW.id, NOW()); END IF; IF(TG_OP='UPDATE') THEN DELETE FROM textetweet_textetweet WHERE link='/ievent/' || NEW.id; INSERT INTO textetweet_textetweet (date,title,content,link,creation) values (NEW.date,'new event','new event details','/ievent/' || NEW.id, NOW()); END IF; IF(TG_OP='DELETE') THEN DELETE FROM textetweet_textetweet WHERE link='/ievent/' || NEW.id; END IF;
The problem seems to be the 'NEW.id'. How do I get the deleted id for the trigger?