Perhaps you could post some DDL detailing the table definitions so I don't
have to guess, but do you want to delete *all* the rows in the child table
or just the rows whose 'PipeLineID' matches the 'PipeLineID' in the parent?
The where clause in your example code confuses me.

From your use of the terms 'Parent' and 'Child' I'll assume that for each
row in the parent there may be zero or more rows in the child with the same
'PipeLineID'.  That is, there is a foreign key relationship between
T720Pipeline_Copy and T720PipelineArchive_Copy (you *do* have one there,
don't you?).

So, whenever you need to delete an entry in the parent, you want to delete
*all* the associated entries in the child.  This is a typical use of
parent/child relationships, so I'll assume this is what you want.

Someone else posted that you might want to consider using ON DELETE CASCADE
on the foreign key relationship.  You may want to consider doing that
instead of using a trigger; it's really much simpler.  DELETE from
T720Pipeline_Copy and the corresponding entries in T720PipelineArchive_Copy
automagically get deleted too.

Frankly, I tend to use a stored procedure instead of a trigger to do this
kind of delete, defining it as:

	Create procedure DeletePipelineData
		@PiplineID integer
	as
		set nocount on
		DELETE T720PipelineArchive_Copy WHERE PipeLineID=@PipeLineID;
		DELETE T720Pipeline_Copy WHERE PipeLineID=@PipeLineID;

and I'll wrap the execution of this in a transaction to insure that either
both or neither delete takes place.  You can define the transaction in the
SP but I tend to do it in the App, where I can control the transaction scope
across potential multiple stored procedure invocations.

I don't use ON DELETE CASCADE because I don't like magic; I like to control
what's happening explicitly. YMMV.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Dave Parkins [mailto:dave.parkins@m...]
Sent: Tuesday, July 16, 2002 7:58 PM
To: sql language
Subject: [sql_language] RE: delete trigger


Jeff, thank you for your reply. Below is what I have and it's not quite
working the way I need it to:

CREATE TRIGGER RG720_Delete ON T720Pipeline_Copy
FOR INSERT, UPDATE, DELETE
AS
Delete T720PipelineArchive_Copy from T720PipelineArchive_Copy
		INNER JOIN T720Pipeline_Copy ON
		T720PipelineArchive_Copy.PipelineID 
T720Pipeline_Copy.PipelineID
where T720PipelineArchive_Copy.PipelineID <>T720Pipeline_Copy.PipelineID

When I delete a record from the parent table(T720Pipeline_Copy), I need
the trigger to delete all the records from the child table
(T720PipelineArchive_Copy). The common field name is PipelineID. When I
run the above trigger, it doesn't delete any records from the child table
and it doesn't error on me either. Is there anything you could add to it
maybe. Thanks again for all your help.

Sincerely,
Dave