> I'm not sure this is a fair assessment of statement level triggers.
> Yes, in MSSQL you can access the rows involved in the statement, but
> in Oracle you cannot (emphasis added):
>
> "Accessing Column Values in Row Triggers
> Within a trigger body of a *row trigger*, the PL/SQL code and SQL
> statements have access to the old and new column values of the current
> row affected by the triggering statement..."
>
> See:
> http://www.engin.umich.edu/caen/wls/software/oracle/appdev.901/a88876/adg13trg.htm#526
But PostgreSQL may be better than Oracle, don't you think? In the named
document,
- users are warned not to use recursiveness
- INSTEAD OF triggers can only be row-level, and are quite restricted.
PostgreSQLs rules are much more flexible.
>
> MSSQL does not have row level triggers at all (I know they didn't in
> 6.5, and looking at the help file it doesn't appear that they were
> added even in MSSQL 2000), so access at the statement level is needed
> to do anything useful at all.
MSSQL2000 still doesn't have row level triggers, and I doubt that 2003 has.
Just to make a clarification: I don't expect that something like this works: UPDATE NEW set foo = bar
i.e. using the OLD and NEW rowsets as updateable rowsets, in the same
way as row-level triggers allow simple NEW.foo := NEW.bar
UPDATE footable set foo=bar FROM footable JOIN NEW USING (thekeycol)
would be the style that's desirable, and this is the (only) way that
MSSQL supports this. For this simple example, a row-level trigger
certainly will be the cheapest way (both in respect to implementation
and execution time), but if additional tables are needed query count
will be multiplied by the number of rows affected. This effectively
reduces mass execution to line-by-line execution.
Regards,
Andreas