Updatable views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updatable views

Author

Topic

nfsoft
Starting Member

36 Posts

Posted - 2004-03-04 : 05:44:55

I am working with updatable views in my application
ex:

/* -------------------------------------------------------------------
* VIEW: V_CURSO
* -------------------------------------------------------------------*/

--drop view V_CURSO
create view V_CURSO as
select A.*, B.ABREV as ABV_AP, B.NOMAP as NOM_AP, C.NOMTIPOCURSO as NOM_TIPOCURSO, D.NOMESTADOCURSO as NOM_ESTADOCURSO
from CURSO A
inner join AP B on A.ID_AP=B.ID_AP
inner join TIPOCURSO C on A.ID_TIPOCURSO=C.ID_TIPOCURSO
inner join ESTADOCURSO D on A.ID_ESTADOCURSO=D.ID_ESTADOCURSO
GO

--drop trigger tr_del_V_CURSO
create trigger tr_del_V_CURSO ON V_CURSO
instead of DELETE
as
begin
SET NOCOUNT ON
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS(SELECT * FROM deleted)
DELETE FROM CURSO --local
FROM CURSO AS A inner join deleted as B
on A.ID_CURSO = B.ID_CURSO
COMMIT TRANSACTION
end

------------

I'd like to share experience on this subject.
Will there be hidden problens in future?
Is this the correct (best) way to do it?

Nuno Ferreira

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-04 : 12:42:27

We don't know what you are trying to do as you haven't explained the situation and problem.

Tara

nfsoft
Starting Member

36 Posts

Posted - 2004-03-04 : 17:14:58

I have a aplication in java running on a SQL Server.
I am going to use uppdatable views (like the code posted).
I have never worked with uppdatable views before, that is why I am trying to get you to tell me if this is a good or bad choice.
Like...
V_CURSO is a view, To make actions on it (insert, remove, update) should I use a "instead of INSERT, UPDATE, DELETE" trigger or a "instead of DELETE" is a bether (since insert and update is alowed on V_CURSO olready)?
(sory about my rusty english.)

Nuno Ferreira

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-04 : 17:19:12

Yes you use INSTEAD OF triggers for updateable views.

http://www.sqlteam.com/item.asp?ItemID=6494

Tara

nfsoft
Starting Member

36 Posts

Posted - 2004-03-04 : 20:06:12

Looks like Garth Wells doesn't like my application design...
"(...)Quite frankly I think updating Views is poor application design.(...)--Garth"
What are the base facts of his point of view...

Nuno Ferreira

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-04 : 21:20:56

Why use an updatable View when you can just update the tables directly? What are the advantages of the View? Seems like more trouble than it's worth.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

JohnDeere
Posting Yak Master

191 Posts

Posted - 2004-03-05 : 00:57:00

One reason to use views is for security. I try not to grant permissions to tables. I encourage (require) the use of stored procedures and or views to update data. It does require more admin work on my part.

Lance Harra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources