After Insert/Update on Table A, IF Conditions Met, Update Record On Table B-CodePudding

I'm looking to create a trigger where after data is either inserted or updated on Table A, if certain criteria is met, an update will happen to a pre-existing record on Table B.

Table A

TableAId	PersonId	Year	Quarter	Food	Color	Comment
1	Joe	2022	4	Apple	Red	null
2	Joe	2022	4	Carrot	Orange	Yummy
3	Bill	2022	4	Celery	null	null
4	Bill	2022	4	Apple	Green	Yucky

Table B

TableBId	PersonId	Year	Quarter	Responded
1	Joe	2021	3	Yes
2	Joe	2022	4	Yes
3	Bill	2022	2	Yes
4	Bill	2022	4	Yes

In verbal logic, the way I want this to work is if there is a record inserted or updated in Table A where Comment is null and Color is not null, I want to update the Table B Responded field to null where the corresponding PersonId, Year and Quarter are equal.

In this specific case, if the 4 records were inserted into Table A, TableAId of 1 would meet the criteria of setting off the trigger because it is the only instance where Comment is null and Color is not null. What would then need to happen on Table B is we would find the corresponding record of Joe/2022/4 (PersonId/Year/Quarter) which in this case is TableBId of 2. We would then change the Responded field to null.

In pseudo code:

After Insert Into Table A

IF(Any inserted rows contain Comment = null AND color != null)

THEN Update Table B

SET TableB.Responded = null 

WHERE TableA.PersonId = TableB.PersonId AND TableA.Year=TableB.Year AND TableA.Quarter = TableB.Quarter

CodePudding user response：

These should do the trick:

CREATE TRIGGER UpdateTableBBasedOnInsert
ON dbo.TableA
AFTER INSERT
AS
BEGIN
    UPDATE b
    SET b.Responded=NULL
    FROM TableB b
    JOIN inserted i
        on i.PersonId=b.PersonId AND i.Year=b.Year AND b.Quarter=i.Quarter
    WHERE i.Comment IS NULL AND i.Color IS NOT NULL
END

CREATE TRIGGER UpdateTableBBasedOnUpdate
ON dbo.TableA
FOR UPDATE
AS
BEGIN
    UPDATE b
    SET b.Responded=NULL
    FROM TableB b
    JOIN inserted i
        on i.PersonId=b.PersonId AND i.Year=b.Year AND b.Quarter=i.Quarter
    WHERE i.Comment IS NULL AND i.Color IS NOT NULL
END

EDIT: As Dale pointed out, these can be combined into a single trigger:

CREATE TRIGGER UpdateRows
ON dbo.TableA
FOR INSERT, UPDATE
AS
BEGIN
    UPDATE b
    SET b.Responded=NULL
    FROM TableB b
    JOIN inserted i
        on i.PersonId=b.PersonId AND i.Year=b.Year AND b.Quarter=i.Quarter
    WHERE i.Comment IS NULL AND i.Color IS NOT NULL
END