Column to write date and time of change SQL Server

2

I am migrating a database from Firebird to Sql Server and today, in Firebird I have some triggers in Before Insert/Update that write TimeStamp of insertion / change and user logged in.

Is there any way I can do this in Sql Server without using triggers of type Instead Of ?

    
asked by anonymous 11.03.2016 / 18:13

1 answer

1

Yes. For inserts, in the declaration of your table, use the following:

CREATE TABLE TABELA (
    ...
    DATA_CRIACAO DATETIME DEFAULT CURRENT_TIMESTAMP,
    ...
);

For update it will have to be trigger :

ALTER TRIGGER dbo.UltimaModificacaoTabela
ON dbo.Tabela
AFTER UPDATE
AS
BEGIN
    IF NOT UPDATE(ULTIMA_MODIFICACAO) -- Evitar recursão na coluna
    BEGIN
        UPDATE t
            SET t.ULTIMA_MODIFICACAO = CURRENT_TIMESTAMP
            FROM dbo.Tabela AS t
            INNER JOIN inserted AS i 
            ON t.ID = i.ID;
    END
END
GO
    
11.03.2016 / 20:41