Creating a Trigger which will insert record in a table on update of another table

sql, sql-server-2008, triggers

Solution

Well, you start writing a trigger with `CREATE TRIGGER`:

CREATE TRIGGER NameOfTheTriggerPlease
…

The table that should trigger the additional action is `T1` so the trigger should be defined `ON` that table:

CREATE TRIGGER T1OnUpdate  /* that's just an example,
                              you can use a different name */
ON T1
…

The action that the trigger should be invoked on is `UPDATE` and the timing is `AFTER` the update, so…

CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
…

Now's the time to introduce the body of the trigger, i.e. the statements that should actually be executed by the trigger. You introduce the body with the `AS` keyword followed by the statements themselves.

In your case, there would be just one statement, `INSERT`, which is obvious. What's not so obvious is how we are going to access the old and the new values. Now, SQL Server offers you two virtual tables, `INSERTED` and `DELETED`, and you can easily guess that the former contains all the new values and the latter the old ones.

These tables have the same structure as the table the trigger is assigned to, i.e. `T1`. They only contain rows that were affected by the particular `UPDATE` statement that invoked the trigger, which means there may be more than one. And that, in turn, means that you need to have some primary key or a unique column (or a set of columns) in your `T1` table that you can use in the trigger to match deleted and inserted rows. (In fact, you might also need your `T2` table to have a column that would reference the `T1`'s primary key, so you could later establish which row of `T1` had which values stored in `T2`.)

For the purposes of this answer, I'm going to assume that there's a primary key column called `PK` and a foreign key column of the same name in `T2`. And the `INSERT` statement then might look like this:

CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
AS
INSERT INTO T2 (PK, OldValue, NewValue)
SELECT i.PK, i.Value, d.Value
FROM INSERTED i INNER JOIN DELETED d ON i.PK = d.PK

One last (but not least) thing to remember: the entire `CREATE TRIGGER` statement should be the only one in the batch, i.e. there should be no statements preceding the `CREATE TRIGGER` keywords (but you can put comments there) and, likewise, everything after the `AS` keyword is considered part of the trigger's body (but you can put the `GO` delimiter to indicate the end of the statement if you are running the script in SQL Server Management Studio, for instance).

Useful reading:

- CREATE TRIGGER (Transact-SQL)

Problem

Suppose I have tables T1 and T2 ``` Columns of T1 -->Value Columns of T2 -->OldValue NewValue ``` What I require is a trigger which will insert a record in T2 on updation of T1 , I need to know the old value and new value also , I have never used triggers before , so can any help me with this , how do I go about creating this trigger.Is it possible ,thanks.

Original source