Re: Trigger Question

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: vul (aaa_at_optonline.net)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 17:36:30 -0500

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
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Test
    ... I believe the trigger below will accomplish the desired ... FROM Ordr o ... JOIN OrderDetail od ON ... please provide DDL ...
    (microsoft.public.sqlserver.programming)
  • Re: Test
    ... Dan, ... I'm doing these cascade updates in order to update RowDeleted field in ... writing Trigger for OrderDetail table. ... will be updated then it will fire trigger in Sopa table, then in Ordr table, ...
    (microsoft.public.sqlserver.programming)
  • Re: Test
    ... > I'm doing these cascade updates in order to update RowDeleted field in ... > will be updated then it will fire trigger in Sopa table, then in Ordr ... > then in OrderDetail table and so on until the end of the chain. ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger Question
    ... This trigger should update RowDeleted field in OrderDetail table with the ... same value when RowDeleted field in records in Parent table Ordr are ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger Question
    ... Pro SQL Server 2000 Database Design - ... > UPDATE OrderDetail ... > This trigger should update RowDeleted field in OrderDetail table with the ... > same value when RowDeleted field in records in Parent table Ordr are ...
    (microsoft.public.sqlserver.programming)