More help with triggers

I have a trigger which updateds a linked server.  This trigger is working when I only need to update on strPatientid.  I would like to modify the trigger to update records in the database where the familyid matches.

Here is what I have so far.

Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
BEGIN
    SET XACT_ABORT ON
    UPDATE    DEN
    SET              CONSENTDATE = inserted.dtmEligExpires
    FROM BCSQL.dentrix.dbo.DDB_PAT_BASE AS DEN
    INNER JOIN inserted ON DEN.CHART = inserted.strPatientid
    INNER JOIN(select FAMILYID, count(*) from DEN having count(*) > 1 where inserted.strPatientid = DEN.CHART) as NameCount)
    on DEN.familyid = NameCount.familyid
    WHERE DEN.chart = inserted.strPatientid
    where Den.familyid = NewCount.familyid
   
END


Thanks for your help.
running32Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Try this one:

Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
BEGIN
    SET XACT_ABORT ON
    UPDATE    DEN
    SET  CONSENTDATE = inserted.dtmEligExpires
    FROM BCSQL.dentrix.dbo.DDB_PAT_BASE AS DEN
    INNER JOIN inserted ON DEN.CHART = inserted.strPatientid
    WHERE DEN.FamilyID = (SELECT FamilyID FROM BCSQL.dentrix.dbo.DDB_PAT_BASE DEN2
                          WHERE DEN2.Chart = inserted.strPatiendID
                          GROUP BY FamilyID HAVING COUNT(*) > 1)
END
0
 
running32Author Commented:
Perfect.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.