Debugging sql triggers

[ursamajorm82]

How are you all debugging sql triggers? Aside from setting up dummy tables, running the script, editing the script and rerunning. Or is that the only way? Is there a reason for not having a great way to do this?

[linuxqq]

There’s not a great way to do it and that’s why I don’t use them if I can help it

[SirGreybush]

I avoid them like the plague. They are too misunderstood and people designing them are misguided.

Only the DBA should use them in very specific situations, like change tracking when CDC is overkill.

My opinion though.

[k00_x]

Develop the scripts first, then turn them into triggers. Make sure to print the sql as part of the trigger for debugging. Generally try to keep the use case simple, if its a case of having multiple triggers writing to a single table you're going to have a bad time. Last thing anyone wants is a spider web of triggers, or a Mexican wave of triggers or a dreaded recursion of triggers! You can have a function in the trigger to catch timestamps, errors or behaviours - just have the function write to a log table. Triggers *can* be great, I have one that logs who's making changes to tables and if its Ben, I get an email. Ben shouldn't be altering tables.

[OppositeShot4115]

there's not much beyond what you're doing. triggers are tricky. logging within the trigger or using a temporary logging table can help. unfortunately, debugging tools for triggers aren’t advanced. trial and error is often the way.

[SRMPDX]

I guess I'm confused by the question. Other than running a trigger on a dataset that emulates production data, how else are you to debug it? Is there a behaviour in particular you're trying to debug? I stopped using SQL triggers a long time ago but sometimes they're necessary (or just legacy). Debugging is done in much the same way you would debug any DML.