We help IT Professionals succeed at work.
Get Started
Private
Troubleshooting Question

Problem with Trigger insert

JohnMac328
JohnMac328 asked
on
59 Views
Last Modified: 2021-01-15
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
Comment
Watch Question
Sr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 63 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE