?
Solved

SQL Trigger on Insert to Update another table

Posted on 2009-04-03
1
Medium Priority
?
206 Views
Last Modified: 2012-05-06
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)


0
Comment
Question by:MimUK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
MimUK earned 0 total points
ID: 24058615
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question