none
Compile (syntax check) triggers

    Question

  • Hi,

    due to the discontinued RAISERROR syntax in SQL 2012, we had to rewrite a lot of our triggers and stored procs. That process introduced new bugs in the code, which we are finding only when the triggers are caused to fire.

    Is there a way to compile, syntax check stored procs and triggers in SQL? Coming from Oracle i cannot understand how i can load a syntax incorrect proc or trigger into SQL without it raises an error. An example is that in the copy and paste process, we missed a variabled in a cursor fetch. The trigger was loaded into the database, but we only found it once a user tried an action in the app which fired this particular trigger.

    Thanks,

    Robert

    Saturday, January 05, 2013 1:21 AM

Answers

  • A missing variable in FETCH is not a syntax error in SQL Server.

    And, no, I am not going to defend it. It's a big shame, that is what it is. I have a Connect request to change all of this. I expect Microsoft to implement when hell freezes over. You can read about the ideas here: http://www.sommarskog.se/strict_checks.html

    Many years ago I wrote a tool to invoke all stored procedures and triggers in a database, but I have not used it (or maintained it) for a long times. I wrote it for SQL 6.5 where some errors were not detected when you created a procedure, but well when the optimizer built the query plan. There are not many such errors today, and if you actually run the code it is very likely that you get spurious errors. (The tool would call all procedures with the same set of junk parameters.)

    To be fair, Microsoft does have an offering in this area, SQL Server Data Tools, which is a free download. SSDT will give you warings for things like misspelled table names, or parameter mismatches in stored procedure calls. I don't know if the check FETCH statements, but I would not count on it.

    By the way, cursors in triggers is something I would discourage from. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by AU-Rabbit Wednesday, January 09, 2013 1:10 AM
    Saturday, January 05, 2013 11:12 AM

