Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-23
8
Medium Priority
?
192 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 61

Expert Comment

by:HainKurt
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 500 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 61

Expert Comment

by:HainKurt
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 61

Expert Comment

by:HainKurt
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 61

Expert Comment

by:HainKurt
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 61

Expert Comment

by:HainKurt
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 61

Assisted Solution

by:HainKurt
HainKurt earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

963 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