Re: Trigger Question
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 12/07/04
- Next message: Steve Kass: "Re: Query HELP!!!"
- Previous message: Michael C: "Re: Statements with Go don't work in VB"
- In reply to: vul: "Re: Trigger Question"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 6 Dec 2004 23:15:40 -0600
Not a problem. Everyone has to do this stuff for the first time once :)
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "vul" <aaa@optonline.net> wrote in message news:uqsLIQ%232EHA.4004@tk2msftngp13.phx.gbl... > Thank you very much. > Sorry for my stupidity. Before to ask you a question I had to check if the > trigger works properly. I just decided that if my trigger for delete was > working wrong then this one should work wrong too. But it works properly. > Sorry again and thank you very much again > Al > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:#m4H2C92EHA.3820@TK2MSFTNGP11.phx.gbl... >> I don't quite understand the question, but it seems that it will work >> just >> fine for multiple rows. >> >> -- >> -------------------------------------------------------------------------- > -- >> Louis Davidson - drsql@hotmail.com >> SQL Server MVP >> >> Compass Technology Management - www.compass.net >> Pro SQL Server 2000 Database Design - >> http://www.apress.com/book/bookDisplay.html?bID=266 >> Note: Please reply to the newsgroups only unless you are interested in >> consulting services. All other replies may be ignored :) >> >> "vul" <aaa@optonline.net> wrote in message >> news:Oc1KkmY2EHA.2572@tk2msftngp13.phx.gbl... >> > Louis, I need some more help please >> > Another trigger: >> > >> > CREATE TRIGGER Ordr_FakeDelete ON [dbo].[Ordr] >> > AFTER UPDATE >> > AS >> > IF UPDATE(RowDeleted) >> > BEGIN >> > >> > UPDATE OrderDetail >> > SET RowDeleted = inserted.RowDeleted >> > FROM OrderDetail >> > JOIN inserted ON >> > inserted.OrderID = OrderDetail.OrderID >> > >> > END >> > >> > This trigger should update RowDeleted field in OrderDetail table with > the >> > same value when RowDeleted field in records in Parent table Ordr are >> > updated. >> > How do I need to rewrite the above trigger to make it work whe multiple >> > records in Ordr table are updated? >> > Thank you >> > Al >> > >> > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message >> > news:OpzEDnW2EHA.2644@TK2MSFTNGP11.phx.gbl... >> >> The first thing is that if you delete > 1 row this will not work. >> >> Rewrite >> >> as: >> >> >> >> CREATE TRIGGER Ordr_Delete ON [dbo].[Ordr] >> >> FOR DELETE >> >> AS >> >> DELETE FROM OrderDetail >> >> WHERE OrderDetail.OrderID in (select orderId from Deleted) >> >> >> >> Second. You probably don't even need a trigger, you could use a > foreign >> > key >> >> constraint using ON DELETE CASCADE >> >> >> >> >> >> Can you post a script creating the table with some minimal data and >> >> how >> > you >> >> are not seeing it work? It will be easier to see what might be going > on >> >> rather than just firing off guesses. >> >> >> >> -- >> >>> ------------------------------------------------------------------------- > - >> > -- >> >> Louis Davidson - drsql@hotmail.com >> >> SQL Server MVP >> >> >> >> Compass Technology Management - www.compass.net >> >> Pro SQL Server 2000 Database Design - >> >> http://www.apress.com/book/bookDisplay.html?bID=266 >> >> Note: Please reply to the newsgroups only unless you are interested in >> >> consulting services. All other replies may be ignored :) >> >> >> >> "vul" <aaa@optonline.net> wrote in message >> >> news:OlD2XUW2EHA.1392@tk2msftngp13.phx.gbl... >> >> > Thank you. >> >> > Everything looks correct for me. But trigger doesn't work as I >> >> > expected. >> >> > Al >> >> > >> >> > "Rajesh Peddireddy" <anonymous@discussions.microsoft.com> wrote in >> > message >> >> > news:052201c4d958$fe439d50$a501280a@phx.gbl... >> >> >> Hey >> >> >> >> >> >> Just check the login name and owner of the object related >> >> >> stuff.. >> >> >> >> >> >> Its just an idea .. >> >> >> >> >> >> Thanks >> >> >> Rajesh Peddireddy >> >> >> >> >> >> >> >> >> >> >> >> >-----Original Message----- >> >> >> >I have a trigger: >> >> >> > >> >> >> >CREATE TRIGGER Ordr_Delete ON [dbo].[Ordr] >> >> >> >FOR DELETE >> >> >> >AS >> >> >> >DECLARE @OrderID int >> >> >> >SELECT @OrderID=OrderID FROM deleted >> >> >> >DELETE FROM OrderDetail WHERE OrderDetail.OrderID=@OrderID >> >> >> > >> >> >> >If I delete a record from table Ordr directly in EM or >> >> >> run delete statement >> >> >> >from VB6 application then all records from OrderDetail >> >> >> table are deleted >> >> >> >with no problem too. So everything is as I expected. >> >> >> >If I run delete statement from Query Analyzer then the >> >> >> record from Ordr >> >> >> >table is deleted, but records in OrderDetail table remain >> >> >> untouched. >> >> >> >Why? >> >> >> >Thank you >> >> >> >Al >> >> >> > >> >> >> > >> >> >> >. >> >> >> > >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >
- Next message: Steve Kass: "Re: Query HELP!!!"
- Previous message: Michael C: "Re: Statements with Go don't work in VB"
- In reply to: vul: "Re: Trigger Question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
