Solved

SQL Trigger on Insert to Update another table

Posted on 2009-04-03
1
198 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
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2005 Srink database in chunks 4 58
SQL Query 2 61
Following an example - removing duplicate strings 4 55
CREATE DATABASE ENCRYPTION KEY 1 64
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 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