January 23, 2008 at 10:11 am
I was able to overcome the Multi-Table view update problem by using two INSTEAD OF triggers on the view: one for INSERTs and one for UPDATEs. IN SSMS I'm able to insert new rows into the view, and update existing rows (including columns from each table) just fine.
In my Access application, I brought the view over as an ODBC link, and I can insert a row just fine, but I can't update existing rows.
Could it be because of the SET NOCOUNT ON statement I'm using, that it doesn't say how many records were affected? Anyway, here is my trigger...
CREATE Trigger tr_View_ind_memb_Update ON vw_ind_memb
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
-- good for updating one row at a time --
UPDATE Membership_Info
SET [type] = i.[type],
prosp_type=i.prosp_type,
first_name=i.first_name,
mi=i.mi,
last_name=i.last_name,
company_name=i.company_name,
company_contact=i.company_contact,
upgraded=i.upgraded,
status=i.status,
postage=i.postage,
member_since=i.member_since,
date_received=i.date_received,
memb_dues=i.memb_dues,
total_amt_due=i.total_amt_due,
exp_date=i.exp_date,
form_used=i.form_used,
how_learn_TBA=i.how_learn_TBA,
how_learn_notes=i.how_learn_notes,
exclude_email=i.exclude_email,
exclude_mail_list=i.exclude_mail_list,
hold=i.hold,
vip=i.vip,
no_CB=i.no_CB,
new_card=i.new_card,
memo=i.memo,
CB_only=i.CB_only,
last_edited=i.last_edited,
deceased=i.deceased
FROM inserted i
WHERE Membership_Info.membID=i.membID
UPDATE Ind_Info
set memb_type=i.memb_type,
region=i.region,
country=i.country,
gender=i.gender,
ethnicity=i.ethnicity,
volunteer=i.volunteer,
board_member=i.board_member,
refer_to_Th=i.refer_to_Th,
mm_only=i.mm_only,
memo=i.memo2,
af_SAG_AFTRA=i.af_SAG_AFTRA,
af_AEA=i.af_AEA,
af_SSDC=i.af_SSDC,
af_AGMA=i.af_AGMA,
af_IATSE=i.af_IATSE,
af_Costumers_Coop = i.af_Costumers_Coop
FROM inserted i
WHERE Ind_Info.membID=i.membID
UPDATE Primary_Contacts
SET phoneID=i.phoneID,
addressID=i.addressID,
emailID=i.emailID,
ship_addressID=i.ship_addressID
FROM inserted i
WHERE Primary_Contacts.membID=i.membID
END
January 23, 2008 at 10:18 am
I'm not sure how you're running the inserts versus deletes, nor whether you're using an Access Data Project or the "regular" access with linked tables, so I'll just throw this one out there. If you are using "conventional" access (.MDB or .MDE files) with linked tables, if the linked tables (which include views) dont' have something marked as the "primary key" for the recordset - updates and inserts would be disabled. Of course you could get around those by using tSQL code in the background, but the conventional data calls for inserting or updating would fail.
How are you implementing the updates and inserts? (pass-through queries, ADO calls?)
What kind of Access file are you using? which version of Access?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2008 at 10:51 am
Hi Matt,
Thanks for responding. It's a regular Access file, an MDB. All the tables are brought over via ODBC link tables, and are bound to the forms, so updates, inserts and deletes are happenning automatically.
Since writing this post I tried SETTING NOCOUNT back to OFF right before the last update (I had set it to ON in the beginning). So now, instead of returning nothing, or returning
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
it only returns one "(1 row(s) affected)" line. This actually worked for 99% of the rows in the view. I can now edit their contents. Just not the last couple of rows that I inserted myself manually, directly into the view. Also, instead of the thing just @#*! beeping at me, it displays that magic Write Conflict dialogue: "This record has been changed by another user since you started editing it." I'm thinking it may be because either I have some NULLS in there that Access doesn't like or something similar.
In terms of the primary key, of course inside of SQL each of the 3 tables has a primary key defined (an identity int) and are related to each other through foreign keys. When I bring the view over to Access as a link table, it asks me for the unique record identifier and I specify the primary key of the main table in the join.
Thanks for your help!
Aaron
January 23, 2008 at 11:02 am
In that case - you might care to double-check that you have no nullable bit/boolean fields, which will cause much confusion to Access (it's allowable in SQL Server, but Access doesn't, so linked tables with that feature will cause it much pain).
You may also need to add a timestamp field to your main table, and show it in the view. That has also been known to fix that error you just described....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2008 at 12:27 pm
Well you're absolutely right. It is because of nulls in the bit fields. The weird thing is though, that I knew about that problem, and I'd previously set defaults of 0 on all my bit fields. If I insert into the table directly, any unspecified bit fields get filled with their defaults of 0, but if I insert through the view, they wind up as nulls.
I'm thinking it's because of the way I wrote my INSTEAD OF INSERT trigger. I just inserted into the table from the contents of the 'inserted' temporary table. The 'inserted' table must still contain nulls. I know that's not clear, so here's an excerpt from the trigger.
INSERT INTO Membership_Info ([type], prosp_type, first_name, mi, last_name, company_name, company_contact,
upgraded, status, postage, member_since, date_received, memb_dues, total_amt_due, exp_date, form_used,
how_learn_TBA, how_learn_notes, exclude_email, exclude_mail_list, hold, vip, no_CB, new_card,
memo, CB_only, last_edited, deceased)
SELECT [type], prosp_type, first_name, mi, last_name, company_name, company_contact,
upgraded, status, postage, member_since, date_received, memb_dues, total_amt_due, exp_date, form_used,
how_learn_TBA, how_learn_notes, exclude_email, exclude_mail_list, hold, vip, no_CB, new_card, memo, CB_only, last_edited, deceased
FROM inserted
-- get identity back from table
select @newID1 = @@Identity
I guess the 'inserted' table doesn't know about the defaults.
Is there a way to give the 'inserted' table defaults, or do I need to manually check for nulls in each bit field?
Thanks.
Aaron
January 23, 2008 at 3:35 pm
I wound up fixing the problem by defining a custom function:
CREATE FUNCTION NoNullsBit(@input bit)
RETURNS bit
AS
BEGIN
DECLARE @result bit
if @input is null
set @result = 0
else
set @result = @input
RETURN @result
END
I then modified my view's input trigger to use the function. Ultimately, of course, I'm recreating the default logic, which is being bypassed by the trigger. So it is a bit of a kludge, but it works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply