"Doesn't satisfy criteria" error on Access update of updateable view with INSTEAD OF trigger

[Rob Oaks]

I have an Access form based on an updateable view based on three
tables defined in SS 2K. I have INSTEAD OF triggers that modify all
three of the tables on Insert/Update operations. I have tested all of
this thoroughly in SS and it works fine.

Without WITH VIEW_METADATA on the view, Access gives the "...doesn't
satisfy the criteria in the underlying record source" (if I close the
form and reopen, however, the changes are there). With WITH
VIEW_METADATA Access won't let me type into any of the fields in the
form.

The view exposes the PKs for two of the three underlying tables
tables. Could this have anything to do with it? I saw a posting that
referred to the need to expose all PKs.

MY DEALINE FAST APPROACHES...

Thanks for your help.

[Brian M. Sockey [MS]]

Hi Rob,

If you could post some script to recreate your tables, the view, and the trigger I will take a look at it. Also can
you clarify whether you are using Access 2000 or 2002 ADP files?

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
© 2001 Microsoft Corporation. All rights reserved.

--------------------
| From: rob...@comcast.net (Rob Oaks)
| Newsgroups: microsoft.public.access.adp.sqlserver
| Subject: "Doesn't satisfy criteria" error on Access update of updateable view with INSTEAD OF trigger
| Date: 25 Apr 2002 07:11:06 -0700

[Rob Oaks]

For all of the scripts, I've tried to eliminate extraneous constraints
and relationships to keep things simple. Thanks so much for your help.

Here we go:

1. BTW, I'm using Access 2002.

2. The tables:

CREATE TABLE [Info] (
[InfoID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Transcription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PKInfo] PRIMARY KEY NONCLUSTERED
(
[InfoID]
) ON [PRIMARY] ,
CONSTRAINT [CK_AssetInfoDescriptionIsNotEmptyString] CHECK
([dbo].[IsEmptyString]([Description]) = 0)
)

