CODEWITHSUNDEEP
sqlsql-server
Crysis Hhtht
Crysis Hhtht

Reputation: 131

SQL Server trigger to update another table's column

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?

Upvotes: 6

Views: 2230

Answers (3)

El.Hum
El.Hum

Reputation: 1479

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:

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would recommend querying for the information:

select a.*,
       (case when exists (select 1
                          from b
                          where b.id = a.tranid and b.printed = 'Printed'
                         )
             then 'Printed'
        end) as printstatus
from a;

This is simpler than writing a query and you can wrap this in a view.

From a performance perspective, an index on b(id, printed) should make this pretty fast -- and not slow down inserts.

A trigger can be quite complicated, if you want to take inserts, updates, and deletes into account. I prefer to avoid such complication, if possible.

Upvotes: 0

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

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

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)

Upvotes: 2

Related Questions