SQL Server 2005 and line version control

Asked

Viewed 345 times

6

In my current scenario, every Friday, I need to list all rows of a table that have been added or changed.

In this table we have a field DataDeInclusao which obviously receives the date of inclusion of that record.

My problem is to list the changed records from a certain date. Add a field of type timestamp or rowversion do not store the information "when".

That’s why I thought I’d use a Trigger in the table that I want to control and this way update a field (Datadaultimaalteracao).

Is this the best way? What would you do?

sql-server versioning tsql sql-server-2005

2 answers

5


Yes, this is the path I would take even for auditing issues (of break I would also use a field to identify the user who made the change, but that’s not the case). For SQL Server 2005 you can use a value default GETDATE() or (CURRENT_TIMESTAMP) for the date of insertion and a Trigger for the update date. For SQL Server 2008+ you can use the function SYSDATETIME() and the most precise type DATETIME2.

CREATE TABLE dbo.MinhaTabela
(  
   ID INT IDENTITY(1,1) PRIMARY KEY,
   -- suas colunas
   DataDeInclusao  DATETIME NOT NULL
       CONSTRAINT DF_MinhaTabela_Inclusao DEFAULT (GETDATE()),
   DataDeAtualizacao DATETIME NULL -- Ou com DEFAULT de acordo com seus requisitos
);

CREATE TRIGGER dbo.TRG_MinhaTabelaAtualizada
ON dbo.MinhaTabela
AFTER UPDATE 
AS
    UPDATE dbo.MinhaTabela
    SET DataDeAtualizacao = GETDATE()
    FROM Inserted i
    WHERE i.ID = dbo.MinhaTabela.ID;

See that to meet your requirement only one column DataUltimaAtualizacao (with the default Constraint and the Trigger) would be sufficient, but it is always good to differentiate update insertion.

Finally, don’t forget to index the two dates (to optimize your queries).

CREATE INDEX IDX_MinhaTabelaInclusao ON  dbo.MinhaTabela(DataDeInclusao);
CREATE INDEX IDX_MinhaTabelaAtualizacao ON  dbo.MinhaTabela(DataDeAtualizacao);

Example in SQL Fiddle

Sources:

3

That’s right. The correct is a column called DataDeAlteracao of the kind DateTime, upgradable by Trigger.

In this answer, teaching a command to create a Trigger for each table in your database. It may be useful if you intend to audit all tables.

timestamp and rowversion are not suitable for control of the last change date because they do not keep a date itself, but a binary number that is changed every time the line is changed. The purpose of this type of data is simply to solve conflicts of competition, for large and widely accessed systems at reading and writing levels.

Browser other questions tagged sql-server versioning tsql sql-server-2005

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