Update Trigger not working

Hi Friends,

The following is the code for my update trigger:


CREATE TRIGGER TransTrig ON TransTest after UPDATE AS
If UPDATE(Status)
BEGIN
declare @status nvarchar(10);
declare @audio nvarchar(20);
select @status=status,@audio=audio from transtest;
update transtest set docfilename=@audio;
if @status='T'
begin
update transtest set transbegin=getdate() where audio=@audio;
end
else
if @status='WQ'
begin
update transtest set transend=getdate() where audio=@audio;
end
else
if @status='Q'
begin
update transtest set QaBegin=getdate() where audio=@audio;
end
else
if @status='U'
begin
update transtest set Qaend=getdate() where audio=@audio;
end
END
GO

'---------
The problem in this is the value in @audio is the value last inserted value but not the value related to the record updated.

How is this possible with one select statement a value from one col of one row and the value of the other col from another row?

Please let me know how to solve and this and if you can please try to answer the question i wrote earlier?

Thanks

M.L.Srinivas

Just a curious question. Why are you using a trigger instead of a stored procedure?