Shorting "instead up update" trigger update statement?

[Former Member]

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

[VolkerBarth]

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...).

[Breck_Carter]

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 🙂

[Former Member]

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

[MarkCulp]

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.

[VolkerBarth]

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 ...")?

[MarkCulp]

Correct. My "short answer" was too short... so I have clarified my answer to make this more clear.