Re: Can We Explain Missing Rows?
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/11/04
- Next message: dw: "Re: Cursor vs. table data type"
- Previous message: verbani: "Re: Partitioned views"
- In reply to: Mac McMicMac: "Re: Can We Explain Missing Rows?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 11 Jul 2004 08:58:25 -0500
I'm glad it helped.
-- Dan Guzman SQL Server MVP "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 > > > > > > > > > > > >
- Next message: dw: "Re: Cursor vs. table data type"
- Previous message: verbani: "Re: Partitioned views"
- In reply to: Mac McMicMac: "Re: Can We Explain Missing Rows?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
