SQL update table , case when, select distinct

I have 2 tables as below

Table 1: r_report8

work_order_no	report8a	report_item7
EGR220511003493	56	1

Table2 : rjob_header

work_order_no	report_item7
EGR220511003493	1

I already set a triggers on both table

Table 1: r_report8

DECLARE @workorderno nvarchar(15),@report_item7 int

SELECT @workorderno = WORK_ORDER_NO 
FROM inserted

IF (UPDATE(REPORT8A)) 
BEGIN
    UPDATE R_REPORT8 
    SET REPORT_ITEM7 = (CASE WHEN (REPORT8A<>'') THEN '1' ELSE '0' END)
    WHERE @workorderno = WORK_ORDER_NO 
END

Table 2: rjob_header

DECLARE @workorderno nvarchar(15), @report_item7 int

SELECT @workorderno = WORK_ORDER_NO 
FROM inserted

UPDATE RJOB_HEADER 
SET REPORT_ITEM7 = (CASE 
                       WHEN (SELECT DISTINCT b.report_item7 
                             FROM rjob_header a 
                             INNER JOIN r_report8 b ON b.work_order_no = a.work_order_no 
                             WHERE a.work_order_no = @workorderno) IS NOT NULL 
                           THEN '1' 
                           ELSE '0' 
                    END)
    WHERE @workorderno = WORK_ORDER_NO 
END

2 tables will update by our system form in same time.

When (Table1) dbo.r_report8.report8a updated. The column report_item7 will change be '1' and (Table2) dbo.rjob_header.report_item7 also be '1'

But If I delete the record in (Table1) dbo.r_report8, The column report_item7 still be '1'.

How can I do it when the record deleted from (Table1) dbo.r_report8,(Table2) dbo.rjob_header.report_item7 change back to '0' or null?

Solution

You can write a delete trigger as below:

Delete Trigger:

CREATE TRIGGER tr_r_report8_after_delete
ON dbo.r_report8
AFTER DELETE
AS
BEGIN
    DECLARE @workorderno nvarchar(15)

    SELECT @workorderno = WORK_ORDER_NO 
    FROM deleted

    -- Check if any related records exist in r_report8
    IF NOT EXISTS (SELECT 1 FROM r_report8 WHERE work_order_no = @workorderno)
    BEGIN
        -- Update rjob_header to set report_item7 to 0 
        UPDATE rjob_header
        SET report_item7 = 0 
        WHERE work_order_no = @workorderno
    END
END

Select from tables before deletion:

select * from r_report8;
select * from rjob_header;

work_order_no	report8a	report_item7
EGR220511003493	56	1

work_order_no	report_item7
EGR220511003493	1

Delete from r_report8:

delete r_report8

Select from the tables after deletion:

select * from r_report8;
select * from rjob_header;

work_order_no	report8a	report_item7

work_order_no	report_item7
EGR220511003493	0

*** report_item7 is set to 0 in rjob_header table for work_order_no EGR220511003493

fiddle