Solved

Write SQL Trigger script that updates a column value from the sum of another tables columns

Posted on 2011-09-23
8
189 Views
Last Modified: 2012-05-12
I am a novice trying to get my head around triggers and SQL Server. I have looked at many examples but cant get any triggers to work. I have attached some SQL code that looks like it should create the trigger for what I am trying to achieve, but I get a syntax error. Can some one tell me what is wrong with it.
 
CREATE TRIGGER updateREQprice
ON RI
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE p
SET p.REQ_PRICE=pc.REQ_PRICE
FROM dbo.REQUISITION p
INNER JOIN (SELECT REQ_ID,SUM(RI_PRICE) AS REQ_PRICE
FROM REQUISITION pc
WHERE EXISTS (SELECT 1 FROM INSERTED WHERE REQ_ID =pc.REQ_ID)
GROUP BY REQ_ID) pc
ON pc.REQ_ID=p.REQ_ID
END

Open in new window


In the SQL script I get a message that RI_PRICE is an "invalid column name". I know it exists and is spelt correctly
SUM(RI_PRICE) AS REQ_PRICE
 
details:
TABLE1 named - REQUISITION
COLUMNS: REQ_ID (pk) , REQ_DATE, REQ_PRICE

TABLE2  named - RI    (Requisition Items)
COLUMNS: REQ_ID (pk) , RI_LINE (pk) , RI_DESCRIPTION, RI_PRICE

All i am trying to do is update the REQUISITION REQ_PRICE field when the RI_PRICE field is updated.
0
Comment
Question by:dearness
[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
  • 6
8 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36590673
i dont see any issue

here is another way of writing that logic & formatted
CREATE TRIGGER updateREQprice
ON RI
AFTER INSERT,UPDATE
AS
BEGIN

UPDATE p
   SET p.REQ_PRICE=pc.REQ_PRICE
  FROM dbo.REQUISITION p 
       INNER JOIN (
         SELECT REQ_ID, SUM(RI_PRICE) AS REQ_PRICE
           FROM REQUISITION
          WHERE REQ_ID in (SELECT REQ_ID from inserted)
          GROUP BY REQ_ID
       ) pc ON pc.REQ_ID=p.REQ_ID;

END;

Open in new window

0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 125 total points
ID: 36590679
Couple of things:

1) I think "SET p.REQ_PRICE=pc.REQ_PRICE" should be "SET REQ_PRICE=pc.REQ_PRICE" i.e. I don't think you can prefix the column you are updating.

2) Should "FROM REQUISITION pc" be "FROM RI pc"? You're updating the price on the requisition, so don't you want to sum the requisition items?

In fact I wonder if you want to do what is in the attached code i.e. update all requisitions that are referenced in the update (your app might restrict it to a single requisition, but the database doesn't and should therefore handle all possible cases).
CREATE TRIGGER updateREQprice
ON RI
AFTER INSERT,UPDATE
AS
BEGIN
  UPDATE p SET
    REQ_PRICE = pc.REQ_PRICE
  FROM dbo.REQUISITION p
  INNER JOIN
  (
    SELECT REQ_ID, SUM(RI_PRICE) AS REQ_PRICE
    FROM RI pc
    GROUP BY REQ_ID
  ) pc ON pc.REQ_ID=p.REQ_ID
  WHERE p.REQ_ID in (SELECT REQ_ID FROM INSERTED)
END

Open in new window

0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36590680
or simplified version
CREATE TRIGGER updateREQprice
ON RI
AFTER INSERT,UPDATE
AS
BEGIN

UPDATE p
   SET p.REQ_PRICE=(select sum(REQ_PRICE) from REQUISITION r where r.REQ_ID=p.REQ_ID)
  FROM dbo.REQUISITION p inner join inserted i on p.REQ_ID=i.REQ_ID;

END;

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36590686
when I looked at tables, it seems not logical :)
can you please post a sample data, and an update on this table, target table before and after the trigger...
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36590695
what about this

(will try to write more simplified version)
UPDATE p
   SET p.REQ_PRICE=(select sum(REQ_PRICE) from inserted i where i.REQ_ID=p.REQ_ID)
  FROM dbo.REQUISITION p inner join inserted i on p.REQ_ID=i.REQ_ID;

Open in new window

0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36590702
ok, I gues this is what you want

UPDATE p
   SET p.REQ_PRICE=(select sum(REQ_PRICE) from RI r where r.REQ_ID=p.REQ_ID)
  FROM dbo.REQUISITION p inner join inserted i on p.REQ_ID=i.REQ_ID;
0
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 125 total points
ID: 36590713
or this


UPDATE p
   SET p.REQ_PRICE=(select sum(REQ_PRICE) from RI r where r.REQ_ID=p.REQ_ID)
  FROM dbo.REQUISITION p 
 WHERE p.REQ_ID in (select REQ_ID from inserted);

Open in new window

0
 

Author Closing Comment

by:dearness
ID: 36590729
Thanks alot for the very fast respone, you both answered exactly the same time. HainKurt your first solution had the same problem and Dale your response helped me understand the syntax.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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