Bienvenue !

Transactions et gestion des erreurs dans SQL Server

Miriam Antona

Software Engineer

Sujets abordés

Chapitres 1 et 2 : Gestion des erreurs
Chapitre 3 : Transactions
Chapitre 4 : Concurrence dans les transactions

Ensemble de données : magasin de vélos électriques

Produits

| product_id | product_name                    | stock | price   |
|------------|---------------------------------|-------|---------|
| 1          | Trek Conduit+ - 2016            | 50    | 2999.99 |
| 2          | Sun Bicycles ElectroLite - 2017 | 47    | 1559.99 |
| 3          | Trek Powerfly 8 FS Plus - 2017  | 41    | 4999.99 |
| 4          | Trek Conduit+ - 2018            | 10    | 2799.99 |
| 5          | Trek CrossRip+ - 2018           | 12    | 4499.99 |

Ensemble de données : magasin de vélos électriques

Acheteurs

| buyer_id | first_name | last_name | email                 | phone     |
|----------|------------|-----------|-----------------------|-----------|
| 1        | Dylan      | Smith     | [email protected]   | 555888999 |
| 2        | John       | Antona    | [email protected]   | 555111222 |
| 3        | Astrid     | Harper    | [email protected] | 555000999 |
| 4        | Angus      | Brown     | [email protected]   | 555222012 |
| 5        | David      | Elcano    | [email protected]  | 555602314 |

Ensemble de données : magasin de vélos électriques

Personnel

| staff_id | first_name | last_name | email                   | phone     |
|----------|------------|-----------|-------------------------|-----------|
| 1        | Mohammed   | Ferrec    | [email protected] | 555888111 |
| 2        | Dimitri    | Brown     | [email protected]   | 555012012 |
| 3        | Leila      | Merheg    | [email protected]    | 555999133 |
| 4        | Mateo      | Casanovas | [email protected] | 555110996 |
| 5        | Carl       | York      | [email protected]       | 555010011 |

Ensemble de données : magasin de vélos électriques

Commandes

| order_id | product_id | buyer_id | staff_id | price | order_date |
|----------|------------|----------|----------|-------|------------|
| 1        | 2          | 1        | 5        | 100   | 1559.99    |
| 2        | 2          | 5        | 5        | 100   | 1559.99    |
| 3        | 5          | 10       | 1        | 100   | 4499.99    |
| 4        | 10         | 3        | 3        | 100   | 2799.99    |
| 5        | 15         | 2        | 7        | 100   | 2299.99    |

Obtenir une erreur

| product_id | product_name                        | stock | price   |
|------------|-------------------------------------|-------|---------|
| 19         | Trek Powerfly 5 - 2018              | 15    | 3499.99 |

CONSTRAINT unique_product_name UNIQUE (product_name);

INSERT INTO products (product_name, stock, price)
    VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);

Violation of UNIQUE KEY constraint 'unique_product_name'. 
Cannot insert duplicate key in object 'dbo.products'. 
The duplicate key value is (Trek Powerfly 5 - 2018).

La syntaxe TRY...CATCH

BEGIN TRY  
     { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
     [ { sql_statement | statement_block } ]  
END CATCH  
[ ; ]

Placez vos instructions dans le bloc TRY
Placez le code de gestion d'erreur dans le bloc CATCH
Erreur dans le bloc TRY -> le bloc CATCH prend le relais
Aucune erreur dans le bloc TRY -> le bloc CATCH est ignoré

Exemple avec TRY...CATCH

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
    SELECT 'Product inserted correctly!' AS message;
END TRY
BEGIN CATCH
    SELECT 'An error occurred! You are in the CATCH block' AS message;  
END CATCH

| message                                       |
|-----------------------------------------------|
| An error occurred! You are in the CATCH block |

Exemple avec TRY...CATCH

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Super new Trek Powerfly', 5, 1499.99);
    SELECT 'Product inserted correctly!' AS message;
END TRY
BEGIN CATCH
    SELECT 'An error occurred! You are in the CATCH block' AS message;
END CATCH

| message                     |
|-----------------------------|
| Product inserted correctly! |

Imbriquer des TRY...CATCH

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
    SELECT 'Product inserted correctly!' AS message;
END TRY
BEGIN CATCH
    SELECT 'An error occurred inserting the product!
        You are in the first CATCH block' AS message;
    BEGIN TRY
        INSERT INTO myErrors 
            VALUES ('ERROR!');
        SELECT 'Error inserted correctly!' AS message;
    END TRY    
    BEGIN CATCH
        SELECT 'An error occurred inserting the error!
            You are in the second CATCH block' AS message;
    END CATCH    
END CATCH

| message                                   |
|-------------------------------------------|
| An error occurred inserting the product!  |        
| You are in the first CATCH block          |

| message                                   |
|-------------------------------------------|
| An error occurred inserting the error!    |        
| You are in the second CATCH block         |

Passons à la pratique !

Transactions et gestion des erreurs dans SQL Server