Re: Create trigger problem : - Mailing list pgsql-general

> CREATE FUNCTION DelArtFather(INT4)
>  RETURNS INT4
>  AS '
>   DELETE FROM TArticles WHERE IdArtFather = $1;
>   SELECT 1 AS ignore_this
>  '
>  LANGUAGE 'sql';
>
> CREATE TRIGGER trigger_TArticles
>  BEFORE DELETE ON TArticles
>  FOR EACH ROW EXECUTE PROCEDURE DelArtFather(old.IdArt);
>
> and i obtain this error :
>
> ERROR: parse error at or near "old"
>
> How can i correct my command ??

Triggered functions don't accept arguments and don't return values. Well,
they *do* return OPAQUE, but not a real value like an INT4. DELETE (and
UPDATE) triggers implicitly have the "old" record available, so no need to
pass any parameters from there. So what you would need to do is:

CREATE FUNCTION DelArtFather() RETURNS OPAQUE
  AS '
    DELETE FROM TArticles WHERE IdArtFather = old.IdArt;
  ' LANGUAGE 'sql';

CREATE TRIGGER trigger_TArticles
  BEFORE DELETE ON TArticles
  FOR EACH ROW EXECUTE PROCEDURE DelArtFather();

Now having said that... it sounds more like you want a foreign key
cascade... no need to write a trigger. Assuming your table is written:

CREATE TArticles (
  IdArt  INTEGER,
  IdArtFather INTEGER,
  ArtText: TEXT,
  PRIMARY KEY(IdArt)
);

You could just change that to:

CREATE TArticles (
  IdArt  INTEGER,
  IdArtFather INTEGER,
  ArtText: TEXT,
  PRIMARY KEY(IdArt),
  FOREIGN KEY (IdArtFather) REFERENCES (TArticles) IdArt ON DELETE CASCADE
);

Hope this helps,

Greg