SQL Server trigger to update another table's column

Question

I have two SQL Server tables called Table A and Table B. I have an application which inserts one row into Table A and three rows into Table B at the same time. As you can see in the screenshot below, we can link these inserted records based on their ID column in Table A and TransID column in Table B.

During the data insert on table B, if any rows out of 3 inserted rows contain a value called Printed in the Printed column, I want to update my Table A's relevant record's PrintStatus column to Printed as well.

How do I write a SQL Server trigger for this?

Answer 1

Well the best solution is to do this in your code(app) but if there is no way, you can write a Trigger After Insert for Table B like the trigger example below:

Click to copy

CREATE TRIGGER [dbo].[YourTrigger] ON [dbo].[TableB]
    AFTER INSERT
AS
DECLARE @id INT
BEGIN
SET NOCOUNT ON;
 SET @id = (SELECT DISTINCT ID FROM Inserted)
 IF EXISTS (SELECT * FROM Inserted WHERE Printed='Printed')   
  UPDATE TableA
  SET PrintStatus='Printed'
  WHERE ID = @id

END

May this help you

Answer 2

It could be correct for your problem : (not sure at 100%)

Click to copy

CREATE TRIGGER TriggerTableB
ON TableB
AFTER INSERT
AS
    UPDATE TableA AS A
    SET PrintStatus = 'Printed'
    WHERE A.TranID = inserted.ID
        AND 'Printed' = (SELECT MAX(I.Printed)
                         FROM inserted AS I)