how to update joined table using instead of triggers

[seco]

Hi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?

thanks in advance.

 

[ss7thirty]

If you create a TableAdapter using select command that uses JOIN, insert, update and delete commands cannot be generated automatically, but you can do it yourself.

My suggestion to you is, that you generate select, insert, update and delete just form the first table (can be auto generated). Than expand the select command with join and use that select command also in update and insert commands (a select command must be present after insert and update commands).

I am using Microsoft SQL Server, so you must check if this is possible when using other databases.

The other way to go about this would be using a stored procedure.

Plagerized from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=872566&SiteID=1

 

[cjard]

Hi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?

thanks in advance.

This is quite an advanced thing, and you need to understand more about databases to understand why you cannot update all columns of a view..


Suppose we have CUstomer and Orders. When we join on ID we get:

CustID, Name, CustID, OrderID, Product
1, John Smith, 1, 1, Sugar
1, John Smith, 1, 2, Lemons
1, John Smith, 1, 3, Potatoes


SELECT * FROM cust NATURAL INNER JOIN order

Maybe SQL Server supports Natural, maybe it doesnt.. ITs just a fast way of joining two tables where the columns you want to join on are the same name.


Now you want to update this:

UPDATE
(SELECT * FROM cust NATURAL INNER JOIN order)
SET
name = name + product



How will the database do that? How will it set John Smith to be:

John Smithsugar
John Smithlemons
John Smithpotatoes


all at the same time? You cant! A cell in a row CANNOT have 3 different values all at the same time

So, NONE of the columns from the Customer table in this query are updatable, because they are ALL repeated. Their KEYS have LOST the unique quality.

My advice to you would be:

Dont use JOINs for queries you intend to write back to the db.

If you must use a join, then you can only update ONE table, the one that still has a unique key present.

That one table should be how your updating commands are written.


In this case, it would be ORDERS..

Your select might look like:

SELECT orders.*, customer.Name FROM oders NATURAL INNER JOIN customers


But your update query will look like:

UPDATE orders SET blah = blah...

DONT even try to update customer here.. Update it elsewhere, and mark the column with "John Smith" in your grid view as Read only.

 

[seco]

thanks for reply

i read your advice carefully

i try to do it but my view still don't have primary key !!! and so no update & delete generation
my solution is to make this column on the orders table to be primary key on the dataset(the only column that is not changed) and the update and delete statements will be generated correctly.
huh ?! how is that ?! is my solution can works?!

thanks in advance.

 

[cjard]

thanks for reply

i read your advice carefully

i try to do it but my view still don't have primary key !!!

Views dont, for the reasons I explained above

and so no update & delete generation
my solution is to make this column on the orders table to be primary key on the dataset(the only column that is not changed) and the update and delete statements will be generated correctly.

Fraid not.. Its irrelevant what the key of the datatable is.. only relevant what the key of the database table itself is

There is nothing, at all, stopping you writing the update and delete queries yourself! Only update the table you still have unique key values for. If you still dont understand this, you need to show me the code for the view

 

[seco]

you are right im forced to write the update and delete and insert commands my self because it gives me error messsage says "Dynamic SQL generation is not supported against multiple base tables."


i think alot of pain (i will write statements and parameters ,,,)
so could you give me simple explain to write the SQL statements? on any example ex. like customers and orders you provide on top and how to make insert and update and delete

thanks in advance.

 

[cjard]

best advice I could give you:



take your SELECT sql and copy paste it into notepad (click the tableadapter in the dataset then look in the properties)

now identify the one table in the query that you want to update. You cannot update multiple tables. pick ONE!!

change the select query to be:
SELECT * FROM <that ONE table>

you are asked: "Do you want to updat ethe updating SQL based on the new command text?"

YES YOU DO

now paste back in your old query with joined tables

"DO you want to updat ethe updating commands based on the new query text?"

NO YOU DO NOT


-

Now just take a look at the update insert and delete
That is how they are written

For a comprehensive tutorial on writing SQL, go to www.w3schools.com

 

[seco]

thanks for reply
one last question and sorry for stupidity

now the insert and update and delete commands generated because its belongs to one table just the insert get its data from a join .
so how can i update the columns from the parent table that appears on the grid? those columns of course are not in the update statement so. what now?!

thanks in advance.

 

[cjard]

thanks for reply
one last question and sorry for stupidity

now the insert and update and delete commands generated because its belongs to one table just the insert get its data from a join .
so how can i update the columns from the parent table that appears on the grid? those columns of course are not in the update statement so. what now?!

thanks in advance.

You'll have to write a separate update for them.
Maybe you missed the entire point I was trying to make with the posts on why parents in views arent updateable:

PARENT TABLES IN A VIEW ARE NOT UPDATEABLE BECAUSE THEY LOSE THE UNIQUE QUALITY OF THEIR PRIMARY KEY

Remember customer-order example.. one customer, many orders:

John Smith, Apples
John Smith, Pears

You cant update John Smith! He isnt unique any more! You cannot make him take both valies simulatneously. For more info re-read this entire thread.

 

[cjard]

You know.. i just realised, I read your question in completely the wrong order:

You wrote:
how to update joined table using instead of triggers

I read:
how to update joined table instead of usingtriggers


Throw me a piece of that humble pie. Have a read of this:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

Be aware that the code in a INSTEAD OF on a view is evaluated for every row you try to update. This doesnt make the view itself updatable per se, it simply runs some code that usually carries out two updates: One to the parent and one to the child

Bear in mind, the "cant take two values" rule still applies. If your user has a grid that looks like:


John Smith, Apples
John Smith, Pears
John Smith, Peaches
John Smith, Oranges

And they edit it thus:


John Smith, Apples
John Jones, Pears
James Smith, Peaches
Jane Jones, Oranges


We cant really be sure which will end up as, but the most recently submitted row will be the one that survives:

Supposing they are submitted in order:

Jane Jones, Apples
Jane Jones, Pears
Jane Jones, Peaches
Jane Jones, Oranges



This can be very confusing for your user. I would still recommend that you make these rows read only.

Actually, I really would recommend that you show your related data in a different way. Read the DW2 link in my signature, section on Displaying Related Data


There is no poit, really in having a grid that shows:

John Smith, Apples
John Smith, Pears
John Smith, Peaches
John Smith, Oranges


You would have ONE text box and one grid:

Customer Name: [John Smith ]
Orders grid:
Apples
Pears
Peaches
Oranges