|
hi there on SA16.0.0.1915 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.? Thanks Martin |
|
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 :) |
|
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. Just to understand: Am I right that the "Yes" applies to the second alternative of the question (i.e. "or do I really have to write all ...")?
(26 Mar '15, 09:41)
Volker Barth
Replies hidden
Correct. My "short answer" was too short... so I have clarified my answer to make this more clear.
(26 Mar '15, 11:06)
Mark Culp
|
|
hi guys 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 .. ;-( Martin |
IHMO, old and new relate to the schema of the according view, so it would not necessarily be clear how to "split" this to the according base tables/views...
Besides that, there is no "SET t.* =" "star assignment" in Watcom-SQL (and should not be mixed up with the old T-SQL outer join operator or a C-style multiple-assignment operator...).