Creating trigger failed silently in SQL Server 2005 / 2008 - by NKcVE

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 645692 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 2/21/2011 6:10:08 AM
Access Restriction Public


The creation of a trigger fails silently without any error when you use a non existing aggregate within a cte and the distinct clause in the trigger sql module.

I've tried it on both, SQL 2005 and 2008.
Sign in to post a comment.
Posted by Umachandar [MSFT] on 3/14/2011 at 2:20 PM
Hi Robert,
We have now fixed the issue in the next major version of SQL Server. We may consider porting the fix to SQL Server 2008 based on more customer reports. Thanks again for reporting the issue.

Umachandar, SQL Programmability Team
Posted by Umachandar [MSFT] on 3/1/2011 at 10:56 AM
Hi Robert,
Thanks for your comment. Yes, I tried your script but I kind of missed the trigger failing part. Anyway, I can repro it all versions from SQL Server 2005 onwards. So we will take a look at it and see what is going on. Thanks again for bringing this to our attention.

Posted by NKcVE on 2/28/2011 at 11:57 PM
thanks for your reply! But have you tried my provided script (see details)? I know I can create SQL modules like triggers and procedures containing objects which does not exists yet (your first link).

But within my provided script, the creation of the trigger fails without any error (because it is syntactically correct).
Please give my script another try.

I'll attach two files with the scripts I've used. The second one shows the same issue on the creation of procedures.

With best regards,
Posted by Umachandar [MSFT] on 2/28/2011 at 9:05 PM
Thanks for your feedback. The behavior you are seeing is by design. You can observe it for modules in general - procedures, functions and triggers. When parsing a module during creation, apart from checking the syntax of the statements we also try to resolve object names used in the statements. In cases where we don't find the object in the database then we defer the name resolution / compilation to runtime. So later when the object is created and the code is run it will work fine. The link below has more details on this:

If you want to check for missing objects or wrong object names and such you can use the new DMVs to discover missing dependencies. See link below:

Hope this helps explains the behavior.

Umachandar, SQL Programmability Team