Thread: Statement level triggers
Hi all. What'd be the behavior of a (plpgsql) trigger function when called as a statement level trigger? Let's say that a statement will involve more than one row. The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says: "Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement." Many thanks. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
Vincenzo Romano wrote: > Hi all. > > What'd be the behavior of a (plpgsql) trigger function when called as > a statement level trigger? > Let's say that a statement will involve more than one row. > The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says: > > "Statement-level triggers do not currently have any way to examine the > individual row(s) modified by the statement." It means you don't have NEW or OLD record-variables. You can still perform any queries you need to on the table affected and any other tables of interest. I find statement-level triggers to be useful mostly for sanity-checking. I'll have an AFTER statement level trigger verify a certain invariant on a table - like a table-level CHECK (as opposed to row-level CHECK). -- Craig Ringer
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > What'd be the behavior of a (plpgsql) trigger function when called as > a statement level trigger? > Let's say that a statement will involve more than one row. > The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says: > > "Statement-level triggers do not currently have any way to examine the > individual row(s) modied by the statement." It's not clear what you are asking, but statement-level triggers imply that the actual data (e.g. row changes) are not relevant (or visible) to the trigger. Thus, it's more of "something" has changed trigger, rather than a row-level "this stuff" has changed trigger. For example, Bucardo uses both kinds: the row-level triggers keep track of what rows have changed, and statement-level triggers which send a NOTIFY to let the Bucardo daemon know that the table has changed. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201001200828 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAktXBSAACgkQvJuQZxSWSsj3HwCeN/RhPN/Zo7KmKT9gHafDLLAf ZEQAn0pkS1sX5jb3+ycQIN6wK0OHxgtC =V/4j -----END PGP SIGNATURE-----
In article <4B5702B9.50706@postnewspapers.com.au>, Craig Ringer <craig@postnewspapers.com.au> writes: >> What'd be the behavior of a (plpgsql) trigger function when called as >> a statement level trigger? >> Let's say that a statement will involve more than one row. >> The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says: >> >> "Statement-level triggers do not currently have any way to examine the >> individual row(s) modified by the statement." > It means you don't have NEW or OLD record-variables. Other databases have NEW and/or OLD pseudo-tables for that. My suggestion about implementing that got turned down because, without a primary key, you can't say which NEW and OLD rows belong to each other. Since tables often have a primary key I still think that this would be an addition making statement-level triggers much more useful than they are now.