FIX: The JOIN queries in the triggers that involve the inserted table or the deleted table may return results that are not consistent (810920)

MORE INFORMATION

Steps to reproduce the problem

1. In your database, create two tables that are named ParentTable and ChildTable.
2. Create a parent-child relationship between the tables that has a cascading delete reference, and then populate the tables with sample data. To do this, run the following Transact-SQL script in SQL Query Analyzer:

   USE <Database Name>
   GO
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ParentID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
   ALTER TABLE [dbo].[ChildTable] DROP CONSTRAINT FK_ParentID
   GO
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_ChildTable_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
   drop trigger [dbo].[t_ChildTable_Delete]
   GO
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChildTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ChildTable]
   GO
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ParentTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ParentTable]
   GO
   
   CREATE TABLE [dbo].[ChildTable] (
   [ChildID] [int] IDENTITY (1, 1) NOT NULL ,
   [ParentID] [int] NOT NULL 
   ) ON [PRIMARY]
   GO
   
   CREATE TABLE [dbo].[ParentTable] (
   [ParentID] [int] IDENTITY (1, 1) NOT NULL 
   ) ON [PRIMARY]
   GO
   
   ALTER TABLE [dbo].[ChildTable] WITH NOCHECK ADD 
   CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED 
   (
   [ChildID]
   ) ON [PRIMARY] 
   GO
   
   ALTER TABLE [dbo].[ParentTable] WITH NOCHECK ADD 
   CONSTRAINT [pk_Parent] PRIMARY KEY CLUSTERED 
   (
   [ParentID]
   ) ON [PRIMARY] 
   GO
   
   ALTER TABLE [dbo].[ChildTable] ADD 
   CONSTRAINT [FK_ParentID] FOREIGN KEY 
   (
   [ParentID]
   ) REFERENCES [dbo].[ParentTable] (
   [ParentID]
   ) ON DELETE CASCADE 
   GO
   
   SET QUOTED_IDENTIFIER OFF 
   GO
   SET ANSI_NULLS ON 
   GO
   
   SET IDENTITY_INSERT ParentTable ON
   GO
   INSERT INTO ParentTable (ParentID) VALUES (1)
   INSERT INTO ParentTable (ParentID) VALUES (2)
   GO
   SET IDENTITY_INSERT ParentTable OFF
   GO
   INSERT INTO ChildTable (ParentID) VALUES (1)
   INSERT INTO ChildTable (ParentID) VALUES (2)
   GO
   
   SET QUOTED_IDENTIFIER ON 
   GO
   SET ANSI_NULLS ON 
   GO
   

3. Create a DELETE trigger on the child table. To do this, run the following Transact-SQL script in SQL Query Analyzer:

   CREATE TRIGGER t_ChildTable_Delete
   ON ChildTable
   FOR DELETE
   AS 
   	SELECT * FROM deleted INNER JOIN ParentTable on deleted.ParentID = ParentTable.ParentID
   	SELECT COUNT(*) FROM deleted INNER JOIN ParentTable on deleted.ParentID = ParentTable.ParentID
   	
   IF EXISTS(SELECT * FROM deleted INNER JOIN ParentTable on deleted.ParentID = ParentTable.ParentID)
   		PRINT 'Exists returned true'
   	ELSE 
   		PRINT 'Exists returned false'
   
   RETURN
   GO

4. Delete data from the ParentTable table:

   DELETE FROM parenttable WHERE parentid=2