Sql trigger update second table

Question

0.00/5 (No votes)

See more:

I am trying to create trigger to update a second table but its not working.

What I have tried:

CREATE trigger [dbo].[Report_Update] on [dbo].[SellTable] 
AFTER UPDATE AS
BEGIN
  
    IF UPDATE(TransType) BEGIN
    UPDATE ReportTable
    SET    ReportTable.TransType = inserted.TransType
    FROM   SellTable
     INNER JOIN Inserted ON SellTable.TransID = Inserted.TransID
	
	
    End

	IF UPDATE(Quantity) BEGIN
    UPDATE ReportTable
    SET    ReportTable.TransType = inserted.Quantity
    FROM   SellTable
    INNER JOIN Inserted ON SellTable.TransID = Inserted.TransID
	
    End

	IF UPDATE(Price) BEGIN
    UPDATE ReportTable
    SET    ReportTable.TransType = inserted.Price
    FROM   SellTable
    INNER JOIN Inserted ON SellTable.TransID = Inserted.TransID
    End

	IF UPDATE(Cost) BEGIN
    UPDATE ReportTable
    SET    ReportTable.TransType = inserted.Cost
    FROM   SellTable
 INNER JOIN Inserted ON SellTable.TransID = Inserted.TransID
	End

     
END

Posted 29-Jan-20 20:49pm

qulaitks

Updated 30-Jan-20 4:17am

Add a Solution

Comments

Santosh kumar Pithani 30-Jan-20 7:01am

Hello,your updating all records for "ReportTable" based on updated "SellTable" columns, i hope there is no addition queries its should be complete in single line

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2020-01-30T00:16:00

Solution 1

You seem to update the same field of ReportTable for distinct fields of SellTable.
Shouldn't you write

SQL

SET    ReportTable.Quantity = inserted.Quantity

,

SQL

SET    ReportTable.Price = inserted.Price

and

SQL

SET    ReportTable.Cost = inserted.Cost

instead?

Posted 30-Jan-20 0:16am

phil.o

Updated 30-Jan-20 0:18am

v2

Richard Deeming · Answer 2 · 2020-01-30T04:18:00

Quote:

SQL

UPDATE ReportTable
SET    ReportTable.TransType = inserted.TransType
FROM   SellTable
INNER JOIN Inserted ON SellTable.TransID = Inserted.TransID

You're updating every record in the ReportTable table with one random value from the updated records.

There is presumably a connection between the records in the two tables? You will need to limit the records you're updating based on that join.

SQL

UPDATE
    ReportTable
SET
    TransType = inserted.TransType
FROM
    ReportTable
    INNER JOIN inserted
    ON inserted.??? = ReportTable.???
;