i have several large views that simply joins some tables. i want to make it updatable. since it's a JDBC connection i learned that such views are always read only. so relief is instead of trigger.
however, the tables have large numbers of columns so writing such a update statement is tedious.
is it possible to write the trigger like this? any other way to shorten the update statement?
--- create trigger .. instead of update begin update t1 set new.* = old.* where old.t1_pkey = new.t1_pkey; update t2 set new.* = old.* where old.t2_pkey = new.t2_pkey; ... update tn set new.* = old.* where old.tn_pkey = new.tn_pkey; end ---
or do i really have to write all set noew.col = old.col etc. etc.?
The INSERT ON EXISTING UPDATE form of the INSERT statement can sometimes be used as a shorthand for an UPDATE statement.
Be careful to thoroughly test your solution, in particular to make sure your choice of ON EXISTING DEFAULTS ON versus OFF is the correct one (DEFAULTS OFF is the default, and by omitting that clause you ARE making a choice :)
answered 26 Mar '15, 11:38
Short answer: Yes, you need to write all of the new.col = old.col pieces in the set clause of the update statement.
Longer answer: You could construct the update statement using the list of columns from SYSCOLUMNS and then do a execute immediate on the constructed statement but I would not actually recommend doing this since it adds unnecessary computation and will harm performance.
thanks for your suggestions ... i also thought about a computed execute immediate ... the insert on existing update is nice ... maybe i will spend some time on that ... alhtough i don't have time at all on this project .. ;-(
answered 26 Mar '15, 15:27