On Thu, 13 Jun 2002, Tim Ellis wrote:
> 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.
>
> It spewed forth the following code:
>
> -- file generated by "dezign for databases"
> -- create-date :6/13/2002
> -- create-time :10:29:56 am
> -- project-name :not yet specified
> -- project-author :not yet specified
>
> create table transaction(autonum integer);
>
> 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);
>
> Postgres (as you might suspect) didn't like it. Looking at the Postgres
> "create trigger" syntax, there is nothing about "referencing X as Y" being
> a possible phrase.
>
> Is the problem in:
>
> 1. Dezign for Databases, this really isn't ANSI Level 2,
> 2. Postgres, it doesn't (yet) support this level of functionality, but
> should, or
> 3. Me, I'm mixing up ANSI Level 2 and ANSI SQL '92, Postgres doesn't now
> and has no plans to support ANSI Level 2 SQL
IIRC, SQL92 doesn't have triggers. I believe the above is SQL 99 syntax
and it looks like triggers aren't Core SQL99 either.
PostgreSQL may eventually take the full SQL99 syntax but it's likely to be
a while unless someone wants it enough to work on it.