Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 645692
Opened: 2/21/2011 6:10:08 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft 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 Microsoft 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.

--
Umachandar
Posted by NKcVE on 2/28/2011 at 11:57 PM
Hello,
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,
Robert.
Posted by Microsoft on 2/28/2011 at 9:05 PM
Hi,
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:

http://msdn.microsoft.com/en-us/library/ms190686.aspx

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:

http://msdn.microsoft.com/en-us/library/ms345449.aspx

Hope this helps explains the behavior.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
silent fail create proc.sql 2/28/2011 939 bytes
silent fail create trigger.sql 2/28/2011 968 bytes