Accessing trigger tables (inserted/deleted) vai a stored procedure

Question

Accessing trigger tables (inserted/deleted) vai a stored procedure

shaba

Mr or Mrs. 500

Points: 529
More actions
September 1, 2010 at 4:28 am

#239796

I have created an Update trigger that records data changes to an audit table. This works perfectly as it shows info like... date, username, event, table, beforeValue,afterValue.
Performace is excellent on all tables bar one. I have a table with 328 fields and as the trigger cycles through each field to check for changes this can take up to 10 minutes to complete. All other tables with fields <150 run fine. Is I am not able to change the table structure, I am stuck with 328 fields.
I would be able to get round this hurdle if I could pass the inserted and deleted tables to a stored procedure. This would then free the trigger and let the SP dela with the loop.
Does any one know of a way to pass these table values to a SP even via a tmp table?
Note: I do pass these values to tmp tables instde the trigger to allow referencing in dynamic sql therefore if the SP could also use the tmp tables it might be a solution?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

cycles through each field

Without looking at your trigger, I'm thinking there may be a better or faster way to do whatever you are doing in your trigger;

if your trigger is completely set based, you wouldn't have to iterate thru anything; something as simple as a CTE joining INSERTED and DELETED in your trigger may provide you with the set based speed up you might need to make the trigger model scalable.

even better, there are a lot of volunteers here on SSC that drool at the opportunity to offer high performance code; you'll get some awesome suggestions.

can you show us a CREATE TRIGGER example of your trigger so we can offer some suggestions?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shaba Mr or Mrs. 500 Points: 529 More actions · Answer 2

Thanks lowell, heres the trigger...

Note: addedd an extra ' to the code line

'select getdate(),replace(suser_sname(),'ukdngroup\'',''),'

as was not displaying colors correctly on this site.

Correct code =

'select getdate(),replace(suser_sname(),'ukdngroup\',''),'

ALTER trigger [dbo].[ClaimUpdateDelete] on [WIS].[dbo].[Claim] FOR UPDATE, DELETE as

Set NOCOUNT ON

IF not exists (select * from inserted)

begin

insert into wis.dbo.tblaudit (date,username,ukdnno,jobnumber,[event],

)

select getdate(),replace(suser_sname(),'ukdngroup\'',''),ukdnno,micontjobno,'D','Claim' from deleted

RETURN

end

IF not exists (select * from inserted except select * from deleted) RETURN

Declare @fieldname varchar(128), @i int,@sql varchar(max)

create table #tmpColumnTable (id int,Column_Name varchar(100))

insert into #tmpColumnTable select ordinal_position,column_name from information_schema.columns where table_name='claim'

select * into #ins from inserted

select * into #del from deleted

set @i=1

while @i <= (select count(*) from #tmpColumnTable)

begin

set @fieldname=(select column_name from #tmpColumnTable where id=@i)

set @sql='

if exists(select i.jobno from #ins i inner join #del d on i.ukdnno=d.ukdnno where i.['+@fieldname+'] <> d.['+@fieldname+'])

insert into wis.dbo.tblaudit (date,username,ukdnno,jobnumber,[event],

,[field],[before],[after])

select getdate(),replace(suser_sname(),''ukdngroup\'',''''),d.ukdnno,d.micontjobno,''U'',''Claim'','''+@fieldname+''',d.['+@fieldname+'],i.['+@fieldname+'] from #ins i inner join #del d on i.ukdnno=d.ukdnno'

exec (@sql)

set @i=@i+1

end