Ib kauj ruam ntawm kauj ruam mus rau Teem dauv ... XWM rau kov SQL neeg rau zaub mov yuam

Txheeb xyuas cov kev ua yuam kev uas tsis cuam tshuam kev ua tiav

NYIAJ ... TSEEM TSEEM TSEEM CEEB hauv Transact- SQL paub thiab ua haujlwm yuam kev hauv koj cov ntaub ntawv database. Tsab ntawv no yog qhov cornerstone ntawm SQL neeg rau zaub mov yuam kev tuav thiab yog ib feem tseem ceeb ntawm kev tsim robust database daim ntawv sau npe. XEEM ... CATCH siv rau SQL neeg rau zaub mov pib nrog 2008, Azure SQL Database, Azure SQL Cov Ntaub Ntawv Warehouse thiab Cov Ntaub Ntawv Nres Parallel.

Qhiab NYHAJ TSHAJ

XEEM ua hauj lwm los ntawm kev tso cai rau koj los qhia ob nqe lus Transact-SQL: ib qho uas koj xav kom "sim" thiab lwm tus siv los "ntes" cov kev yuam uas tshwm sim. Thaum SQL Server ntsib ib tug TRY ... nqe lus hais, nws tau ua raws li cov lus muaj nyob rau hauv cov lus ciaj ciam kawg nkaus. Yog hais tias cov lus tshaj tawm cov lus tshaj tawm executes, SQL neeg rau zaub mov tsuas yog tsiv rau. Txawm li cas los xij, yog hais tias Tsoom Fwv Teb Chaws lus tshaj tawm ua yuam kev, SQL neeg rau zaub mov executes cov lus qhuab qhia rau cov lus gracefully lis qhov yuam kev.

Lub ntsiab syntax yuav siv daim ntawv no:

NYUAS THAUM TWG {sql_statement | statement_block} THEM NTAU NTXIV BEGIN CHANG [{sql_statement | statement_block}] kawg tuam tsev; ]

DAWB ... KWS QHIA

Nws yooj yim rau kev nkag siab txog kev siv cov lus qhia no los ntawm kev siv tus piv txwv. Xav txog tias koj yog tus thawj coj ntawm cov chaw muab kev pab tib neeg uas muaj lub npe hu ua "Neeg Ua Hauj Lwm," uas muaj cov lus qhia txog txhua tus neeg ua hauj lwm hauv koj lub koom haum. Lub rooj ntawv siv tus zauv ID tus neeg ua num ua tus thawj qhov tseem ceeb . Koj tuaj yeem sim siv cov lus hauv qab no los sau tus neeg ua haujlwm tshiab rau hauv koj cov ntaub ntawv:

INSERT INTO neeg ua hauj lwm (ID, first_name, last_name, extension) VALUES (12497, 'Mike', 'Chapple', 4201)

Raws li ib txwm muaj tshwm sim, tsab ntawv no yuav ntxiv rau tus Neeg Ua Hauj Lwm cov lus. Txawm li cas los xij, yog ib tug neeg ua haujlwm nrog ID 12497 twb muaj nyob rau hauv cov ntaub ntawv, inserting cov kab yuav ua txhaum thawj qhov yuam sij thiab ua rau qhov yuam kev li hauv qab no:

Msg 2627, Theem 14, Xeev 1, Kab 1 Ua txhaum ntawm PRIMARY KEY constraint 'PK_employee_id'. Tsis ntxig duplicate qhov tseem ceeb ntawm cov khoom 'dbo.employees'. Cov lus tau raug kaw.

Thaum uas muaj qhov yuam kev no muab koj cov ntaub ntawv koj xav tau teeb meem qhov teeb meem, muaj ob qho teeb meem nrog nws. Ua ntej, cov lus yog cryptic. Nws muaj xws li yuam kev lis dej num, kab xov tooj thiab lwm yam ntaub ntawv uas tsis paub meej txog tus neeg siv nruab nrab. Thib ob, thiab qhov tseem ceeb dua, nws ua rau daim ntawv yuav tshem tawm thiab ua rau daim ntawv thov kev sib tsoo.

Qhov kev qhia yog los muab cov lus nyob rau hauv ib daim ntawv ... THAWV nqe, raws li qhia hauv qab no:

(12497, 'Mike', 'Chapple', 4201) kawg THAWJ TSWJ XEEB TXHUA HNIAV 'yuam kev:' + ERROR_MESSAGE (); EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Neeg ua hauj lwm xa ntawv', @recipients = 'hr@foo.com', @body = 'Ib qho yuam kev tshwm sim tsim ib tus neeg ua hauj lwm tshiab.', @subject = 'Neeg ua hauj lwm ID Duplication yuam kev'; END TECH

Hauv qhov ua piv txwv no, txhua yam teeb meem uas tshwm sim tau raug xa mus rau ob qho tib si rau tus neeg siv ua qhov kev txib thiab qhov hrm@foo.com chaw nyob. Qhov yuam kev uas tau tshwm rau tus neeg siv tshwm hauv qab no:

Yuam kev: Txhaum ntawm PRIMARY KEY constraint 'PK_employee_id'. Tsis ntxig duplicate qhov tseem ceeb ntawm cov khoom 'dbo.employees'. Xa ntawv xa tawm.

Feem ntau cov tseem ceeb, daim ntawv thov tseem niaj hnub, lub programmer kom gracefully lis qhov yuam kev. Kev siv ntawm cov NYIAJ ... CATCH daim ntawv qhia yog ib qho yooj yim txoj kev proactively ntes thiab lis yuam uas tshwm sim nyob rau hauv SQL neeg rau zaub mov database daim ntaub ntawv.

Kawm Ntxiv

Yog tias koj xav kawm ntxiv txog Lus Tsim Kho Lus Askiv, nyeem rau Introduction to SQL .