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?
Thanks a lot
