Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Nested Trigger error when doing a select on a view?

    Good Morning,
    In a SQL db we have we get the following error when just doing a simple select query against the view.

    Msg 217, Level 16, State 1
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    No changes have been made to triggers or stored procedures recently and all was good prior to that.

    I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
    Code:
    SELECT     TOP (100) PERCENT O.EID, O.OStart, O.OEnd, O.OID, T.Title, P.PStatus AS PS, dbo.CalcAge(O.OStart, ISNULL(O.OEnd, CURRENT_TIMESTAMP)) AS ODuration, O.PID, 
                          O.Residence, O.b55, O.SplitItem, O.PeakStaff, O.ResidenceSub, O.Negotiator, O.Supervisor, O.TimeType, O.BreakPM, O.WorkEnd, O.Lunch, O.BreakAM, O.WorkBegin, 
                          O.SupervisorPosition, O.HDCPosition, P.PType, P.ItemEnd, P.LoanEnd, P.LoanStart, P.SResidence, P.LoanType, P.LoanStatus, P.PStatus, 
                          dbo.EmpName(O.Supervisor) AS SupName, ISNULL(O.OEnd, CURRENT_TIMESTAMP) AS SortDate, O.IsSup, O.IsMgr, O.ManagerPosition, 
                          '#' + CAST(O.OID AS varchar(20)) + ':  ' + O.PID + ' ~ ' + RTRIM(T.Title) AS OccupancyTitle
    FROM         dbo.tblOccupancy AS O INNER JOIN
                          dbo.tblPositions AS P ON O.PID = P.PID INNER JOIN
                          dbo.tblTitles AS T ON P.Title = T.TID
    ORDER BY O.OStart, SortDate DESC

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Triggers don't fire on SELECTs.

    The problem is in your dbo.EmpName(O.Supervisor) function call.
    Either because of the nature of the data, or the nature of the algorithm, you are exceeding 32 levels in your hierarchy.
    Convert this function to use a CTE (Common Table Expression) for recursive processing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    Using the prefix “tbl-” is such a bad practice that it has a name! Tibbling! We do not format display data in a query; that is what presentation layers do. Columns change names based on which table they are in your code, while other data element names are so vague as to be useless. We do not write with flags in SQL (is that what “is_sup_flg and is_mgr_flg do?); that was assembly language, not SQL. We also do not use UDFs; they screw up the optimizer, avoid proper declarative programming and cannot be maintained. They also tend to do just what you have seen; hang in loops, eat up resources and crash the apps. Since we cannot see the code, I will bet that you are using an adjacency list model for the organizational structure. That requires a non-relational recursive CTE (hidden in a cursor and loop) and tehy crap out after running orders of magnitude slower than a nested sets model.

    But UDFs do make your SQL look like COBOL or BASIC. Traversing an adjacency list feels like procedural code, too.

    If you really want help, post your DDL, so we can re-design correctly.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    People frequently (make that usually) have no say over the naming convention of the database with which they have to work.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Talking

    Quote Originally Posted by blindman View Post
    People frequently (make that usually) have no say over the naming convention of the database with which they have to work.
    That is good! In the old days of procedural code and file systems, every programmer invented his own data element names (make that "field and record names") becasue programs were not shared. I worked with one guy who would pick a theme for his programs and write his COBOL from it! There was the flower garden payroll ("PERFORM Rosebush") or world tour vacation time ("GO TO Belgium"). I wish this was a joke.

    This ought to be the job of the Data Architect when he sets up the Data Dictrionary. I did research on this topic for AIRMICS; good data element names, and formatting save 8-12% of the cost of maintaining a system over its lifetime.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •