SQL Trigger on Insert to Update another table

I have a table called CASES
And I would like that when a case is inserted with case_incidenttype = 'INAFP'
That it updates a field called COMP_AFPSCONSUMED in a table called COMPANY by adding an incremental number, I have included the join below

CASES
case_primarycompanyid         case_incidenttpye        
101                                           INAFP

COMPANY
comp_companyid                    comp_afpsconsumed
101                                           2 (+1 when a case is inserted with type INAPF)


MimUKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MimUKConnect With a Mentor Author Commented:
update Company
  set comp_afpsconsumed =
    (select count(case_caseid)
     from Cases ca
     where ca.case_primarycompanyid = company.comp_companyid and ca.case_deleted is null and ca.case_incidenttype = 'INAFP')
  where comp_companyid in
    (select case_primarycompanyid from inserted
     union
     select case_primarycompanyid from deleted);
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.