[Webinar] Learn how to a build a cloud-first strategyRegister Now


T-sql Update Trigger Query

Posted on 2014-08-10
Medium Priority
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

Dany Balian earned 1000 total points
ID: 40252216
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
Dale Burrell earned 1000 total points
ID: 40252218
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

ID: 40252565
Excellent,thank you both of you

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard 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.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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