Get Old Value in Update Trigger 2

[hi2jenny]

I wonder how to use UPDATE trigger and get the old value of some column before update. I need to insert that value to a table. I created a trigger but it doesn't return any value. I have the print statement but nothing gets printed.

CREATE TRIGGER Test_Update ON Test FOR UPDATE
AS
DECLARE
@rowID int,
@date DATETIME

SELECT @rowID = AutoID, @date = Date
FROM Deleted

print 'rowID = ' + Cast(@rowID as nvarchar(20))

 

[mercwrought]

i belive that the old vaule is stored in the deleted table so...

select column from deleted

 

[mercwrought]

here is what i did and it worked

create table bob (
		idnumber int,
		n	int
)
create table bob2 (
		idnumber int,
		n	int
)

CREATE TRIGGER Test_Update ON bob FOR UPDATE
AS
    DECLARE
    @rowID int,
    @date int

    SELECT @rowID = idnumber, @date = n
    FROM Deleted
insert into bob2 select  @rowid, @date

insert into bob select 1, 5

update bob set n = 6 where idnumber = 1
select * from bob
select * from bob2

 

[clapag22]

Not trying to be picky but that above trigger would not work if you had a multi-row update. You'd need to do something like this:

CREATE TRIGGER Test_Update ON bob FOR UPDATE
AS

insert into bob2 (idnumber, n)
SELECT idnumber, n
FROM Deleted

Craig

 

[mercwrought]

I was just trying to keep it similar to hi2jenny example.