begin transaction
set nocount on

CREATE TABLE joinreason (jrid int, jr_active char(1), jr_par_jrid int)
INSERT joinreason VALUES (1, 'Y', null)
INSERT joinreason VALUES (2, 'Y', 1)
INSERT joinreason VALUES (3, 'Y', null)
go
CREATE TRIGGER [dbo].[trgInsertUpdate]
ON [dbo].[joinreason]
AFTER INSERT, UPDATE
AS
BEGIN
    IF UPDATE(jr_active)
    BEGIN
        UPDATE joinreason
        SET jr_active = 'N'
	   FROM joinreason j
	   JOIN inserted i ON i.jrid = j.jr_par_jrid
	   JOIN deleted d  ON i.jrid = d.jrid
	   WHERE d.jr_active = 'Y' AND i.jr_active = 'N'
    END
END
go
select * from joinreason
update joinreason set jr_active = 'N' where jrid = 1
select * from joinreason

rollback transaction