All replies

  • The old  RAISERROR should still be there. A better question is why aren't you getting rid of TRIGGERs in favor of DRI actions, CHECK() constraints, and other declarative constructs? My heuristic that you should write no more than five triggers in your entire career and that you ought to die behind a keyboard. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, January 05, 2013 2:02 AM
  • Not really helpful. I can't do anything about long standing designs or customers which choose SQL over Oracle. I've got the problem that I'd like to validate the syntax in all our database stores procs and triggers. Yes raiserror is still there, but requires (). So we put try catches in all our triggers and changed couple other things. The deferred name resolution isn't quite working as documented either. Surely I understand it if a table would be missing, but in my example we select 3 columns in a cursor, however only provided 2 variables to fetch it into. That's syntactically wrong, yet the proc loaded without error. We got the error once the proc was executed for the first time. Basic question: Is there anything like alter procedure X compile; which actually syntax checks everything and reports errors at that point?
    Saturday, January 05, 2013 7:24 AM
  • >Is there anything like alter procedure X compile; which actually syntax checks everything and reports errors at that point?

    No, there is no such a thing. Some errors caught at runtime only. Not a big deal, you just have to get used to it.  Do more manual checking of the code.

    The order of solution implementation with SQL Server is the following:

    table design >> constraints >> stored procedures >> triggers >> application

    UDF (user-defined function) CHECK constraints are extremely powerful because they are not limited to the current row or current table:

    http://www.sqlusa.com/bestpractices/udf-check-constraint/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, January 05, 2013 8:56 AM
    Moderator
  • We migrated from Sybase to MS SQL last year. Although you probably do not think my remark helpfull, I must say that we did some very extensive testing before we went life. Things like runtime errors in triggers should be found during testing. You can migrate the objects and the data, do testing, change code and finally migrate the data again from the Oracle system. But this doesn't help you now, because you are already life, as I read it. Here is a trick that helps you find calls to objects that do not exist. This also causes runtime errors instead of compile time ones. Here at home I have SQL Server 2005, so I wasn't able to test the script. You should have SQL Server 2008, at least, since that is the version in which the dynamic management views sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities were introduced. I will test this script on Monday and I will let you know if it worked or if it needed some changes. As you can read in the T-SQL, I assume you do not have cross server and/or cross database references.

    BEGIN
        CREATE TABLE #NonExistingRefs
          ( CallingObject  nvarchar(261)  NOT NULL
          , CalledSchema   nvarchar(128)  NULL
          , CalledObject   nvarchar(128)  NOT NULL
          , CalledColumn   nvarchar(128)  NULL
          );
    
        DECLARE crObjects CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
            SELECT type                   AS ObjectType
            ,      SCHEMA_NAME(schema_id) AS ObjectSchema
            ,      name                   AS ObjectName
            FROM   sys.objects
            WHERE  type NOT IN ('IT', 'S', 'SQ')
            ORDER  BY ObjectType, ObjectSchema, ObjectName;
    
        DECLARE @ObjectType    nvarchar(128)
        ,       @ObjectSchema  nvarchar(128)
        ,       @ObjectName    nvarchar(128)
        ,       @CallingObj    nvarchar(261);
    
        OPEN crObjects;
        FETCH crObjects INTO @ObjectType, @ObjectSchema, @ObjectName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @CallingObj = N'[' + @ObjectSchema + N'].[' + @ObjectName + ']';
    
            INSERT INTO #NonExistingRefs
            (      CallingObject
            ,      CalledSchema
            ,      CalledObject
            ,      CalledColumn
            )
            SELECT @CallingObj            AS CallingObject
            ,      referenced_schema_name AS CalledSchema
            ,      referenced_entity_name AS CalledObject
            ,      referenced_minor_name  AS CalledColumn
            FROM   sys.dm_sql_referenced_entities(@CallingObj, N'OBJECT')
            WHERE  referenced_id IS NULL
            AND    referenced_server_name IS NULL
            AND    referenced_database_name IS NULL;
    
            FETCH crObjects INTO @ObjectType, @ObjectSchema, @ObjectName;
        END;
        CLOSE crObjects;
    
        DEALLOCATE crObjects;
    
        SELECT CallingObject
        ,      CalledSchema
        ,      CalledObject
        ,      CalledColumn
        FROM   #NonExistingRefs;
    
        DROP TABLE #NonExistingRefs;
    END;



    Saturday, January 05, 2013 10:29 AM
  • A missing variable in FETCH is not a syntax error in SQL Server.

    And, no, I am not going to defend it. It's a big shame, that is what it is. I have a Connect request to change all of this. I expect Microsoft to implement when hell freezes over. You can read about the ideas here: http://www.sommarskog.se/strict_checks.html

    Many years ago I wrote a tool to invoke all stored procedures and triggers in a database, but I have not used it (or maintained it) for a long times. I wrote it for SQL 6.5 where some errors were not detected when you created a procedure, but well when the optimizer built the query plan. There are not many such errors today, and if you actually run the code it is very likely that you get spurious errors. (The tool would call all procedures with the same set of junk parameters.)

    To be fair, Microsoft does have an offering in this area, SQL Server Data Tools, which is a free download. SSDT will give you warings for things like misspelled table names, or parameter mismatches in stored procedure calls. I don't know if the check FETCH statements, but I would not count on it.

    By the way, cursors in triggers is something I would discourage from. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by AU-Rabbit Wednesday, January 09, 2013 1:10 AM
    Saturday, January 05, 2013 11:12 AM
  • If you misspell a the name of a permanent table, the script will flag it.

    However, this procedure is not caught

    CREATE PROCEDURE bad_news AS
    CREATE TABLE #temp(a int NOT NULL)
    SELECT OrderID
    FROM   Orders O
    JOIN   #temp t ON O.nosuchcolumn = t.a

    Nor:

    CREATE PROCEDURE bad_news AS
    CREATE TABLE #temp(a int NOT NULL)
    SELECT OrderID
    FROM   Orders O
    JOIN   #tmp t ON O.EmployeeID = t.a

    Both these errors were caught in SQL Server 6.5.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 05, 2013 2:34 PM
  • Thanks Erland.

    I thought about the script idea. Invoking stored procs and functions is reasonable straight forward. However my legacy issue is that someone designed several thousand triggers which can only be triggered by inserts, updates and deletes and those are further complicated by not null and check constraints.

    Well i just have to live with it.

    Wednesday, January 09, 2013 1:14 AM
  • Hi, I have a question additional to AU-Rabbit's one.We are plannig to upgrade our 2008 environments to 2012 soon  and I checked that about 5000 stored procedure&triggers includes old format of RAISERROR, RAISERROR <NUMBER> <MESSAGE>. So when we upgrade our sytem to 2012, our most applications probably will explode:). Is there a way to rewrite all these sps and triggers  without manually identifying and changing them to the new format one by one?

    Tuesday, March 12, 2013 4:06 PM