0

I need to have a nested if statement in an update trigger. How do I say with an If statement in sql -- if two columns don't equal each other - throw an error message (don't shut down the system - just throw a message alerting the user).

If(@order_tot_paid_amt > 0) -- that means that the user has entered a value. (and its calculated)

I've reviewed a few of the existing posts, and they differ from what I am asking. Do I need a catch statement? (I know in c++ you need a catch statement otherwise things shut down).

What I have so far is (and I know I'm missing data). 

IF (@order_tot_paid_amt > 0)
   IF @order_tot_paid_amt <> (select pmt_rcvd_amt from LT_CHC_TOURS_RSV_CS where id_key = @id_key)
THROW  ... 
CATCH  ...

I don't know where to get all the commands for throw and catch.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37
  • 1
    If statements in triggers are why we can't have nice things. – Joel Coehoorn Jan 14 '15 at 19:19
  • What version of SQL Server? – RBarryYoung Jan 14 '15 at 19:22
  • SQL server 2008 R2. (I need it in the trigger on the update -- because it only gets tested once data is updated into the table no need to do it before) – YelizavetaYR Jan 14 '15 at 19:26
  • 1
    From the overly simplified code you posted I can tell your trigger has a MAJOR flaw. It will not support multiple row operations. Your trigger needs to be set based and allow for multiple rows being updated in a single update statement. You have to remember that the trigger executes once per operation, not once per row. – Sean Lange Jan 14 '15 at 19:53
  • Each time the trigger executes (once per operation) the above stated query should return one value for each of the fields. They are a summation of a given set of data. the trigger needs to check if they are equal and if they are not - simply to display a message to the user to review the entered data. – YelizavetaYR Jan 14 '15 at 19:56
  • 1
    That logic belongs in the application – Joel Coehoorn Jan 14 '15 at 20:20
  • @YelizavetaYR correct the trigger will execute once per operation but if I inserted 100000 rows in a batch that is one operation and you are checking for one value and not checking 100000 values. This trigger is flawed. – Namphibian Jan 14 '15 at 20:32
  • @Namphibian there may be 10000 inserts - what I'm checking is for that given customer record -- take the two values (which are/can be the sum of many rows) and see if the two separate values are equivalent. How do i make it better? – YelizavetaYR Jan 14 '15 at 20:36
  • If the data is inserted in a batch with a transaction there will be one insert with 100000 rows not 100000 inserts. – Namphibian Jan 14 '15 at 20:37
  • how do you recommend i fix it. – YelizavetaYR Jan 14 '15 at 20:43
  • @YelizavetaYR I really need more information to help you. The code is very light on details. Maybe a table structure to start off with. Secondly keep this in mind if you inserted 10 rows in an operation and one row failed throwing an exception will roll back everything i.e. the other 9 rows wont be inserted. Maybe a check constraint would work better see this post http://stackoverflow.com/questions/15546169/check-constraint-in-sql-server-2008 – Namphibian Jan 14 '15 at 20:49
  • @Namphibian what I'm trying to do is - check on the update (in the update rows wouldn't be added but they'd be updated) it's possible multiple rows can be updated at once. What I'd like to do is say if those two fields -- don't match throw an error message/exception for the user. I don't necessarily need it to roll back everything - just display a message. Is that even possible? – YelizavetaYR Jan 14 '15 at 20:52
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68834/discussion-between-namphibian-and-yelizavetayr). – Namphibian Jan 14 '15 at 20:53

0 Answers0