I need help with an update trigger.

[rarubio1]

I wrote a trigger that inserts or updates a record when
the value of the "Secured" field is equal to 1.

Here is a step-by-step of the trigger:
Step 1: Check to see if the "Secured" field of the updated
record has a value of 1. If it is, then proceed with the trigger otherwise do nothing.
Step 2: Check to see if the record exists in tblPartNumber. I do this by getting a count of the records based on the PartNumberID. If the count is 0 then the record is inserted in tblPartNumber. If the count is not 0 then the record is updated.
Step 3: Insert the record if the returned value was 0.
Step 4: Update the record if the returned value was not 0.

THE PROBLEM I AM HAVING IS THAT A RECORD IS INSERTED
MORE THAN ONCE WHEN THE RETURNED VALUE = 0. DO I HAVE TOO
MANY BEGIN and END statements? CAN SOMEONE PLEASE TELL
ME WHAT I AM DOING WRONG?

the following is the syntax for my update trigger:

CREATE TRIGGER trgUpdatePart ON [dbo].[Inventory]
FOR UPDATE
AS

BEGIN
--STEP 1
IF (SELECT S.Secured FROM INSERTED S) = 1
BEGIN
--STEP 2
IF (SELECT Count(PartNumberID)
FROM Scheduling..tblPartNumber, INSERTED S
WHERE PartNumberID = S.InvtID) = 0
--STEP 3
--IF The returned value equals 0 then insert the the record.
BEGIN
INSERT Scheduling..tblPartNumber(PartNumberID,Secured)
SELECT S.InvtID, S.Secured
FROM INSERTED S, Scheduling..tblPartNumber083004
END
ELSE
--STEP 4
--IF The returned value is not 0 then update the the record.
BEGIN
UPDATE Scheduling..tblPartNumber
SET Secured = S.Secured
FROM INSERTED S,Scheduling..tblPartNumber083004 P
WHERE P.fkPartNumberID = S.InvtID
END
END
END

 

[SQLSister]

I know this sounds dumb, but is there another trigger on this table or a related table that may also be doing the same thing?

Questions about posting. See faq183-874