Обсуждение: Statement triggers 7.4 NEW/OLD
How can I address the rows affected by an insert/update/delete statement in pl/pgsql? The doc says that NEW and OLD will be NULL for statement triggers, which makes sense because NEW and OLD are of type RECORD, capable of holding only a single row. So where's the beef? Regards, Andreas
Andreas Pflug wrote: > > How can I address the rows affected by an insert/update/delete statement > in pl/pgsql? > The doc says that NEW and OLD will be NULL for statement triggers, which > makes sense because NEW and OLD are of type RECORD, capable of holding > only a single row. > So where's the beef? IMHO statement triggers instead should have access to the NEW and OLD rows through some mechanism. I can think of NEW and OLD being reference cursors in the case of statement triggers, and the trigger then can FETCH the rows from there. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan, > IMHO statement triggers instead should have access to the NEW and OLD > rows through some mechanism. I can think of NEW and OLD being reference > cursors in the case of statement triggers, and the trigger then can > FETCH the rows from there. Yeah; how else could it possibly work? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: >>IMHO statement triggers instead should have access to the NEW and OLD >>rows through some mechanism. I can think of NEW and OLD being reference >>cursors in the case of statement triggers, and the trigger then can >>FETCH the rows from there. >> >> > >Yeah; how else could it possibly work? > > > Why do it easy if you can have it difficult? ;-) If I want to perform row by row actions I use a row level trigger! Statement triggers need to work like this: Instead of NEW.foo := 1; you code UPDATE NEW SET foo=1; If the statement trigger doesn't offer a complete SETOF RECORD, It's pretty useless. In my case, updates and deletes on a table could possibly touch thousands of rows at the same time, and the trigger will update aggregated values in another table, and insert or delete a third one with 3-4 additional statements. This is pretty fast if implemented as true statement trigger, but will possibly blow the system if done row by row. Imagine a single statement, that triggers ROW_COUNT*4 statements, where ROW_COUNT can easily be 1000, 10000 or much more! Regards, Andreas