How to refactor a trigger that uses the inserted and deleted tables, to move common code to a stored procedure

Question

I have a trigger that will be dynamically created, as it will be attached to a view that is also dynamically generated. I don't want my stored procedure to have the entire trigger within it, so I would like to move most of it to a stored procedure, but I won't know the fields in the inserted and deleted tables.

The trigger is about 90 lines long, and the only part I really need to be different between triggers is:

DECLARE @DEBUG bit = 1
DECLARE @EntityName nvarchar(128) = 'Lot'
 SELECT * INTO #MYINSERTED FROM INSERTED
 SELECT * INTO #MYDELETED FROM DELETED

If I could move the rest of it to a stored procedure then that would be great.

The problem with just passing in the @DEBUG and @EntityName and using #MYINSERTED and #MYDELETED in the stored procedure then I would have a problem if two people are inserting or updating the same view at the same time.

The best bet would be to pass a table variable to remove any concurrency issues but I am not certain the best way to do that.

Thank you.

Answer 1

This would actually be a bad idea. SQL is not like your run-of-the-mill procedural language. The SQL 'compilation' binds to a physical access path plan, meaning that the statements are compiled into plans that say 'open rowset with ID 1234, seek a record and retrieve its content' and that '1234' is determined during the compilation of a batch by the optimizer. Which means that moving common code into a procedure as you plan more often hurts more than it benefits. The procedure cannot be bound to a 'generic' access path, it needs to know the actual tables and objects it should look into for selects and updates and the like. You either end up doing dynamic SQL in the procedure or moving only non data bound, generic, parts of the procedure (eg. calculations) which creates very convoluted code and still can hurt performance while at the same time decreasing the procedure readability.

Much more advisable is to have a template and generate your triggers from these template via various code generation techniques, like XML and XSLT.

Answer 2

I suspect the metadata/schema about inserted and deleted are the core problem here (which is why you are using SELECT * INTO).

If you are code generating the trigger and view dynamically, I would say it probably doesn't make much difference. After all, all the triggers and views are code generated and can be regenerated as your system gets new capabilities or the core SP is improved.

Only if the triggers and views are customized and are never regenerated, then there would be a benefit of sharing a core SP which can be modified and upgraded instead of regenerating the views and triggers.

The overhead of regenerating is probably outweighed by generated code which will have a solid execution plan and better binding.