CODEWITHSUNDEEP
sqlsql-servertriggers
Deiwys
Deiwys

Reputation: 253

Log Trigger (BEFORE UPDATE) - SQL Server

I have a table:

TABLE1: 

cod   |           date
 1    |  2008-07-03 00:00:00.000
 2    |  2009-09-03 00:00:00.000
 5    |  2010-12-03 00:00:00.000

And I have a trigger:

CREATE TRIGGER trigger1
ON TABLE1
INSTEAD OF UPDATE
AS
    DECLARE
        @cod   numeric(9)
       ,@date  datetime

    DECLARE c_test cursor for select cod, data from TABLE1

    open c_test 
    fetch next from c_test into @cod, @date

    WHILE @@FETCH_STATUS = 0 
        BEGIN

           IF(@date < GETDATE())
           BEGIN
                INSERT INTO TABLE_AUDIT (cod, date, date_alt) VALUES (@cod, @date, GETDATE());
           END
           fetch next from c_test into @cod, @date
        END

    close c_test
    deallocate c_test

I want to execute an update like this:

update TABLE1 
set date = '2003-07-15 00:00:00.000' 
where cod = 5;

and I want the old value from TABLE1 (before the update, value 2010-12-03 00:00:00.000) inserted into TABLE_AUDIT and after TABLE1 receives the new value 2003-07-15 00:00:00.000 from the above update.

I know SQL Server doesn't have BEFORE trigger, so it's because I'm asking any alternatives.

Thanks!

Upvotes: 2

Views: 1128

Answers (1)

M.Ali
M.Ali

Reputation: 69494

Instead of this performance killer INSTEAD OF TRIGGER with cursor, you can simply have an after trigger something like this ....

CREATE TRIGGER trigger1
ON TABLE1
AFTER UPDATE
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO TABLE_AUDIT (cod, [date], date_alt)
   SELECT cod, [date], GETDATE()
   FROM deleted 
END

Upvotes: 4

Related Questions