Problem with update trigger
Hi all
Appreciate any help with this one, I am trying to implement a simple audit trail within a SQL2k database so I can see who is changing what
The main table the user updates is called drivers and the audit table is called simply audit.
My trigger code is this
create trigger trdrivers on [drivers] for update as
declare @old nVarChar(255)
declare @new nVarChar(255)
declare @rn int
declare @user nVarChar(10)
declare @datetime datetime
select @datetime=getdate()
select @rn=(select record_number from inserted)
select @user=(Select changed_by from inserted)
if update(salutation)
begin
select @old=(select salutation from deleted)
select @new=(select salutation from Inserted)
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Salutation',@rn,@old,@new,@user,@datetime)
end
If I issue this update statement:- Update drivers set salutation='GB', occupation='dogsbody' where driver_code='123'
All works fine, I get two records written to the audit trail and one driver record updated.
Now if issue this: - update drivers set occupation='Staff' the query should affect loads of records but it fails with "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
" The error points to the select @rn line in the trigger.
If I understand the BOL correctly it says that the trigger should fire once for each record so Inserted and Deleted should only ever have either 0 or 1 row.
Anybody got any ideas?
Best wishes
Graham
|
