MI-L editing views in spatialware

[Wotzhere]

Judging from the quick responces and requests for more info, there seems  
that there is some interest in how to edit views in SQL, so I will give  
you the example I am using to explain the technique.

I have a view which is a join between a paddocks table and a farms table  
that contains a field that designates the mill it belongs to:

SELECT  dbo.T_PAD_CUR.C_PAD_ID,  
dbo.T_PAD_CUR.C_LINKCODE,dbo.T_PAD_CUR.C_Farm, dbo.T_PAD_CUR.F_Area_Ha,  
dbo.T_PAD_CUR.C_EDITFILE, dbo.T_PAD_CUR.MI_STYLE, dbo.T_PAD_CUR.SW_MEMBER  
AS MI_PRINX, dbo.T_PAD_CUR.SW_GEOMETRY, dbo.T_FARM.MILL
FROM dbo.T_PAD_CUR INNER JOIN dbo.T_FARM ON dbo.T_PAD_CUR.C_Farm =  
dbo.T_FARM.C_Farm

You can then open this view in MapInfo with only those columns from the  
T_PAD_CUR table (lose the MILL field from the T_FARM table), and filter  
rows based on mill for each user.  If you save as T_PAD_CUR, you end up  
with user tables that only contain a users data, but which has the same  
name as the original table.

I imagine you could do the same with a view that was a spatial join, but  
am not up to that page yet (crash course in SQL Server and Spatialware at  
present).

But you cannot edit this view directly, so to edit it, you put triggers on  
that view that replace the standard insert, delete and update functions.

for delete:
CREATE TRIGGER trg_V_PAD_CUR_delete ON [dbo].[V_PAD_CUR]
INSTEAD OF DELETE
AS
   DELETE FROM T_PAD_CUR WHERE T_PAD_CUR.sw_member IN (SELECT MI_PRINX
FROM Deleted)
for insert:
CREATE TRIGGER trg_V_PAD_CUR_Insert ON dbo.V_PAD_CUR
INSTEAD OF INSERT
      AS
            Insert Into T_PAD_CUR (C_PAD_ID, C_LINKCODE, C_MILL, C_FARM
,C_BLOCK, C_PADDOCK, C_BED, F_AREA_HA, C_EDITFILE)
               SELECT C_PAD_ID, C_LINKCODE, C_MILL, C_FARM, C_BLOCK,
C_PADDOCK, C_BED, F_AREA_HA, C_EDITFILE From Inserted
At present this doen't work as I haven't included the obj field yet, and  
while I get the record inserted, I lose the map object. - need to include  
SW_GEOMETRY.

Still working on the update one.
its a pretty useful trick, as you can include logging into the trigger to  
record all changes.

r
--
________________________________________________
Robert Crossley
Agtrix P/L
9 Short St
PO Box 63
New Brighton 2483
Far Southern Queensland
AUSTRALIA
153.549004 E 28.517344 S
P: 02 6680 1309
F: New Connection
M: 0419 718 642
E: [EMAIL PROTECTED]
W: www.agtrix.com
W: www.wotzhere.com
---------------------------------------------------------------------
List hosting provided by Directions Magazine | www.directionsmag.com |
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Message number: 13956