You're assigning a value to @PersonnelID twice:

>SET @PersonnelID = (SELECT PersonnelID FROM Dependants WHERE DependantID = @DependantID)
>
>SET @PersonnelID = (SELECT D.PersonnelID FROM Dependants D, inserted I WHERE D.DependantID = I.DependantID)

Is it possible that the second query is NOT what you want?

BTW, this trigger is written as if it is expected to be called once for each row modified. That is not the way that SQL Server works. SQL Server will call a trigger once, regardless of the number of rows that are modified. Even if the query modified zero (0) rows, your trigger will still be called. You may want to modify your trigger to be more set-oriented.

-Mike

>I started what I thought would be a simple addition of a trigger to a table, but have been frustrated by it, and unable to find the problem.
>I added the trigger (below), which should insert a record into another table if the conditions are met. When the conditions are met, the insert into the other table does happen. The problem is that the original insert into the first table (Dependants) is left without a key ID. It has to have been there for the code to work, but it does not show up in the table, and if I try to delete the line (in table view), I get an error that a row without a KeyID can not be deleted.
>
>If I take out the insert into the 2nd table (DepCare) there are no problems.
>
>CREATE TRIGGER UpdateDepCare ON [dbo].[Dependants]
>FOR INSERT, UPDATE
>AS
>
>DECLARE @DependantID int,
> @PersonnelID int
>
>SET @DependantID = (SELECT D.DependantID FROM Dependants D, inserted I WHERE D.DependantID = I.DependantID)
>SET @PersonnelID = (SELECT PersonnelID FROM Dependants WHERE DependantID = @DependantID)
>
>SET @PersonnelID = (SELECT D.PersonnelID FROM Dependants D, inserted I WHERE D.DependantID = I.DependantID)
>
>IF EXISTS (SELECT DependantID FROM Dependants WHERE PersonnelID = @PersonnelID AND Relation = 'Child')
>BEGIN
> IF NOT EXISTS (SELECT DependantID FROM Dependants WHERE PersonnelID = @PersonnelID AND Relation = 'Spouse') OR
> EXISTS (SELECT DependantID FROM Dependants WHERE PersonnelID = @PersonnelID AND Relation = 'Spouse' AND DpndSSN IS NOT NULL)
> BEGIN
> IF NOT EXISTS (SELECT DepCareID FROM dbo.DepCare WHERE PersonnelID = @PersonnelID)
>--PROBLEM IS HERE =====>> INSERT INTO DepCare (PersonnelID, DepCareCrtRqdYN)
>--PROBLEM IS HERE =====>> VALUES (@PersonnelID, 1)
> ELSE IF ((SELECT TOP 1 DepCareCrtRqdYN FROM dbo.DepCare WHERE PersonnelID = @PersonnelID) = 0)
> UPDATE dbo.DepCare
> SET DepCareCrtRqdYN = 1
> WHERE PersonnelID = @PersonnelID
> END
>END