ALTER TRIGGER [dbo].[tri_memberLoginUpdate]
ON [dbo].[tbl_advisor_registration]
FOR UPDATE
AS
UPDATE T
SET t.USerName = i.advisor_username, t.Password = = i.advisor_password
FROM tbl_login t
INNER JOIN INSERTED i
ON i.advisor_id = t.User_id
AND t.Status = 2
i don't think there is a loop needed hope this solves your problem