How to raise error forcebily

Question

How to raise error forcebily

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
March 24, 2017 at 11:38 am

#401842

I have 4 records in that I want to fail 2 record .
How can I raise the error at the 2 record?
I know constraint voilation errors are done here to achieve this but I don't want to delete parent record which is present in child table
because the purpose is I have to test if one record fails is it roll backing all 4 records or not -

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mcfarlandparkway - Friday, March 24, 2017 11:38 AM
I have 4 records in that I want to fail 2 record .
How can I raise the error at the 2 record?
I know constraint voilation errors are done here to achieve this but I don't want to delete parent record which is present in child table
because the purpose is I have to test if one record fails is it roll backing all 4 records or not -

Not sure how to answer such a vague question. If you could provide a concrete example of what you are talking about by providing the DDL for the tables, some sample data as INSERT INTO statements to the tables that is representative of the problem domain, expected results when all is done AND what you have done so far so we know where you are at in this regard.

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 2

You asked this same question a few days ago, your spelling of forcibly is very distinctive I'm not quite sure who bily is or why I'm trying to force him to raise an error.

But if you want to test the basics of SQL transaction roll backs.

CREATE TABLE #TESTY(COL_ONE decimal(29, 9))

INSERT INTO #TESTY SELECT 5.4 UNION SELECT 6.2 UNION SELECT 7.2 UNION SELECT 8.6

INSERT INTO #TESTY SELECT 5.4 UNION SELECT 6.2 UNION SELECT 7.2 UNION SELECT 5.0/0.0

SELECT * FROM #TESTY

Lowell SSC Guru Points: 323518 More actions · Answer 3

i suspect what you really need to do is produce an exception report to find data that was inserted or updated, but that now violates a business rule, which is not enforced by a proper constraint.

if we had details of your parent/child table, and what constitutes the actual error, we could , for example, offer a check constraint that uses a user defined function...or a unique constraint that prevents more than one row per foregin key, or something.

help us help you.
sampel,t tables and sample data that demonstrates the issue gets you a tested , working solution by many of the big names inthe SQL community.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!