August 31, 2022 at 2:10 pm
I am working with a system that has audit trail triggers on most of it's key tables. On an INSERT/UPDATE/DELETE the audit table records get written with the domain table name and the before and after image using the following generic code
if exists (select 1 from inserted)
select @myai = CAST((SELECT TOP 1 * FROM inserted FOR XML RAW, BINARY BASE64) AS varchar(8000))
HOWEVER....
The vendor product has two screens available in different parts of the application to manage the same entity. One of the screens does not honour a value that has been set against the entity and overwrites it with empty string ('')
I can modify the trigger so that if the deleted image has a value set and the inserted image does NOT have the value set, update the record to include the previous value, however this override is not reflected in the audit trail because I have not generated the change into the inserted record.
any ideas on how to either get this override value into the inserted table or to modify the code above so that I can take all of the fields from the inserted except for the field I am overriding, without having to specify the field list in the SELECT statement. I don't want to do this because if the DDL for the underlying table changes the new fields would not be captured by default or the field order could be wrong.
Currently I am doing the following:
select TOP 1 * into #tt_inserted from inserted
UPDATE #tt_inserted SET myfield = @PrevMyField
SELECT @myai = CAST((SELECT TOP 1 * FROM #tt_inserted_co_person FOR XML RAW, BINARY BASE64) AS varchar(8000))
DROP TABLE #tt_inserted
but this feels a bit dirty!
can anyone suggest a better way?
I am aware that this trigger only works for the first record in the transaction scope. I have pointed this out to the powers that be...
September 1, 2022 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 12, 2022 at 7:44 am
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply