Thread: Delete Trigger

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

Chris Berry <chrisdberry82@googlemail.com> writes:
> 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.

There's no "NEW" row in a delete trigger (... and the error message
should have told you that pretty explicitly ...).  You can look at the
OLD row instead.

BTW, you should probably think carefully about what the UPDATE case
is doing and whether it needs to look at NEW or OLD or a combination.

            regards, tom lane