Problem: Add indexed view will suppress the after trigger to get fired.
Step to Reproduce:
Create TableA, TableB, TableC and TableD with after trigger in each one.
TableA is parent table for TableB with Foreign key Cascade Delete.
TableB is parent table for TableC with Foreign key Cascade Delete.
TableC is parent table for TableD with Foreign key Cascade Delete.
Test Case 1.
Delete from TableA
TableA, TableB, TableC, and TableD's After delete trigger will get fired cor
rectly.
Test Case 2.
Add indexed view in TableC
Delete from TableA
TableA, TableB, and TableD's After delete trigger will get fired correctly
but miss TableC's After trigger.
If anyone has any suggestion, that will be appreciated.
----
--
-- Sample sql
----
--
-- Clean Out
IF OBJECT_ID (N'[dbo].[TableC_VW]') IS NOT NULL
DROP View [dbo].[TableC_VW]
GO
IF OBJECT_ID (N'TableD') IS NOT NULL
DROP TABLE TableD
GO
IF OBJECT_ID (N'TableC') IS NOT NULL
DROP TABLE TableC
GO
IF OBJECT_ID (N'TableB') IS NOT NULL
DROP TABLE TableB
GO
IF OBJECT_ID (N'TableA') IS NOT NULL
DROP TABLE TableA
GO
Create TABLE TableA
(
ID int not null,
dummy1 varchar(20) null default 'dummy1',
dummy2 varchar(20) null default 'dummy2',
dummy3 varchar(20) null default 'dummy3',
CONSTRAINT [PK-TableA_-ID] PRIMARY KEY CLUSTERED ( [ID] )
)
GO
CREATE TRIGGER [dbo].[TableA_Delete]
ON [dbo].[TableA]
AFTER DELETE
AS
begin
print 'Trigger TableA_Delete'
end
GO
Create TABLE TableB
(
ID int not null,
TableAId int null,
dummy1 varchar(20) null default 'dummy1',
dummy2 varchar(20) null default 'dummy2',
dummy3 varchar(20) null default 'dummy3',
CONSTRAINT [UK-TableB-ID] Unique ( [ID] ),
CONSTRAINT [FK-TableB-TableAId_TableA-ID]
FOREIGN KEY ( [TableAId] ) REFERENCES [TableA] ( [ID] ) ON DELETE CASCADE
)
GO
CREATE TRIGGER [dbo].[TableB_Delete]
ON [dbo].[TableB]
AFTER DELETE
AS
begin
print 'Trigger TableB_Delete'
end
GO
Create TABLE TableC
(
ID int not null,
TableBId int null,
dummy1 varchar(20) null default 'dummy1',
dummy2 varchar(20) null default 'dummy2',
dummy3 varchar(20) null default 'dummy3',
CONSTRAINT [PK-TableC-ID] PRIMARY KEY CLUSTERED ( [ID] ),
CONSTRAINT [FK-TableC-TableBId_TableB-ID]
FOREIGN KEY ( [TableBId] ) REFERENCES [TableB] ( [ID] ) ON DELETE CASCADE
)
GO
CREATE TRIGGER [dbo].[TableC_Delete]
ON [dbo].[TableC]
AFTER DELETE
AS
begin
print 'Trigger TableC_Delete'
end
GO
Create TABLE TableD
(
ID int not null,
TableCId int null,
dummy1 varchar(20) null default 'dummy1',
dummy2 varchar(20) null default 'dummy2',
dummy3 varchar(20) null default 'dummy3',
CONSTRAINT [PK-TableD-ID] PRIMARY KEY CLUSTERED ( [ID] ),
CONSTRAINT [FK-TableD-TableCId_TableC-ID]
FOREIGN KEY ( [TableCId] ) REFERENCES [TableC] ( [ID] ) ON DELETE CASCADE
)
GO
CREATE TRIGGER [dbo].[TableD_Delete]
ON [dbo].[TableD]
AFTER DELETE
AS
begin
print 'Trigger TableD_Delete'
end
GO
----
-- Test 1 Delete from TableA without indexd view schema binding which case t
he TableC After delete trigger has fired.
----
Print 'Delete from TableA without indexd view schema binding which case the
TableC After delete trigger has fired.'
Set NoCount On
Insert Into TableA (ID) values(1)
Insert Into TableB (ID, TableAId) values(1,1)
Insert Into TableC (ID, TableBId) values(1,1)
Insert Into TableD (ID, TableCId) values(1,1)
delete from TableA
----
-- Test 2 Delete from TableA with indexd view schema binding which case the
TableC After delete trigger has not fired.
----
Print 'Test 2 Delete from TableA with indexd view schema binding which case
the TableC After delete trigger has not fired.'
Insert Into TableA (ID) values(1)
Insert Into TableB (ID, TableAId) values(1,1)
Insert Into TableC (ID, TableBId) values(1,1)
Insert Into TableD (ID, TableCId) values(1,1)
IF OBJECT_ID (N'[dbo].[TableC_VW]') IS NOT NULL
DROP View [dbo].[TableC_VW]
GO
CREATE VIEW [dbo].[TableC_VW] WITH SCHEMABINDING
AS
SELECT TableBId, C.Dummy1, C.Dummy2, C.Dummy3
FROM [dbo].TableC C inner join [dbo].TableB B on B.ID = B.TableAId
WHERE TableBID Is Not Null
GO
CREATE UNIQUE CLUSTERED INDEX [IX_TableC_VW]
ON [dbo].[TableC_VW] (TableBID, Dummy1,Dummy2,Dummy3)
Go
delete from TableA
----
----Notes:
Sql 2000 sp4 get this trouble.
Sql 2005 is good without this issue.
Saturday, February 25, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment