Triggers and declare cursor question
Hi all
I'm trying to get my head around triggers in SQL2k. What I need to do is hold an audit table of database changes so I wrote this: -
CREATE TRIGGER [tr_allocs_u] ON [dbo].[allocations]
FOR update
AS
set nocount on
declare @duser nvarchar(255)
declare @iuser nvarchar(255)
declare @istart nvarchar(255)
declare @iend nvarchar(255)
declare @idriver_name nvarchar(255)
declare @ivehicle_id nvarchar(255)
declare @icostcentre nvarchar(255)
declare @idepot_id nvarchar(255)
declare @dstart nvarchar(255)
declare @dend nvarchar(255)
declare @ddriver_name nvarchar(255)
declare @dvehicle_id nvarchar(255)
declare @dcostcentre nvarchar(255)
declare @ddepot_id nvarchar(255)
declare @iarchive nvarchar(255)
declare @darchive nvarchar(255)
declare @rn int
declare @user nVarChar(10)
declare @datetime datetime
select @datetime=getdate()
-- Declare the audit cursor
declare audit_cursor cursor local for
select
i.record_number,i.start_date as istart,i.end_date as iend,i.driver_name as idriver_name,i.vehicle_id as ivehicle_id,i.changed_by as iuser,i.costcentre as icostcentre,i.depot_id as idepot_id,i.archive_status as iarchive,
d.start_date as dstart,d.end_date as dend,d.driver_name as ddriver_name,d.vehicle_id as dvehicle_id,d.changed_by as duser,d.costcentre as dcostcentre,d.depot_id as ddepot_id,d.archive_status as darchive
from inserted i inner join deleted d on i.record_number=d.record_number
open audit_cursor
fetch next from audit_cursor into @rn,@istart,@iend,@idriver_name,@ivehicle_id,@iuse r,@icostcentre,@idepot_id,@iarchive,@dstart,@dend, @ddriver_name,@dvehicle_id,@duser,@dcostcentre,@dd epot_id,@darchive
while @@fetch_status=0
begin
select @user=@iuser
-- Need a statement block for each entry to check
if @istart<>@dstart
begin
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Allocation Start Date',@rn,@dstart,@istart,@user,@datetime)
end
if @iend<>@dend
begin
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Allocation End Date',@rn,@dEnd,@iEnd,@user,@datetime)
end
if @idriver_name<>@ddriver_name
begin
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Allocation driver_name',@rn,@dDriver_name,@idriver_name,@user ,@datetime)
end
if @ivehicle_id<>@dvehicle_id
begin
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Allocation Vehicle ID',@rn,@dVehicle_id,@ivehicle_id,@user,@datetime)
end
if @icostcentre<>@dcostcentre
begin
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Allocation Costcentre',@rn,@dcostcentre,@icostcentre,@user,@d atetime)
end
if @iarchive<>@darchive
begin
insert into audit (audit_comment,host_record,audit_old_value,audit_n ew_value,audit_user,audit_date) values ('Allocation Archive Status',@rn,@darchive,@iarchive,@user,@datetime)
end
-- loop
fetch next from audit_cursor into @rn,@istart,@iend,@idriver_name,@ivehicle_id,@iuse r,@icostcentre,@idepot_id,@iarchive,@dstart,@dend, @ddriver_name,@dvehicle_id,@duser,@dcostcentre,@dd epot_id,@darchive
end
-- tidy
close audit_cursor
deallocate audit_cursor
Am I on the right track with this, I don't know whether i'm over complicating things. Also are there any concurrency style issues with generating a cursor to combine the inserted and deleted tables?
Appreciate any pointers.
Regards
Graham
|
