[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ways to debug a check constraint which is unsing a UDF

Posted on 2014-01-17
3
Medium Priority
?
711 Views
Last Modified: 2014-01-17
Hi, I know that you cannot use print, raise error or execute any table data changes from a UDF, but does anyone have a good approach to debug a constraint. It's rejecting all data and I can't see why.

The constraint uses a UDF. If I run the UDF on it's own, it gives me the expected outcome which would allow the check constraint to be satisfied, however in the check constraint the exact same data seems to get a different result from the UDF. The UDF returns 1 or 0 depending on if it's checks on the data are acceptable or not.

I have even hard coded the data that is tested within the UDF, so it's not that the constraint is passing different data.

it's confused me no end! I could really do with seeing what the UDF is doing using some kind of output. I know that the raiseerror, print, logging to table, etc are all out. also profiler doesn't pick up the UDF being called from the constraint.

is there anything I can do to debug this?

thanks
0
Comment
Question by:PatrickK_W
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 1

Author Comment

by:PatrickK_W
ID: 39787838
sorry - I should have mentioned what the function does.

I have a table of bookings. I cannot have any overlapping bookings, so the UDF lokos at the booking table and ensures that the new booking (it's just a start and end date) don't overlap with any existing records int eh booking table.

so if I run the UDF froma query it works, but inserting the same 2 dates I tested with fails.

I assume it's because it's finding the record that I have just inserted and so fails, but i'm trying to pass the PK of the new record to the UDF via the constraint and ignore that new record.

this is why I could do with seeing some debug info from the UDF - see if it is picking up the inserting row as a duplicate
0
 
LVL 1

Author Comment

by:PatrickK_W
ID: 39787914
my understanding is that the order of execution is
instead of trigger
constraints
after insert trigger


so I thought this would work:
1) add a bit field as a flag in my booking table. default to 0
2) in the UDF called by constraint, exclude any records with this flag set to 0
3) in the after insert trigger set the flag field to 1

so I tested this without updating the flag to 1 (step 3) and it lets me insert the data as i'd expect.

but as soon as I added step 3 and set the inserted rows flag field to 1 the check constraint starts failing again, because it seems to be including the inserting row again in the UDF.

I thought that the constraint would see the row as having the flag field set to 0, as it is executed before the after insert trigger??
0
 
LVL 1

Accepted Solution

by:
PatrickK_W earned 0 total points
ID: 39788287
I've re-writtenteh check constraint into an after trigger. this has allowed tme to get some fedbcak and print out variable etc.it also works without any changes, so I think i'll leave it like that!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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