Create Trigger to change record only when modifying a field

Question

I have a SQL Server registration table, I have a column that shows the status of this registration ('A','I','S','C').

I created a field with name Update_data, that you will receive a datatime every time the customer changes the status of the registration. This is also true for an Insert.

I’m having doubts because I never created a Trigger in sql server.

Ex: from my table

ID | Processo | status | Update_data
1  | A33      | A      | null
2  | A34      | I      | null
3  | A55      | A      | null

I could not advance in the following example below Trigger:

CREATE TRIGGER atualizaData
ON cadastro
 AFTER INSERT,UPDATE
AS
IF INSERT OR UPDATE(status)
BEGIN
UPDATE cadastro
      SET Update_data = GETDATE()
WHERE id = id_que_foi_modifica/inserida
END

At the end, it will be updated with the current date only if there is modification or insertion in the situation field.

Answer 1

Here’s a possibility:

-- código #1

CREATE TRIGGER atualizadata
     on CADASTRO
     after INSERT, UPDATE as
begin

-- verifica se há algo a processar
IF not exists (SELECT * from INSERTED)
  return;

-- verifica se a coluna status consta na lista de inserção/atualização
IF not UPDATE (status)
  return;

-- detecção do evento
declare @isINSERT bit, @isUPDATE bit;
set @isUPDATE= exists (SELECT * from DELETED);
set @isINSERT= not @isUPDATE;

--
UPDATE C
  set update_data= cast (current_timestamp as date)
  from cadastro as C
       inner join INSERTED as I on I.chave = C.chave
       left join DELETED as D on D.chave = C.chave
  where @isINSERT = 'true'
        or (@isUPDATE = 'true' and (D.status <> I.status));
end;
go

I have not tested; may contain error(s).

I suggest reading the article "Traps in trigger procedure programming (Trigger)", where are related some of the trappings in the programming of triggers.

Answer 2

The first step is to put a DEFAULT CONSTRAINT in your column Update_data to not have to worry about the INSERT (the date is updated when the registration is added):

ALTER TABLE cadastro ADD CONSTRAINT cadastro_updatedata DEFAULT(GETDATE()) FOR Update_data

Then just create the TRIGGER only for the UPDATE:

CREATE TRIGGER atualizaData ON cadastro
AFTER UPDATE
AS
    IF UPDATE(status)
    BEGIN
        UPDATE      CD
        SET         Update_data = GETDATE()
        FROM        deleted     D
        INNER JOIN  cadastro    CD ON CD.id = D.id
    END

The table deleted contains the records that have just been updated (there is also the table inserted, which contains the most current values).

If you want more information about these tables in TRIGGER of SQL, access here: Use the inserted and Deleted Tables