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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
