|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trigger: When deleting more then 1 row it doesn't triggerI have this ALTER TRIGGER [mytableDelete] ON [dbo].[mytable] FOR DELETE AS SET NOCOUNT ON DECLARE @user_id int SELECT @user_id=user_id FROM deleted DELETE FROM users WHERE user_id = @pa_id Now this works fine when I only delete one row like this DELETE FROM mytable WHERE id=1 But if I delete more than one row at once, my trigger does not work. DELETE FROM mytable WHERE id in (1, 3, 4, 5) What is wrong? Thanks for any help in advance! Regards Andreas Klemt This line is likely your problem as it will set @user_id to the user_id
column from ONE row in the deleted table. SELECT @user_id=user_id FROM deleted Also, I see the variable @pa_id in the delete statement, but, it is not declared or given a value iin the trigger. If you want to delete all rows from the users table where the user_id column = any of the values in the user_id column of all rows in the deleted table, then, thsi is what may work: ALTER TRIGGER [mytableDelete] ON [dbo].[mytable] FOR DELETE AS SET NOCOUNT ON DELETE FROM users WHERE user_id IN (SELECT user_id FROM deleted) Show quote "Andreas Klemt" <aklem***@hotmail.com> wrote in message news:es%23lXEiTGHA.1688@TK2MSFTNGP11.phx.gbl... > Hello, > > I have this > > ALTER TRIGGER [mytableDelete] ON [dbo].[mytable] > FOR DELETE > AS > SET NOCOUNT ON > DECLARE @user_id int > SELECT @user_id=user_id FROM deleted > DELETE FROM users WHERE user_id = @pa_id > > Now this works fine when I only delete one row like this > DELETE FROM mytable WHERE id=1 > > But if I delete more than one row at once, my trigger does not work. > DELETE FROM mytable WHERE id in (1, 3, 4, 5) > > What is wrong? > > Thanks for any help in advance! > > Regards > Andreas Klemt > Hello Dave,
many thanks! That helped me. Kind Regards, Andreas Klemt Show quote "Dave Frommer" <anti@spam.com> schrieb im Newsbeitrag news:Obx$jmiTGHA.1728@TK2MSFTNGP11.phx.gbl... > This line is likely your problem as it will set @user_id to the user_id > column from ONE row in the deleted table. > > SELECT @user_id=user_id FROM deleted > > Also, I see the variable @pa_id in the delete statement, but, it is not > declared or given a value iin the trigger. > > If you want to delete all rows from the users table where the user_id > column = any of the values in the user_id column of all rows in the > deleted table, then, thsi is what may work: > > ALTER TRIGGER [mytableDelete] ON [dbo].[mytable] > FOR DELETE > AS > SET NOCOUNT ON > DELETE FROM users WHERE user_id IN (SELECT user_id FROM deleted) > > "Andreas Klemt" <aklem***@hotmail.com> wrote in message > news:es%23lXEiTGHA.1688@TK2MSFTNGP11.phx.gbl... >> Hello, >> >> I have this >> >> ALTER TRIGGER [mytableDelete] ON [dbo].[mytable] >> FOR DELETE >> AS >> SET NOCOUNT ON >> DECLARE @user_id int >> SELECT @user_id=user_id FROM deleted >> DELETE FROM users WHERE user_id = @pa_id >> >> Now this works fine when I only delete one row like this >> DELETE FROM mytable WHERE id=1 >> >> But if I delete more than one row at once, my trigger does not work. >> DELETE FROM mytable WHERE id in (1, 3, 4, 5) >> >> What is wrong? >> >> Thanks for any help in advance! >> >> Regards >> Andreas Klemt >> > >  |
|||||||||||||||||||||||
Other interesting topics