CODEWITHSUNDEEP
sql-server-2008triggers
chase huber
chase huber

Reputation: 794

How to validate a trigger in SQL Server

My problem is this: sometimes changes to database schema invalidate the SQL statements in a trigger. For instance I may drop a column in a table that a trigger references. When I do that, I do not receive any indication of that problem until the next time the trigger fires, which might be months out in production in certain situations.

What is the best way to validate objects, such as triggers or user defined functions, in a SQL server database?

Upvotes: 3

Views: 584

Answers (2)

David Atkinson
David Atkinson

Reputation: 5899

I'm not totally sure if this case is covered, but you should try the 'Find Invalid Objects' feature in Red Gate's SQL Prompt 5. Let me know if this doesn't work.

Upvotes: 2

marc_s
marc_s

Reputation: 754388

Have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

In your case: search for e.g. columns that you've dropped, and let SQL Search find all stored procs, triggers etc. that referenced that column -> gives you an easy list of those things that you need to fix.

enter image description here

enter image description here

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

Upvotes: 3

Related Questions