Create Trigger to change record only when modifying a field

Question

Create Trigger to change record only when modifying a field

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

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

I created a field named Update_data , which will receive a datatime every time the client changes the registration status. This also applies to an insert.

I'm in doubt, because I've never created a Trigger on a sql server.

Ex: from my table

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

I was unable to proceed in the following example below:

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

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

sql-server trigger

asked by anonymous 22.12.2018 / 02:57

2 answers

mysql workbench error [closed] Application closing (unfortunately MyApplication has stopped)

score 1 · 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 did not test; may contain error (s).

I suggest reading the article " Traps in Trigger Procedures Programming

" , where some of the clutter in the triggers programming are related.

score 1 · Answer 2

The first step is to put a DEFAULT CONSTRAINT in your Update_data column so you do not have to worry about INSERT (the date is updated at the time the log is added):

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

Then just create TRIGGER only for 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 deleted table contains the records that have just been updated (there is also the inserted table, which contains the most current values).

If you want more information about these tables in SQL , go here: #