Trigger causes "data won't be displayed" warning

[eksortso]

Can somebody explain something to me about how Access 2003 works with triggers and foreign keys? Let me explain what I'm trying to do.

I'm running Access 2003, with a back-end database on SQL Server 2000. In this database, I have a Products table with a one-to-many relationship to a Categories table. Both tables have IDENTITY columns (AutoNumber fields) for primary keys, and the Categories table has a foreign key which references the Products' IDENTITY column. The foreign key is set to cascase deletes and updates.

In addition, there is an AFTER INSERT, UPDATE trigger on the Categories table. When records are added or modified in the Categories table, the Product ID is put into a Changes table.

But the trigger is causing me grief. If I open the Categories table in Access and add a new Categories record for a Product that already has one record in Categories, then Access gives me this warning: "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source." There are no filters on this table; I get the warning when I add a 2nd category record, but I can add a category record for a product without one without getting the warning. And when I drop the trigger that adds records to the Changes table, I stop getting the warnings.

Why is it doing this? Is Access unable to handle this sort of trigger properly? I can share the SQL code if you need to see the details.

I could find alternate (and inelegant) ways of tracking changes, but I was hoping that this trigger would solve my problems. What's happening?

 

[lupins46]

Don't understand your data structure.
'and the Categories table has a foreign key which references the Products' IDENTITY column.

So a Category can only contain one product?

 

[eksortso]

By that, I meant that the Categories table has a field which must reference the Products table. Like I said, each Product may have more than one Category.

[blue]ALTER TABLE[/blue] Categories
  [blue]ADD CONSTRAINT[/blue] [FK_Categories_Products]
  [blue]FOREIGN KEY[/blue] (ProductID)
  [blue]REFERENCES[/blue] Products (ProductID)
  [blue]ON DELETE CASCADE  ON UPDATE CASCADE[/blue]

Here's the code for the trigger that was causing me trouble.

[blue]CREATE TRIGGER[/blue] TG_Categories_InsUpd
[blue]ON[/blue] Categories
[blue]AFTER INSERT, UPDATE
AS
INSERT INTO[/blue] Changes (
    ProductID, CategoryChanged)
[blue]SELECT DISTINCT[/blue]
    i.ProductID,
    1 [blue]AS[/blue] CategoryChanged
[blue]FROM[/blue] inserted [blue]AS,[/blue] i

Can you see why this might be causing problems in my Access ADP? Could it be the IDENTITY field in the Categories table?

 

[eksortso]

Whoops, I just noticed a typo in the trigger code. The last line should read:

[blue]FROM[/blue] inserted [blue]AS[/blue] i

Anyone have any ideas about what's going on?