After Insert trigger to modify particular value

I have some problems with my trigger.

On my transactions table I can’t allow any rows to be inserted which are cancelled.

Any transaction can be Scheduled, Done or Cancelled.

If you insert a cancelled transaction, my trigger should check the date of transaction and if was before the actual date (GETDATE()) it’s Done, and if its after the actual date is Scheduled.

I performed and it worked, but it change the old records not only the inserted row.

CREATE OR ALTER TRIGGER t2222
ON Transacoes 
AFTER INSERT
AS
BEGIN
    UPDATE TRANSACOES 
    SET ESTADO = 'Done' 
    FROM inserted i 
    WHERE (i.ESTADO = 'Cancelada'  AND i.DATA_DA_TRANSACAO < GETDATE())

    UPDATE TRANSACOES 
    SET ESTADO = 'Scheduled' 
    FROM inserted i 
    WHERE (i.ESTADO = 'Cancelada' AND i.DATA_DA_TRANSACAO >= GETDATE())
END
GO

Answer

Hmmm . . . You need some sort of id to connect the transactions table with inserted. So, I would expect something like:

UPDATE t
    SET ESTADO = (CASE WHEN i.ESTADO = 'Cancelada'  AND i.DATA_DA_TRANSACAO < GETDATE() THEN 'DONE'
                       WHEN i.ESTADO = 'Cancelada' and i.DATA_DA_TRANSACAO >= GETDATE() THEN 'Scheduled'
                  END)
    FROM TRANSACOES t JOIN
         inserted i
         ON i.transaction_id = t.transaction_id
    WHERE (i.ESTADO = 'Cancelada'  AND i.DATA_DA_TRANSACAO < GETDATE()) OR
          (i.ESTADO = 'Cancelada' and i.DATA_DA_TRANSACAO >= GETDATE());