July 1, 2015 at 2:04 pm
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?
July 1, 2015 at 2:46 pm
Try running:
sp_refreshsqlmodule or
sp_refreshview
on potentially affected objects.
You should get errors on the object(s) that now reference unknown columns.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 1, 2015 at 3:05 pm
I ran EXEC sys.sp_refreshsqlmodule @name = 'trg_Audit_Del' but it did not give me any error even though that trigger is using a table that doesn't exist in the database. Any other suggestions?
July 1, 2015 at 3:31 pm
A table that doesn't exist is a different issue, because SQL's deferred name resolution allows that, but not invalid/missing column names.
The other thing to try would be dmv:
sys.sql_expression_dependencies
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 1, 2015 at 3:35 pm
Do you have any suggestions for table that doesn't exist in trigger? how to find them?
July 1, 2015 at 3:49 pm
Again, check this DMV:
sys.sql_expression_dependencies
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply