Update FKs via frontend or backend

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 2005 Forums

SQL Server Administration (2005)

Update FKs via frontend or backend

Author

Topic

jayblaze2
Starting Member

5 Posts

Posted - 2009-10-09 : 12:03:58

Issue: Updating a view with multiple FKs via Trigger.

Description:
I have a view [view1] that combines (joins) three tables ([Table1], [Table2], [Table3]). [Table1] contains FKs of [table2] and [table3].

I setup an Instead Of trigger (update and insert) for [view1].

Both triggers look up the PKs for the FKs in [Table1] as [Table1] is the table ultimately updated.

Problem:
The client (Frontend) is doing some wierd stuff. I am testing in Access (ADP). I have a form in datasheet view. I use two comboboxes to look up all the possible values from [Table2] and [Table3].

When i update a record by changing the value of a combobox ([Table2] or [Table3]), the actual value in [Table2] or [Table3] is changed.

For Example we'll look at [Table2]:

The table has two columns: 1. ID (int) 2. Field (varchar)
Table Content:

1 "Apple"
2 "Orange"
3 "Grape"

The values of [Table2] change when I use the combobox in the Access form to Update [view1].

[Table2] after Updating [view1] via Access:

1 "Grape"
2 "Orange"
3 "Apple"

Note: this problem doesnt occur if testing in (backend) SQL management studio. should i not use this method for updating a view?

any advice would be great! i appologize for incorrect spelling or being vague.

"I caught a fleeting glimpse
Out of the corner of my eye.
I turned to look but it was gone
I cannot put my finger on it now
The child is grown,
The dream is gone." (Pink Floyd)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 12:49:15

sorry its not clear about how you're updating view in access. can you post used code?

jayblaze2
Starting Member

5 Posts

Posted - 2009-10-09 : 13:58:27

Sorry.... I am using SQL Server 2005 for the back-end. Access is the front-end. I am simply assigning the [view1] as the datasource.

"I caught a fleeting glimpse
Out of the corner of my eye.
I turned to look but it was gone
I cannot put my finger on it now
The child is grown,
The dream is gone." (Pink Floyd)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:00:08

ok..how does update happen?

jayblaze2
Starting Member

5 Posts

Posted - 2009-10-09 : 14:16:43

[code]
ALTER TRIGGER [dbo].[update_view1]
ON [dbo].[view1]
Instead of UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Update [Table1]
Set [Table1_FK_Table2] = [Table2].[Table2_PK],
NewsHeadline = ins.NewsHeadline,
NewsDescription = ins.NewsDescription,
NewsLink = ins.NewsLink,
[Table1_FK_Table3] = [Table3].[Table3_PK]
From [Table1] Inner Join inserted as ins
on [Table1].[Table1_PK] = [ins].[ins_PK] Inner Join [Table2]
on [Table2].[Table2_Field] = ins.[Field] Inner Join [Table3]
on [Table3].[Table3_Field] = ins.[Field]

END

[/code]

"I caught a fleeting glimpse
Out of the corner of my eye.
I turned to look but it was gone
I cannot put my finger on it now
The child is grown,
The dream is gone." (Pink Floyd)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-09 : 15:31:47

So your problem is not with the view or table1 at all, right? The problem is that your lookup tables (table2 and table3) are changing when you change the values in your combo boxes?

If that is the case this seems to be an Access question rather than a SQL Server Administration (2005) question. Maybe your combo box datasource (or whatever Access calls that) needs to change from a direct table link to the result set of an SP call.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources