Column for saving SQL Server change date and time

Asked

Viewed 1,377 times

2

I’m migrating a database of Firebird for Sql Server and today, in the Firebird I have some triggers on Before Insert/Update who record the TimeStamp the insertion/change and the logged in user.

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

sql-server firebird

  • You can demonstrate how you do it in Firebird?

  • In Firebird it is quite simple that, in a Trigger of type before Insert/Update, only put one: new.DATAHORA = current_timestamp; new.USUARIO = current_user;

1 answer

1


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

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

For update will have to be by 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

  • Thanks for the help :)

Browser other questions tagged sql-server firebird

You are not signed in. Login or sign up in order to post.