September 14, 2017 at 9:39 am
We have a table trigger which is standard, which marks all records with the name of the person changing the record, and the datetime of the change (aka 'mod stamp'). I was looking into using Temporal Tables, and this trigger isn't compatible. The update back against the table for the 'mod stamp' columns ends up adding an additional record into the history table.
I thought, no problem, I'll just build something equivalent into the existing trigger, for those cases where we need a history table. But then after feeling proud about the decision, I realized the same outcome would happen... that being, 2 history records for every update.
So then I started looking into whether there was a call stack, so I could suppress one of the inserts into the history table, and I came across some code suggestions which use the ID of the current procedure, and context_info and writing my own stack code.
Is there anything in MSSQL 2016 which is available, for a better call stack solution?
Thanks,
--=Chuck
September 14, 2017 at 3:34 pm
Yes. Set a control value using sp_set_session_context; you can test it inside the trigger using SESSION_CONTEXT.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 14, 2017 at 4:03 pm
It looks like SESSION_CONTEXT is insert-only, if I'm reading the documentation correctly. I was looking for something where I could PUSH/POP from memory, but work with something that was a little more user friendly than the binary variable in CONTEXT_INFO.
---=Chuck
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply