"Mac McMicMac" <Tortelli@Tortilla.gov> wrote in message
news:eGf3PovZEHA.2388@TK2MSFTNGP11.phx.gbl...
> 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
>
>

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:%23M$gZMwZEHA.3112@tk2msftngp13.phx.gbl...
> Your trigger can only handle a delete statement affecting one row. If
more
> than one row is deleted by a DELETE statement, only one of the
> AccessController rows will be removed and you will have the symptoms you
> describe.
>
> You might revise the trigger like the example below. Also, consider
> creating a foreign key constraint to ensure you can't have orphaned
> AccessController rows.
>
> CREATE TRIGGER [DELETE_AccessController] ON dbo.UnitComponents
> FOR DELETE
> AS
>
> DELETE FROM AccessController
> FROM deleted d
> WHERE
> AccessController.StoreID = d.StoreID
> AND
> AccessController.ComponentID = d.ComponentID
> GO
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Mac McMicMac" <Tortelli@Tortilla.gov> wrote in message
> news:eGf3PovZEHA.2388@TK2MSFTNGP11.phx.gbl...
> > 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
> >
> >
>
>

"Mac McMicMac" <Tortelli@Tortilla.gov> wrote in message
news:%23rTlMUxZEHA.2388@TK2MSFTNGP09.phx.gbl...
> Thank you so much.... now I have something to look for (the thing that
> caused multiple rows to be deleted from UnitComponents) and a way to make
> the system more robust.
>
> -MMMM
>
>
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:%23M$gZMwZEHA.3112@tk2msftngp13.phx.gbl...
> > Your trigger can only handle a delete statement affecting one row. If
> more
> > than one row is deleted by a DELETE statement, only one of the
> > AccessController rows will be removed and you will have the symptoms you
> > describe.
> >
> > You might revise the trigger like the example below. Also, consider
> > creating a foreign key constraint to ensure you can't have orphaned
> > AccessController rows.
> >
> > CREATE TRIGGER [DELETE_AccessController] ON dbo.UnitComponents
> > FOR DELETE
> > AS
> >
> > DELETE FROM AccessController
> > FROM deleted d
> > WHERE
> > AccessController.StoreID = d.StoreID
> > AND
> > AccessController.ComponentID = d.ComponentID
> > GO
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Mac McMicMac" <Tortelli@Tortilla.gov> wrote in message
> > news:eGf3PovZEHA.2388@TK2MSFTNGP11.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>