Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trigger insert old values- values that was updated

I need to create trigger in SQL Server 2008 that gone insert all values from one row in which some value was changed into Log table!

For example if i have table Employees that have column id, name , password, and i update this table and insert new value for column name, than i need to insert values that was in table Employees after update in table Log.

How I can do this? Thanks!

like image 527
user383875 Avatar asked Jul 05 '10 17:07

user383875


People also ask

Does an on update trigger have access to old and new variables?

An UPDATE trigger can refer to both OLD and NEW transition variables. INSERT.

In which trigger old and new values Cannot be used?

For a DELETE trigger, OLD contains the old values, and NEW contains no values.

Can we use update command in trigger?

AFTER UPDATE Trigger is a kind of trigger in SQL that will be automatically fired once the specified update statement is executed. It can be used for creating audit and log files which keep details of last update operations on a particular table.

How do you execute a trigger only when a specific column is updated?

In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.


1 Answers

In your trigger, you have two pseudo-tables available, Inserted and Deleted, which contain those values.

In the case of an UPDATE, the Deleted table will contain the old values, while the Inserted table contains the new values.

So if you want to log the ID, OldValue, NewValue in your trigger, you'd need to write something like:

CREATE TRIGGER trgEmployeeUpdate ON dbo.Employees AFTER UPDATE AS     INSERT INTO dbo.LogTable(ID, OldValue, NewValue)       SELECT i.ID, d.Name, i.Name       FROM Inserted i       INNER JOIN Deleted d ON i.ID = d.ID 

Basically, you join the Inserted and Deleted pseudo-tables, grab the ID (which is the same, I presume, in both cases), the old value from the Deleted table, the new value from the Inserted table, and you store everything in the LogTable

like image 50
marc_s Avatar answered Oct 05 '22 10:10

marc_s