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

asked 25 Mar '15, 18:04

Tinu's gravatar image

Tinu
76338
accept rate: 0%

edited 26 Mar '15, 04:24

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

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

(26 Mar '15, 09:45) Volker Barth

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

permanent link

answered 26 Mar '15, 11:38

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

permanent link

answered 26 Mar '15, 09:31

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

edited 26 Mar '15, 11:05

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

permanent link

answered 26 Mar '15, 15:27

Tinu's gravatar image

Tinu
76338
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×260
×79

question asked: 25 Mar '15, 18:04

question was seen: 2,180 times

last updated: 26 Mar '15, 15:27