updatable view question; one-to-one table relationship

C

Craig Buchanan

I started with two table that I built in MS Access: Object and Person.

Object
------
Id AutoNumber,Long Integer,Indexed (No Duplicates),Primary Key
....

Person
------
ObjectId Number,Long Integer,Indexed (No Duplicates),Primary Key
....

I created a one-to-one relationship between these tables on
Object.Id=Person.ObjectId. I enabled referential integrity with
cascading updates and deletes.

I created a view that referenced all fields from the Object table and
all fields from the Person table except ObjectId.

When I run this view, I am able to add new records. It appears that the
AutoNumber value is being set in the Person table automatically.


Next, I upsized the tables to Sql Server 2000. I selected DRI as the
mechanism to enforce referential integrity.

I created a view in Sql Server that matched the one in Access. I am not
able to add new records, as I get a message indicating that the
Person.ObjectId can not be null. It appears that the AutoNumber value
is NOT being set in the Person table automatically.

Is there a way to get this to work in Sql Server like it working in
Access? If not, what is good approach?

Thanks,

Craig Buchanan
 
H

Hugo Kornelis

I started with two table that I built in MS Access: Object and Person.

Object
------
Id AutoNumber,Long Integer,Indexed (No Duplicates),Primary Key
...

Person
------
ObjectId Number,Long Integer,Indexed (No Duplicates),Primary Key
...

I created a one-to-one relationship between these tables on
Object.Id=Person.ObjectId. I enabled referential integrity with
cascading updates and deletes.

I created a view that referenced all fields from the Object table and
all fields from the Person table except ObjectId.

When I run this view, I am able to add new records. It appears that the
AutoNumber value is being set in the Person table automatically.


Next, I upsized the tables to Sql Server 2000. I selected DRI as the
mechanism to enforce referential integrity.

I created a view in Sql Server that matched the one in Access. I am not
able to add new records, as I get a message indicating that the
Person.ObjectId can not be null. It appears that the AutoNumber value
is NOT being set in the Person table automatically.

Is there a way to get this to work in Sql Server like it working in
Access? If not, what is good approach?

Hi Craig,

You'll have to write an INSTEAD OF trigger for the view, to tell SQL
Server how an insert into the view should be translated for the base
tables.

The raw base version, without any special case handlig or error
handling, would look roughly like this:

CREATE TABLE Tab1
(KeyCol int NOT NULL PRIMARY KEY,
DataCol1 int NOT NULL)
CREATE TABLE Tab2
(KeyCol INT NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES Tab1,
DataCol2 int NOT NULL)
go
CREATE VIEW Both
AS
SELECT Tab1.KeyCol, DataCol1, DataCol2
FROM Tab1 INNER JOIN Tab2 ON Tab1.KeyCol = Tab2.KeyCol
go
-- Try to insert without trigger - will fail
INSERT INTO Both (KeyCol, DataCol1, DataCol2)
VALUES (1, 1, 1)
go
-- Add an INSTEAD OF trigger
CREATE TRIGGER InsertBoth
ON Both INSTEAD OF INSERT
AS
INSERT INTO Tab1 (KeyCol, DataCol1)
SELECT KeyCol, DataCol1
FROM inserted
INSERT INTO Tab2 (KeyCol, DataCol2)
SELECT KeyCol, DataCol2
FROM inserted
go
-- Retry the insert - works this time!
INSERT INTO Both (KeyCol, DataCol1, DataCol2)
VALUES (1, 1, 1)
SELECT * FROM Both
go
DROP VIEW Both
go
DROP TABLE Tab2
DROP TABLE Tab1
go
 
C

Craig Buchanan

Hugo-

Thanks for the reply.

If I have this trigger, do I need to also have a linkage defined in the
table Diagram?

Can you explain to me why Access does this automatically, but SQL Server
requires an extra step? Is this just a feature of DAO that exceeds the
ANSI-92 specification?

Thanks,

Craig
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top