Re: Triggers... Questions... Yes.

Tim Ellis <Tim(dot)Ellis(at)gamet(dot)com> writes:
> I just used Dezign for Databases and created a simple table in what it
> claimed was "ANSI Level 2" compliance with a one-up "autonum" column.

There is no such animal as "ANSI Level 2" SQL.

> create trigger transaction_autonum_inc
> before insert on transaction
> referencing new as n
> for each row
> set (n.autonum) = (select (max(autonum),0) + 1 from transaction);

Looking at SQL99 (that's ISO/IEC 9075-2:1999, Part 2: SQL/Foundation
if we have to get pedantic about it), there is something about a
"referencing" clause, which we do not have. However, the "set" command
shown here does not appear to be legal according to SQL99, so I'm not
planning to get excited about not having "referencing". There are a
number of missing features in our trigger stuff, and that one seems
right at the bottom of the list as far as adding useful functionality
goes.

BTW, what are they expecting "(max(autonum),0)" to do, other than draw a
syntax error? Perhaps there was supposed to be a COALESCE there?

Even if it worked or were standards-compliant, this approach to
implementing an autonumbering column would be brain dead in the extreme
--- you do *not* want to do a max() aggregate for every insert. Use
a sequence object instead...

regards, tom lane