Can We Explain Missing Rows?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mac McMicMac (Tortelli_at_Tortilla.gov)
Date: 07/11/04 

Date: Sat, 10 Jul 2004 20:49:47 -0700

My database has TableA and TableB (among many others). Schema information
is at the bottom of this message

Table A contains a DELETE trigger that removes rows from TableB (TableA has
no other triggers)

The problem: I just discovered that TableA is missing a bunch of rows that I
did not explicitly delete. Granted, I'm in a development mode and it's
entirely possible that a stored procedure in development could have deleted
the rows from TableA without me realising it at the time. The confusing part
to me is that the associated rows in TableB still exist. That is, the rows
were removed from TableA and apparently the DELETE trigger was *not* fired
to remove the corresponding rows from TableB.

FWIW: I just did a bunch of regression testing on all functionality that can
cause insertions, updates, and deletions to Tables A and B - and nothing was
deleted unexpectedly (everything worked as designed). Furthermore, the
DELETE trigger is still intact and functioning correctly. I have not
modified that trigger or the schema of Tables A and B for over 2 months (so
it's not like I'm in the middle of a bunch of changes which could be
possible explanations).

At the end of the day, I'm left with the fact that a bunch of rows
disappeared from TableA without corresponding rows in TableB getting removed
per the DELETE trigger.

I'm wondering how this could have happened. Put another way, how can rows be
deleted from TableA in such a way as to prevent the DELETE trigger from
firing? Knowing that might give me some clues.

Any ideas?

Thanks!

-----------------------------------------------------------------------
-- "TableA" as described above
-----------------------------------------------------------------------
CREATE TABLE [dbo].[UnitComponents] (
 [StoreID] [int] NOT NULL ,
 [ComponentID] [int] IDENTITY (1, 1) NOT NULL ,
 [ParentComponentID] [int] NULL ,
 [ComponentDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
... and others
)
-----------------------------------------------------------------------
-- "TableB" as described above
-----------------------------------------------------------------------
CREATE TABLE [dbo].[AccessController] (
 [AccessControllerID] [int] IDENTITY (1, 1) NOT NULL ,
 [StoreID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ComponentID] [int] NOT NULL ,
 [PrincipalID] [int] NOT NULL ,
 [PrincipalType] [tinyint] NOT NULL
... and others
)

-----------------------------------------------------------------------
-- Trigger
-----------------------------------------------------------------------
CREATE TRIGGER [DELETE_AccessController] ON dbo.UnitComponents
FOR DELETE
AS
DECLARE
    @StoreID int,
    @ComponentID int

SELECT @StoreID = StoreID, @ComponentID = ComponentID FROM DELETED

EXEC DeleteAccessController @StoreID, @ComponentID

--END OF TRIGGER

-----------------------------------------------------------------------
-- SP executed by DELETE trigger above
-----------------------------------------------------------------------
CREATE Procedure DeleteAccessController
(
    @StoreID int,
    @ComponentID int
)
AS
SET NOCOUNT ON

-- This SP is called from DELETE_AccessController ON dbo.UnitComponents

DELETE FROM AccessController

WHERE
    StoreID = @StoreID
    AND
    ComponentID = @ComponentID

-- end of sp

Relevant Pages

  • Re: Trigger on Insert for Current Record Only
    ... I was looking to create a trigger on tableA which on insert will ... write data into TableB. ... Using Access form i will have user enter data into TableB. ...
    (microsoft.public.sqlserver.server)
  • Re: Non Updatable View
    ... May be an INSTEAD OF TRIGGER could help. ... create table tableA( ... create table tableB( ... create trigger tr_vwMyView_IO_INSERT on vwMyView ...
    (microsoft.public.sqlserver.programming)
  • Datetime Semantics
    ... (TableA has a trigger which modifies TableA_History) ... (TableB has a trigger which modifies TableB_History) ... I'm worried that the datetime stamp in TableA_History ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger on Insert for Current Record Only
    ... But your trigger is on TableA and inserts into TableA. ... It sounds like you want the trigger on TableB to insert ... A trigger is a real-time mechanism. ...
    (microsoft.public.sqlserver.server)
  • RE: Enforce uniqueness of a key referenced in multiple tables
    ... create function dbo.fnMyFuncB (@Value int) ... insert into tableA default values ... insert into tableB values ... insert into tableC values ...
    (microsoft.public.sqlserver.programming)