CREATE TABLE [AssetInfo] (
[AssetInfoID] [int] IDENTITY (1, 1) NOT NULL ,
[AssetID] [int] NOT NULL ,
[InfoID] [int] NOT NULL ,
CONSTRAINT [PKAssetInfo] PRIMARY KEY NONCLUSTERED
(
[AssetInfoID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [AudioClipFile] (
[AudioClipFileID] [int] IDENTITY (1, 1) NOT NULL ,
[InfoID] [int] NULL ,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
CONSTRAINT [PK_AudioClipFile] PRIMARY KEY CLUSTERED
(
[AudioClipFileID]
) ON [PRIMARY] ,
CONSTRAINT [CK_AudioClipFileNameIsNotEmptyString] CHECK
([dbo].[IsEmptyString]([FileName]) = 0)
) ON [PRIMARY]
GO

Note that the relationship between AssetInfo and Info is 1-to-1,
though I have left out constraints that specifically enforce that as
they don't affect the problem.

3. The View. I tried two versions:

a) This one uses NULLIFs to "filter out" the PK cols that cannot be
NULL and caused the "Doesn't satisfy criteria" error to be returned.
As I understand it, the WITH VIEW_METADATA statement is critical, as
it's what provides the schema info so that Access can update directly
gainst the view instead of going against the individual base tables
which would obviously never work.

CREATE VIEW dbo.vwAssetInfo
WITH VIEW_METADATA
AS
SELECT NULLIF (dbo.AssetInfo.AssetInfoID, NULL) AS AssetInfoID,
dbo.AssetInfo.AssetID, NULLIF (dbo.Info.InfoID, NULL) AS InfoID,
dbo.Info.Description, NULLIF (dbo.AudioClipFile.AudioClipFileID, NULL)
AS AudioClipFileID,
dbo.AudioClipFile.FileName
FROM dbo.AssetInfo INNER JOIN
dbo.Info ON dbo.AssetInfo.InfoID =
dbo.Info.InfoID LEFT OUTER JOIN
dbo.AudioClipFile ON dbo.Info.InfoID =
dbo.AudioClipFile.InfoID

b) This one eliminates the NULLIFs but causes errors like: "The column
AssetInfoID in table AssetInfo ca not be NULL"

CREATE VIEW dbo.vwAssetInfo
WITH VIEW_METADATA
AS
SELECT dbo.AssetInfo.AssetInfoID, dbo.AssetInfo.AssetID,
dbo.Info.InfoID, dbo.Info.Description,
dbo.AudioClipFile.AudioClipFileID,
dbo.AudioClipFile.FileName
FROM dbo.AssetInfo INNER JOIN
dbo.Info ON dbo.AssetInfo.InfoID =
dbo.Info.InfoID LEFT OUTER JOIN
dbo.AudioClipFile ON dbo.Info.InfoID =
dbo.AudioClipFile.InfoID

The outer join is because the AudioClipFile table is initially
unpopulated.

4. The Triggers:

CREATE TRIGGER trAssetInfoInsert ON [dbo].[vwAssetInfo]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Ident int

-- Check to see whether any rows are actually being inserted.
-- Could have been filtered by WHERE clause.
IF (SELECT COUNT(*) FROM Inserted) = 0 RETURN

-- We can only support single row inserts.
IF (SELECT count(*) FROM Inserted) > 1
BEGIN
PRINT 'Only one vwAssetInfo record at a time can be inserted'
RETURN
END

-- First insert the Info row.
INSERT INTO Info (InfoTypeID, Description)
SELECT 1,
i.Description
FROM Inserted AS i
IF @@ROWCOUNT = 0
RAISERROR('Cannot peform Info insertion', 10, 1)

-- Then, using the generated InfoID, insert the AssetInfo row.
SET @Ident = @@IDENTITY

INSERT INTO AssetInfo (AssetID, InfoID)
SELECT i.AssetID,
@Ident
FROM Inserted AS i
IF @@ROWCOUNT = 0
RAISERROR('Cannot peform AssetInfo insertion', 10, 1)

INSERT INTO AudioClipFile (FileName, InfoID)
SELECT i.FileName,
@Ident
FROM Inserted AS i
IF @@ROWCOUNT = 0
RAISERROR('Cannot peform AudioClipFile insertion', 10, 1)

END

CREATE TRIGGER trAssetInfoUpdate ON [dbo].[vwAssetInfo]
INSTEAD OF UPDATE
AS
BEGIN
-- Check to see whether any rows are actually being updated.
-- Could have been filtered by WHERE clause.
IF (SELECT COUNT(*) FROM Inserted) = 0 RETURN

-- First update the Info row.
IF UPDATE(Description)
BEGIN
UPDATE Info
SET Description = i.Description
FROM Inserted AS i
JOIN Info
ON i.InfoID = Info.InfoID
IF @@ROWCOUNT = 0
RAISERROR('Cannot peform Info update', 10, 1)
END

-- Next update the AssetInfo row.
IF UPDATE(AssetID)
BEGIN
UPDATE AssetInfo
SET AssetID = i.AssetID
FROM Inserted AS i
JOIN AssetInfo
ON i.AssetInfoID = AssetInfo.AssetInfoID
IF @@ROWCOUNT = 0
RAISERROR('Cannot peform AssetInfo update', 10, 1)
END

IF UPDATE(FileName)
BEGIN
UPDATE AudioClipFile
SET FileName = i.FileName
FROM Inserted AS i
JOIN AudioClipFile
ON i.InfoID = AudioClipFile.InfoID
IF @@ROWCOUNT = 0
RAISERROR('Cannot peform AssetInfo update', 10, 1)
END
END

I left out the delete trigger as I don't think it will cause problems.

I could send you the Access project, but I think it's just as easy to
setup yourself. Just create a form based on the view and include
AssetID, Description, and AudioClipFileName fields. I have the 3 PKs
on the form as read-only fields, but I suspect that's optional. I have
the form setup as a tabular view, but I doubt that matters.

bso...@microsoft.com (Brian M. Sockey [MS]) wrote in message news:<aveD82L7BHA.1428@cpmsftngxa07>...

[Brian M. Sockey [MS]]

Hi Rob,

There are definitely multiple problems that can arise when trying to insert records into a view using an ADP.
You are right that the VIEW_METADATA option is necessary for Access to use the view as opposed to the
base tables, and instead of triggers are necessary for the data to get inserted correctly when using
VIEW_METADATA.

I didn't have any problem updating the view without the NullIff (even without triggers) unless I tried to insert a
new one. The NullIff statements seem to cause Access to deem the whole view nonupdateable with the
VIEW_METADATA option but I can update just fine without it. Adding the PK fields to the view fixes the
updating problem but not the inserting. However, I couldn't get the insteadof insert trigger you posted to
work (even from EM), and I have not yet had time to try writing my own.

The basic problem is that even if I include the PK fields and use VIEW_METADATA I get a SQL Server
error trying to insert into the view that the view is not updateable because it effects multiple base tables. I
get this error from EM and QA, as well, so obviously it won't work in Access until it's working within SQL
Server.

If I have time over the next couple of days, I'll try to see if I can get an insteadof insert trigger working.

I hope this helps! If you have additional questions on this topic, please reply to this posting.

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
© 2001 Microsoft Corporation. All rights reserved.

--------------------
| From: rob...@comcast.net (Rob Oaks)
| Newsgroups: microsoft.public.access.adp.sqlserver

| Subject: Re: "Doesn't satisfy criteria" error on Access update of updateable view with INSTEAD OF trigger
| Date: 26 Apr 2002 09:01:59 -0700

[Rob Oaks]

Thanks so much for working on this. I'm not sure why the Insert
trigger's not working for you. It's probably because I tried to
"simplify things a bit." Anyway, here's what I've figured out (I'm
just dealing with Inserts for the moment).

I got rid of the NULLIFs in the view and instead just put code in the
BeforeUpdate event to put -1 (any value will work) in the AssetInfoID
and InfoID PKs. This is an updateable view technique that is used in
books like "Professional SQL Server Programming" (Wrox). As you point
out, Access doesn't like the NULLIFs at all and I'm not quite sure
why.

I, of course, get the same "Doesn't satisfy criteria" error. It was at
this point that I realized I needed to understand things "under the
covers" a bit better. I looked at a Profiler trace and I think I see
what's going on. The Trace looks like this:

exec sp_executesql N'INSERT INTO "RealityTour"."dbo"."vwAssetInfo"
("AssetInfoID","AssetID","InfoID","Description") VALUES
(@P1,@P2,@P3,@P4)', N'@P1 int,@P2 int,@P3 int,@P4 nvarchar(13)', -1,
3, -1, N'palestra test'
go
SELECT @@IDENTITY
go
exec sp_executesql N'SELECT * FROM ( SELECT * FROM "dbo"."vwAssetInfo"
) AS DRVD_TBL WHERE "AssetInfoID" = @P1 AND "InfoID" = @P2 AND
"AudioClipFileID" = @P3', N'@P1 int,@P2 int,@P3 int', -1, -1, 20
go

To get some background, look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q275090

From the article: This behavior occurs because the @@IDENTITY function
is used to resynchronize data after new records are added. This
function returns the last identity column value created in the current
session.

If you look at select from the trace, you'll see that the WHERE clause
is totally screwed up. It's looking for records with InfoIDs and
AssetInfoIDs of -1 and a AudioClipFileID (I may not have included this
table as part of the view for simplicity) of 20, which is actually the
@@IDENTITY value from the AssetInfo insert within the trigger.

Also, look at

http://groups.google.com/groups?hl=en&lr=lang_en&threadm=OJjCWTyfBHA.1896%40tkmsftngp05&rnum=4&prev=/groups%3Fhl%3Den%26lr%3Dlang_en%26q%3Dresynccommand%26meta%3Dgroup%253Dmicrosoft.public.access.adp.sqlserver

This is a similar situation. I'm thinking that I need to handle the
error (i.e. ignore it) and then do a requery or something after the
insert. Perhaps you have some other ideas. I assume there's no way to
"help" Access figure out how to requery the row?

Rob

bso...@microsoft.com (Brian M. Sockey [MS]) wrote in message news:<40zdHMA8BHA.1428@cpmsftngxa07>...

[Brian M. Sockey [MS]]

Hi Rob,

I imagine that using a form will be necessary at some point because that's the only place you can specify
the resync command that Access uses for the newly inserted record. Because of the way your table
structure is, I imagine Access will always have problems with inserting directly into the view. However, once
I get an example working right within SQL Server, then I can see if I can find the right resync command to get
it working in an Access form. The problem is that Access just doesn't take triggers into consideration very
well and it doesn't always handle the resync effectively.

I hope this helps! If you have additional questions on this topic, please reply to this posting.

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
© 2001 Microsoft Corporation. All rights reserved.

--------------------
| From: rob...@comcast.net (Rob Oaks)
| Newsgroups: microsoft.public.access.adp.sqlserver
| Subject: Re: "Doesn't satisfy criteria" error on Access update of updateable view with INSTEAD OF trigger

| Date: 30 Apr 2002 08:33:50 -0700

[Brian M. Sockey [MS]]

Hi Rob,

Well, it doesn't look like there's going to be an easy way to get the inserts working within Access if you are
using AutoNumber primary key fields.

To make the multi-table view allow inserts within Access when using WITH VIEW_METADATA, the primary
keys for all the underlying tables need to be included in the view. Fine.

However, for the view insert to even get to the point where the trigger kicks in, you need to specify a value
for the PK fields, even if they are disregarded by the trigger. Otherwise you get a SQL Server error: can't
insert Null values into PK Field. This works fine from SQL EM because you can type bogus values into the
PK fields and the insert ignores them and succeeds just fine. Unfortunately, one of Access' "user-friendly"
features is that you can't type into AutoNumber PK fields. So, you can't insert records without the PK fields
and you can't insert records with PK fields (if they are identity fields).

So option 1 is to make the PK fields non-identity fields and option 2 is to handle the update
programmatically and then requery the form. I think this is one of those times when an unbound form may
be the way to go.

Regarding the update problems, there is a Resync Command property for the form where you can specify
a select statement for Access to use. This won't help for inserts because Access doesn't let us get that far,
but it might help your updates.

I hope this helps! If you have additional questions on this topic, please reply to this posting.

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
© 2001 Microsoft Corporation. All rights reserved.

--------------------
| From: rob...@comcast.net (Rob Oaks)
| Newsgroups: microsoft.public.access.adp.sqlserver
| Subject: Re: "Doesn't satisfy criteria" error on Access update of updateable view with INSTEAD OF trigger

| Date: 30 Apr 2002 08:33:50 -0700

| Thanks so much for working on this. I'm not sure why the Insert