Improve company productivity with a Business Account.Sign Up

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
?
196 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

606 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