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
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.