Thread: Create trigger problem :

Hi all,

I try this :

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 ??

Thanks in advance

Guy De Leeuw

Trigger functions need to take no arguments and return
opaque.  You can use NEW or OLD inside the function and
arguments given in the create trigger statement
are passed into the function in a special way (in
plpgsql, it's TG_ARGV[] i believe)

On Fri, 9 Mar 2001, De Leeuw Guy wrote:

> Hi all,
>
> I try this :
>
> 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"

> 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

Thanks for your interest,
I try also this but i receive the error :

ERROR: ProcedureCreate: sql functions cnnot return type "opaque"
ERROR: CreateTrigger: Function delartfather() does not exist

here is my code :

CREATE FUNCTION DelArtFather()
 RETURNS opaque
 AS '
  DELETE FROM TArticles WHERE IdArtFather = OLD.IdArt;
  SELECT 1 AS ignore_this
 '
 LANGUAGE 'sql';

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

"Stephan Szabo" <sszabo@megazone23.bigpanda.com> a �crit dans le message
news: Pine.BSF.4.21.0103121829380.98920-100000@megazone23.bigpanda.com...
>
> Trigger functions need to take no arguments and return
> opaque.  You can use NEW or OLD inside the function and
> arguments given in the create trigger statement
> are passed into the function in a special way (in
> plpgsql, it's TG_ARGV[] i believe)
>
> On Fri, 9 Mar 2001, De Leeuw Guy wrote:
>
> > Hi all,
> >
> > I try this :
> >
> > 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"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)