>>I have a table that has both an update and delete trigger. The delete trigger actually updates data in the table, causing the update trigger to fire. Can I prevent the update trigger from firing when the update is made by the delete trigger?
>>
>>TIA
>>
>>John
>
>Could you post the code for DELETE trigger?

Basically it updates and end date for a status record for a case manager. A status with a null in the end date is the current status.

-- =============================================
-- Author:		jd
-- Create date: 02/25/2009
-- Description:	set end date for previous status after delete
-- Most times this will be called when a single status is being deleted (called from UI)
-- However, if a case manager is being deleted then RI kicks in and tries to run this.
-- It will blow up if multiple rows are returned to set the @CmId and @PrId vars
-- @Cm is no problem because it will always be one CM (from the UI).  If multiple providers
-- are returned however, it just won't work so we return without doing anything.
-- This won't handle multiple CM's from the same provider being deleted with a statement
-- from the query window (or other developer method), so this trigger would have to be
-- disabled to run that
-- =============================================
ALTER TRIGGER [dbo].[tr_cm_status_delete] 
   ON  [dbo].[cm_status] 
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;
	DECLARE @Count INT, @CmId INT, @PrId INT
	-- check to see how many different providers are involved
	SET @Count = (SELECT COUNT(DISTINCT cs_pr_id) FROM deleted)
	IF @Count > 1			-- can't do it
		RETURN 
	ELSE						-- one provider only
		SET @PrId = (SELECT MAX(cs_pr_id) FROM deleted)		-- could be more than one row so we have to use an aggregate function
		
	SET @CmId = (SELECT MAX(cs_cm_id) FROM deleted)				-- could be more than one row so we have to use an aggregate function

	UPDATE cm_status SET cs_enddate = 
	(
		SELECT MIN(cs_date)-1 FROM cm_status c2
		WHERE c2.cs_cm_id = c1.cs_cm_id
		AND c2.cs_pr_id = c1.cs_pr_id
		AND c2.cs_date > c1.cs_date
	) 
	FROM cm_status c1
	WHERE c1.cs_cm_id = @CmId
	AND c1.cs_pr_id = @PrId

END