Solved

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

Posted on 2011-09-23
8
185 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
  • 6
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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 51

Assisted Solution

by:HainKurt
HainKurt earned 125 total points
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now