Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
193 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

571 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