Re: 'record old is unassigned yet' when using trigger in 7.1.1

OK I tried:
create function utbl_set_statchangedate() returns opaque as '      begin      IF TG_OP = \'INSERT\' OR OLD.status <> NEW.status     THEN	NEW.statchangedate = CURRENT_DATE;     END IF;return NEW;end;
' language 'plpgsql';

... but that had the same problem.  So then I tried:

create function utbl_set_statchangedate() returns opaque as 
'begin      IF TG_OP = \'UPDATE\'     THEN     	NEW.statchangedate := CURRENT_DATE;     ELSE IF OLD.status <> NEW.status     THEN	NEW.statchangedate := CURRENT_DATE;     END IF;return NEW;end;
' language 'plpgsql';

but now I'm getting:
NOTICE:  plpgsql: ERROR during compile of utbl_set_statchangedate near
line 10
ERROR:  parse error at or near ";"



Joe Conway wrote:
> 
> >        begin
> >      IF OLD.status <> NEW.status
> >      THEN
> > NEW.statchangedate = CURRENT_DATE;
> >      END IF;
> > return NEW;
> > end;
> >
> > The problem is when a new row is inserted I get this error message:
> > ERROR:  record old is unassigned yet
> > ... and the insert fails.
> >
> > It doesn't matter if the trigger is before or after.
> 
> OLD doesn't exist on inserts, only on update and delete. If you want to use
> the same function for both insert and update use the TG_OP variable and an
> if statement. See
> http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html
> 
> Hope this helps,
> 
> Joe

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com