1. Home
  2. Categories
  3. Coding Help
  4. Mixed DML and DDL in T-SQL (was: Lambdas/functors in T-SQL)

Mixed DML and DDL in T-SQL (was: Lambdas/functors in T-SQL)

  • C

    This might be a little bit of an X-Y question, since I have thought about how I'd do it in a "straight forward" way but have questions about how to do it in T-SQL specifically.

    So I've been working on this project where we have to delete a bunch of data according to GDPR style rules. That includes going into our temporal tables and deleting data out of them, which isn't allowed unless you turn the temporality feature off.

    OK.

    But altering a table's temporality isn't "transactional". If something in the rest of the transaction fails, the alter statement doesn't get rolled back, leaving the temporality feature turned off.

    This seems like a job for a wrapper, maybe called spModifyTemporalTable, that accepts the name of the base table, the temporal table, and a lambda that acts on the tables but can fail. Theoretically, we would run the lambda in a try block and make sure that even if the transaction fails, we clean up the mess and turn temporality back on so the system can run as usual in the morning.

    But how do I cleanly express "a lambda" in T-SQL? I guess I can do exec and pass in a string containing the DML. I find this objectionable but maybe it's my only good option.

    I know exec would work if I pass the name of a stored procedure in, but I don't necessarily want to name and register all the blocks I need to write, especially since the only difference between them in which table I'm deleting from (i.e., maybe some join structure, but it's hardly logic).

    Or maybe there's just a better pattern? My driver stored procedure already just calls one stored procedure after doing some setup logic. Maybe I should just set up a list and a loop in that outer one that goes and checks if the temporal tables are all turned on after the inner sproc fails. This is my least preferred option, since it forces me to maintain a list of temporal tables. (Granted, I'd be "implementing" the same list if I used a more localized wrapper around each temporal table modification, but I think errors in the code would be a bit more obvious).

    What do ya'll think?

    1 Jaime 1 Reply
  • Jaime

    @Captain In my opinion, this is a classic case where you had a requirement and a built-in feature met that requirement (temporal table). Now your requirements have shifted (need to delete history) beyond the functionality of the built-in feature.

    The right answer in these cases is usually to do whatever you would have done originally had a built-in feature not been available. SQL temporality isn't that sophisticated - simply re-implement it with traditional mechanisms. If I had to do this twenty years ago, I would have made a set of triggers. Now that it's your code, you can add your own feature.

    If you are on an old-ish version of SQL, use "SET CONTEXT_INFO" in your "obliterate history" deletes, and have your trigger use "SELECT CONTEXT_INFO()" to see if the magic switch is turned on so it doesn't attempt to write the deletes to history.

    On 2016 or later, your can use the more flexible "sp_set_session_context" to do the same thing.

    2
Log in to reply