Creating a Trigger which will insert record in a t

问题:

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.

回答1:

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)

回答2:

I'm not going to build the whole thing for you (no fun, right?) but I can point you in the right direction

create trigger logUpdate
on T1
After update
as
begin
  insert into T2...
  --here is just an example
  select * from deleted --the DELETED table contains the OLD values
  select * from inserted  --the INSERTED table contains the NEW values
end

remember that DELETED and INSERTED are internal tables that contains old and new values. On a update trigger, they both exist. On a insert trigger, DELETED will be null because there is nothing being delete. Same logic on a delete trigger, the INSERTED will be empty

EDIT:

answering your question: no matter how many fields you update, your DELETED and INSERTED tables you have all the columns of all the rows affected. Of course, if you update only one column, all the other will have the same value on DELETED and INSERTED

回答3:

create trigger T_UPD_T1
on T1 FOR  update
as
insert into T2 select deleted.value, inserted.value from inserted, deleted