Updateing SQL views via TDBGrid

Hi guys,
problem is simple but very annoying.
I have TADOQuery, TDataSource and TDBGrid all set and ready.
TADOQuery.SQL is set to "select * from My_View" My_View is very simple MSSQL View (select Col1 from My_Table)
when I connect to database I can view, edit, delete, append and everything... but when I launched SQL Profiler I was stunned but what I saw
all SQL INSERTs, UPDATEs etc are performed on My_Table not on My_View.
Have You experienced something like that? I spent 2 hours trying to force SQL commands to preform on view instead of table

ziolko.
LVL 21
ziolkoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
a view is a sight to one or more tables,
a view is not intended to show additional (inserted/updated) data
a view is usual readonly

in modern sql-databases on simple views are inserts straight forwarded to the sourcetable(s)

so your happening is just normal

if you want to have the data of the tables and adding more data wothout affecting the sourcetable,
then you need to use a temporary table

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ziolkoAuthor Commented:
Hi, meikl.
Yeah, so why Microsoft developed trigeers "INSTEAD OF" why allow operation other then select on views?
But the big question is what about permissions, I can set insert, update, delete permissions on view but it's useless untill i set same permissions on source table, what about more complex views with joins or nested views?
maybe its normal but for me its not logical :)

Second thing, imagine that situation:
I have a table with data about some products, theres column identifying product.
First idea that comes to my mind is to create few views, each view would correspond to some product
than I could simple allow users to work on views, but i cant because even if i create trigger (to fill column identifying product)
this trigger will nver be called.  

ziolko.
0
kretzschmarCommented:
>But the big question is what about permissions, I can set insert,
>update, delete permissions on view but it's useless

usual this permissions are not useless,
but depending on your role-concept

if you try this as owner of view and table, then yes, it has no affect
(the owner can usual all)

>I have a table with data about some products, theres column identifying product.
there is no trigger needed, except you want to update/insert,
its just a view based on a joined select

guess you are new to sql-database-concepts?

meikl ;-)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ziolkoAuthor Commented:
>guess you are new to sql-database-concepts?
 
heh, no I'm not new:)
in matter of fact im into databases for sometime now:)
I'll try to be more clear here.

>there is no trigger needed, except you want to update/insert,
>its just a view based on a joined select

I've never used views to update/edit data, always stored procedures, hell I've never used TDBGrid:))
Just yesterday I tried to show someone "hello world" program with ADO and I was suprised by what i saw.

>usual this permissions are not useless,
>but depending on your role-concept

if You give user permission to insert records to view, but not to source table it is useless because eventually user wont be able to insert new records:)

ziolko.

0
kretzschmarCommented:
>if You give user permission to insert records to view, but not to source table it is useless because
>eventually user wont be able to insert new records:)
yep, of course, i saw it from the other direction :-))

well, what is your question already?
(i missed it in the meanwhile)

meikl ;-)
0
ziolkoAuthor Commented:
Is there a way to "force" insert/update on view not table?:)

ziolko.
0
ziolkoAuthor Commented:
..of course by useing combination of ADOQuery, Datasource and DBGrid:)

btw. I'm afraid I know answer, and that answer makes me very dissapointed :))
ziolko.
0
kretzschmarCommented:
>Is there a way to "force" insert/update on view not table?:)
no, because a view is not something physical
on which could be stored additional things,
which are not based on the physical tables

what is your intention to do so?

meikl ;-)
0
ziolkoAuthor Commented:
my intention is simple... I wanna see if it's possible:))

ziolko.
0
kretzschmarCommented:
>I wanna see if it's possible:))
in this case, based on a view on serverside -> no

of course there are some workarounds
(clientdataset, memorytable, etc)
where you can cache new data before they are (or not) forwarded to the server

meikl ;-)
0
ziolkoAuthor Commented:
genaraly my point is...
if I can use: update My_View....  then why TDataSource is so stubborn and keep using: update My_Table...
maybe I'll write TMyDataSource?:)

ziolko.
0
kretzschmarCommented:
this doesn't do the TDataSource,
this does ADO for you

meikl ;-)
0
kretzschmarCommented:
appendix:

>this doesn't do the TDataSource,
>this does ADO for you

respectivly also the sql-server self

:-))

0
ziolkoAuthor Commented:
right -  ADO :)

p.s. I'll keep this open for a while maybe someone else will be interested in shareing his thoughts about this ADO behaviour, then I'll grant You with points... even thou You got plent of them :))

ziolko.
0
kretzschmarCommented:
:-)) is ok
0
ziolkoAuthor Commented:
as I said, points are Yours:)

ziolko.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.