January 22, 2002 at 4:03 pm
I have 36 columns in a table. My objective is to create a trig which traps any column change and populate a tran table. Here is my question. When I compile and run this no errors. When I update a column in my table I am expecting to see one row inserted for the change column with values I am passing. That does happen but it is the 38th row. The rest 37 rows are empty with only one column filled which is the table name.
Q1. What is the deal with 38 rows?
Q2. Why the expected result is in the 38th row?
Here is my code for this. Please point me to anything obvious or please advise where I am goofing it up. Much thanks
declare @S varchar(1000)
declare @table varchar(128),@col varchar(128),@old_value varchar(100),@new_value varchar(100),@last_upd_date datetime
select @table = 'req'
set nocount on
print 'drop trigger tr_' + @table
print 'go'
print 'create trigger tr_' + @table + ' on ' + @table + ' for update'
print 'as'
select @S = 'declare @table varchar(128),@col varchar(128),@old_value varchar(100),@new_value varchar(100),@last_upd_date datetime,@doc_type varchar(20),@col_name varchar(100)'
print @S
select @S = 'select @table = ''req'' '
print @S
print 'set nocount on'
declare @i int, @j-2 int
select @j-2 = -1
select @S = 'select @last_upd_date = getdate()'
print @S
select @S = 'select @doc_type = ''REQ'' '
print @S
while @j-2 < (select max(colid)-1 from syscolumns where id = (select id from sysobjects where name = @table))
begin
select @col = name from syscolumns where colid = @j-2 + 1 and id = (select id from sysobjects where name = @table)
select @S = 'if update(' + @col + ')'
print @S
select @S = 'begin'
print @S
select @S = 'select @new_value = ' + @col + ' from inserted'
print @S
select @S = 'select @old_value = ' + @col + ' from deleted'
print @S
select @S = 'select @col_name = '''+@col+''''
print @S
select @S = 'exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date'
print @S
select @S = 'end'
print @S
end
print 'go'
print ''
January 22, 2002 at 4:06 pm
This is a really long way to go about this. Can you just post the resulting trigger code?
This looks like the right track. You check for updated() and do the insert into your audit table.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 22, 2002 at 4:08 pm
Here it is.. thanks
drop trigger tr_req
go
create trigger tr_req on req for update
as
declare @table varchar(128),@col varchar(128),@old_value varchar(100),@new_value varchar(100),@last_upd_date datetime,@doc_type varchar(20),@col_name varchar(100)
select @table = 'req'
set nocount on
select @last_upd_date = getdate()
select @doc_type = 'REQ'
if update(req_nbr)
begin
select @new_value = req_nbr from inserted
select @old_value = req_nbr from deleted
select @col_name = 'req_nbr'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(dept_nbr_prefix)
begin
select @new_value = dept_nbr_prefix from inserted
select @old_value = dept_nbr_prefix from deleted
select @col_name = 'dept_nbr_prefix'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(dept_nbr_suffix)
begin
select @new_value = dept_nbr_suffix from inserted
select @old_value = dept_nbr_suffix from deleted
select @col_name = 'dept_nbr_suffix'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(req_fiscal_yr)
begin
select @new_value = req_fiscal_yr from inserted
select @old_value = req_fiscal_yr from deleted
select @col_name = 'req_fiscal_yr'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(loc_id)
begin
select @new_value = loc_id from inserted
select @old_value = loc_id from deleted
select @col_name = 'loc_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(how_solicited)
begin
select @new_value = how_solicited from inserted
select @old_value = how_solicited from deleted
select @col_name = 'how_solicited'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(type_code)
begin
select @new_value = type_code from inserted
select @old_value = type_code from deleted
select @col_name = 'type_code'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(est_cost)
begin
select @new_value = est_cost from inserted
select @old_value = est_cost from deleted
select @col_name = 'est_cost'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(type_of_access)
begin
select @new_value = type_of_access from inserted
select @old_value = type_of_access from deleted
select @col_name = 'type_of_access'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(control_code)
begin
select @new_value = control_code from inserted
select @old_value = control_code from deleted
select @col_name = 'control_code'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(req_alternate_id)
begin
select @new_value = req_alternate_id from inserted
select @old_value = req_alternate_id from deleted
select @col_name = 'req_alternate_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(approval_path)
begin
select @new_value = approval_path from inserted
select @old_value = approval_path from deleted
select @col_name = 'approval_path'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(required_date)
begin
select @new_value = required_date from inserted
select @old_value = required_date from deleted
select @col_name = 'required_date'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(dest_addr_id)
begin
select @new_value = dest_addr_id from inserted
select @old_value = dest_addr_id from deleted
select @col_name = 'dest_addr_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(invc_addr_id)
begin
select @new_value = invc_addr_id from inserted
select @old_value = invc_addr_id from deleted
select @col_name = 'invc_addr_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(doc_ref_nbr)
begin
select @new_value = doc_ref_nbr from inserted
select @old_value = doc_ref_nbr from deleted
select @col_name = 'doc_ref_nbr'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(print_format)
begin
select @new_value = print_format from inserted
select @old_value = print_format from deleted
select @col_name = 'print_format'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(info_contact)
begin
select @new_value = info_contact from inserted
select @old_value = info_contact from deleted
select @col_name = 'info_contact'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(originator_id)
begin
select @new_value = originator_id from inserted
select @old_value = originator_id from deleted
select @col_name = 'originator_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(requestor_id)
begin
select @new_value = requestor_id from inserted
select @old_value = requestor_id from deleted
select @col_name = 'requestor_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(short_desc)
begin
select @new_value = short_desc from inserted
select @old_value = short_desc from deleted
select @col_name = 'short_desc'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(current_hdr_status)
begin
select @new_value = current_hdr_status from inserted
select @old_value = current_hdr_status from deleted
select @col_name = 'current_hdr_status'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(minor_status)
begin
select @new_value = minor_status from inserted
select @old_value = minor_status from deleted
select @col_name = 'minor_status'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(purge_date)
begin
select @new_value = purge_date from inserted
select @old_value = purge_date from deleted
select @col_name = 'purge_date'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(user_last_updated)
begin
select @new_value = user_last_updated from inserted
select @old_value = user_last_updated from deleted
select @col_name = 'user_last_updated'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(date_last_updated)
begin
select @new_value = date_last_updated from inserted
select @old_value = date_last_updated from deleted
select @col_name = 'date_last_updated'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(misc_1)
begin
select @new_value = misc_1 from inserted
select @old_value = misc_1 from deleted
select @col_name = 'misc_1'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(misc_2)
begin
select @new_value = misc_2 from inserted
select @old_value = misc_2 from deleted
select @col_name = 'misc_2'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(misc_3)
begin
select @new_value = misc_3 from inserted
select @old_value = misc_3 from deleted
select @col_name = 'misc_3'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(misc_4)
begin
select @new_value = misc_4 from inserted
select @old_value = misc_4 from deleted
select @col_name = 'misc_4'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(misc_5)
begin
select @new_value = misc_5 from inserted
select @old_value = misc_5 from deleted
select @col_name = 'misc_5'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(purchaser_user_id)
begin
select @new_value = purchaser_user_id from inserted
select @old_value = purchaser_user_id from deleted
select @col_name = 'purchaser_user_id'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(pre_bid_process)
begin
select @new_value = pre_bid_process from inserted
select @old_value = pre_bid_process from deleted
select @col_name = 'pre_bid_process'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(edi_tran)
begin
select @new_value = edi_tran from inserted
select @old_value = edi_tran from deleted
select @col_name = 'edi_tran'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(edi_status)
begin
select @new_value = edi_status from inserted
select @old_value = edi_status from deleted
select @col_name = 'edi_status'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
if update(entered_date)
begin
select @new_value = entered_date from inserted
select @old_value = entered_date from deleted
select @col_name = 'entered_date'
exec insert_doc_trans @table,@col_name,@old_value,@new_value,@doc_type,@last_upd_date
end
go
January 22, 2002 at 4:53 pm
This looks good. The only thing is it will not work for multi row updates. Something to keep in mind.
I'll dbl check the syntax and mock something up as I get a chance.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 23, 2002 at 9:38 am
Guys,
Thanks for your time. The problem was I had another trigger on the same table which was redundant. I deleted the old trigger and everything is hip.
Thanks Steve
January 23, 2002 at 11:04 am
You are welcome. Thanks for the followup.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply