T-sql Update Trigger Query

Posted on 2014-08-10
Last Modified: 2014-08-11
See below my update Query
Alter Trigger  [dbo].[empBack]
ON [DB].[dbo].[mainSal]

declare @emp_num nvarchar(7)
DECLARE @AnuualSalary decimal(18,2)

SELECT @emp_num =i.emp_num,@AnuualSalary=i.salary from inserted i;  
   UPDATE mainSal
   Set salary=0.00,annual=0.00
   where emp_num=@emp_num
      UPDATE empBack 
   Set AnnualSalary=@AnuualSalary
   where emp_num=@emp_num


Open in new window

What i need is if someone insert record in "mainSal" Table Salary and anuual salary going to be 0 and
empBack table updated record is going to be save.

My getting following error

"Maximum stored procedure function,trigger or View Nesting Level exceeded limt 32"

Can some one show me where im doing the mistake. Thanks
Question by:ukerandi
    LVL 11

    Accepted Solution

    the sql:
    UPDATE mainSal
       Set salary=0.00,annual=0.00
       where emp_num=@emp_num

    Open in new window

    will force the trigger to run again.

    the solution would be to read the old values, to see if they are 0 before running the update.

    SELECT @AnuualSalary=d.salary from deleted d;
    if @anuualSalary>0 //this means that there were some values in the table before the update
    SELECT @emp_num =i.emp_num,@AnuualSalary=i.salary from inserted i; //fetching the new values
    UPDATE mainSal
       Set salary=0.00,annual=0.00
       where emp_num=@emp_num;
    UPDATE empBack 
       Set AnnualSalary=@AnuualSalary
       where emp_num=@emp_num;

    Open in new window

    LVL 21

    Assisted Solution

    by:Dale Burrell
    OK, there are couple of flaws in your trigger.

    1/ You should never, ever assume that you're only getting a single record update. SQL uses set based logic and so should you unless there is a very good reason not to, in which case you have to loop through all records.

    So you want to design your update a bit like this, so it can handle any number of records:

       UPDATE mainSal set
       from mainSal, inserted i
       where mainSal.emp_num = i.emp_num

    Open in new window

    2/ Your trigger is going to create an infinite loop, because you are updating the table that the trigger runs on. And triggers always fire even if no records are update! Thats a trap for new players, so when updating the same table as the trigger, you need to make your update statement conditional to ensure at some point it stops firing e.g.

    if exists ({some condition for update}) being
      update ...

    Open in new window

    I don't quite understand what your update is trying to achieve so I can't be anymore specific than that unfortunately.

    LVL 10

    Author Closing Comment

    Excellent,thank you both of you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    SQL Select - AVG 3 36
    SQL Server Degrading on Write 13 49
    VB Access SQL question 2 16
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now