After Insert trigger to modify particular value

0
votes

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
2
sqlsql-servertsqltriggers
Tag your question with the database you are using. – Gordon Linoff
Your description contradicts itself. You said you want to PREVENT the insertion of cancelled transactions. You then go on to identify logic that will transform cancelled transactions (in some fashion that isn't quite clear). But this (latter) path is a dangerous one. Triggers should not be in the habit of changing seemingly important values in rows that an application / user has chosen. Hidden transformation logic can lead to other issues and a lack of traceability. – SMor

2 Answers

0
votes

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());
0
votes

You can do your update using a single update statement using the Inserted pseudo-table to filter which records you are updating.

Using a case expression to update to 'Done' or 'Scheduled' as per your logic.

CREATE OR ALTER TRIGGER t2222
ON Transacoes 
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE TRANSACOES 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' ELSE ESTADO END
    WHERE id IN (SELECT id FROM Inserted);
END;
GO