Private
Troubleshooting Question
I am trying to get this trigger to insert the correct data. I seem to be losing the scope_identity value. The code up to here
SELECT UserID FROM dbo.VHVS_USERS WHERE UserID = SCOPE_IDENTITY()
seems to be working but when I try the else statement it does not have that value.
This part of the code is where I am trying to add a user if the userid, hostid and clientid do not already exist but I am getting red lines under UserID and c.UserID=i.UserID
IF ((SELECT COUNT(*) from Inserted) = 1 )
BEGIN
INSERT INTO VHVS_User_Clinics (UserID,clinicID,hostID)
SELECT UserID, clinicID, hostID
FROM Inserted I
WHERE NOT EXISTS (select 1 from VHVS_User_Clinics c WHERE c.clinicID =i.clinicID AND c.hostID= i.hostID AND c.UserID=i.UserID)
Here is the complete trigger.
IF ((SELECT COUNT(*) from Inserted) = 1 )
BEGIN
INSERT INTO dbo.VHVS_Users (ProviderID,UserFirstName,UserMiddleName,UserLastName)
SELECT ProviderID,ProviderFirstName,ProviderMiddleName,ProviderLastName
FROM Inserted
WHERE ProviderID NOT IN (SELECT ProviderID FROM dbo.VHVS_USERS)
AND hostID NOT IN (SELECT hostID FROM dbo.VHVS_USER_CLINICS VHVS_USERS INNER JOIN
VHVS_USER_CLINICS ON VHVS_USERS.UserID = VHVS_USER_CLINICS.UserID
WHERE
VHVS_USERS.UserID <> VHVS_USER_CLINICS.UserID)
INSERT INTO VHVS_User_Clinics (UserID)
SELECT UserID FROM dbo.VHVS_USERS WHERE UserID = SCOPE_IDENTITY()
END
ELSE
IF ((SELECT COUNT(*) from Inserted) = 1 )
BEGIN
INSERT INTO VHVS_User_Clinics (UserID,clinicID,hostID)
SELECT UserID, clinicID, hostID
FROM Inserted I
WHERE NOT EXISTS (select 1 from VHVS_User_Clinics c WHERE c.clinicID =i.clinicID AND c.hostID= i.hostID AND c.UserID=i.UserID)
IF ((SELECT COUNT(*) from Inserted) = 1 )
BEGIN
INSERT INTO dbo.VHVS_Clinics (hostID,clinicID,clinicName)
SELECT hostID,clinicID,clinicName
FROM Inserted I
WHERE EXISTS (select 1 from VHVS_sites s where s.hostID=i.hostID)
AND NOT EXISTS (select 1 from VHVS_clinics c where c.hostID=i.hostID and c.clinicID=i.clinicID)
END
Any help is appreciated.
John