• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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.
0
ziolko
Asked:
ziolko
  • 8
  • 8
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now