Day 21: Week 3 review & integration

A. Lesson overview

Activity breakdown

Business scenario. Merchandising wants a product catalogue they can maintain without direct table access: add products, correct names and prices, retire products, and load the supplier’s price file. Every change must be audited, every failure logged, and two people editing the same product must not overwrite each other’s work.

B. Theory explained clearly

Part 1 — The design

Constraints and the trigger protect the data even from an ad hoc UPDATE in SSMS; procedures carry the business rules that need a decision (“hard delete or retire?”).

Part 2 — Optimistic concurrency

Two people open product TNT-002. Alice changes the price; Bob, still looking at the old screen, changes the name and saves — and his save also writes back the old price. Alice’s change is lost. This is a lost update.

Locking a row while someone looks at a screen is not practical. Instead:

1. Add a rowversion column. SQL Server changes its value automatically every time the row is updated (it’s an 8-byte, database-wide increasing number — not a date and time).
2. The application reads the row and its rowversion.
3. The update says WHERE ProductID = @ProductID AND RowVer = @ExpectedRowVer.
4. If no row matches, someone changed it in the meantime: report a concurrency error and let the user reload.

UPDATE Lab.CatalogueProduct
SET    ListPrice = @ListPrice
WHERE  ProductID = @ProductID
  AND  RowVer    = @ExpectedRowVer;      -- 0 rows → changed by someone else (or doesn't exist)

This is called optimistic because it assumes conflicts are rare and only checks at save time.

Part 3 — The contract

Success is RETURN 0, with output parameters for the new ID, new rowversion or outcome. Every failure: roll back, log, THROW — Day 20’s pattern.

C. Dataset and starting state

Setup (re-runnable). Destructive for the three catalogue tables only. It creates Lab.ErrorLog if Day 20’s copy is missing, and re-creates Lab.usp_LogError exactly as on Day 20.

USE RetailLab;
GO

DROP TABLE IF EXISTS Lab.CatalogueAudit, Lab.CatalogueProduct, Lab.PriceFileResult;

IF OBJECT_ID(N'Lab.ErrorLog', N'U') IS NULL
    CREATE TABLE Lab.ErrorLog
    (
        ErrorLogID    int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ErrorLog PRIMARY KEY,
        LoggedAt      datetime2(0)   NOT NULL CONSTRAINT DF_ErrorLog_LoggedAt DEFAULT (SYSDATETIME()),
        LoginName     sysname        NOT NULL CONSTRAINT DF_ErrorLog_Login    DEFAULT (ORIGINAL_LOGIN()),
        ErrorNumber   int            NOT NULL,
        Severity      int            NULL,
        ErrorState    int            NULL,
        ProcedureName nvarchar(128)  NULL,
        LineNumber    int            NULL,
        ErrorMessage  nvarchar(4000) NOT NULL
    );
GO

CREATE OR ALTER PROCEDURE Lab.usp_LogError
AS
BEGIN
    SET NOCOUNT ON;
    IF ERROR_NUMBER() IS NULL RETURN;
    IF XACT_STATE() = -1
    BEGIN
        PRINT N'usp_LogError: roll back before logging.';
        RETURN;
    END;
    INSERT INTO Lab.ErrorLog (ErrorNumber, Severity, ErrorState, ProcedureName, LineNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());
END;
GO

CREATE TABLE Lab.CatalogueProduct
(
    ProductID    int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CatalogueProduct PRIMARY KEY,
    ProductCode  varchar(20)    NOT NULL CONSTRAINT UQ_CatalogueProduct_Code UNIQUE,
    ProductName  nvarchar(100)  NOT NULL,
    CategoryID   int            NOT NULL
        CONSTRAINT FK_CatalogueProduct_Category REFERENCES Inventory.Category (CategoryID),
    ListPrice    decimal(10,2)  NOT NULL CONSTRAINT CK_CatalogueProduct_ListPrice CHECK (ListPrice > 0),
    StandardCost decimal(10,2)  NOT NULL CONSTRAINT CK_CatalogueProduct_Cost      CHECK (StandardCost >= 0),
    IsActive     bit            NOT NULL CONSTRAINT DF_CatalogueProduct_IsActive  DEFAULT (1),
    RowVer       rowversion     NOT NULL
);

SET IDENTITY_INSERT Lab.CatalogueProduct ON;
INSERT INTO Lab.CatalogueProduct (ProductID, ProductCode, ProductName, CategoryID, ListPrice, StandardCost, IsActive)
SELECT ProductID, ProductCode, ProductName, CategoryID, ListPrice, StandardCost,
       CASE WHEN DiscontinuedDate IS NULL THEN 1 ELSE 0 END
FROM   Inventory.Product;
SET IDENTITY_INSERT Lab.CatalogueProduct OFF;

CREATE TABLE Lab.CatalogueAudit
(
    AuditID      int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CatalogueAudit PRIMARY KEY,
    AuditAction  char(1)        NOT NULL,
    ProductID    int            NOT NULL,
    ProductCode  varchar(20)    NOT NULL,
    OldName      nvarchar(100)  NULL,
    NewName      nvarchar(100)  NULL,
    OldListPrice decimal(10,2)  NULL,
    NewListPrice decimal(10,2)  NULL,
    OldIsActive  bit            NULL,
    NewIsActive  bit            NULL,
    AuditedAt    datetime2(0)   NOT NULL CONSTRAINT DF_CatalogueAudit_At DEFAULT (SYSDATETIME()),
    AuditedBy    sysname        NOT NULL CONSTRAINT DF_CatalogueAudit_By DEFAULT (ORIGINAL_LOGIN())
);

CREATE TABLE Lab.PriceFileResult
(
    RowID        int            NOT NULL CONSTRAINT PK_PriceFileResult PRIMARY KEY,
    ProductCode  varchar(20)    NULL,
    NewListPrice decimal(10,2)  NULL,
    Outcome      varchar(10)    NOT NULL,
    Reason       nvarchar(100)  NULL,
    ProcessedAt  datetime2(0)   NOT NULL CONSTRAINT DF_PriceFileResult_At DEFAULT (SYSDATETIME())
);
GO

Validation. Expected: 30, 29, 30, 0, 0.

SELECT  (SELECT COUNT(*) FROM Lab.CatalogueProduct)                    AS Products,
        (SELECT SUM(CAST(IsActive AS int)) FROM Lab.CatalogueProduct)  AS ActiveProducts,
        IDENT_CURRENT(N'Lab.CatalogueProduct')                         AS LastIdentity,
        (SELECT COUNT(*) FROM Lab.CatalogueAudit)                      AS AuditRows,
        (SELECT COUNT(*) FROM Lab.ErrorLog WHERE ErrorNumber BETWEEN 50300 AND 50399) AS CatalogueErrors;

Order matters today. The expected results build on each other: worked examples → lab → task 1 → task 2 → task 3. If you get lost, re-run the setup and repeat the steps in order. (CatalogueErrors counts only today’s error numbers, so older Day 20 log rows don’t interfere — unless you have done this lesson before; then delete rows 50300–50399 from Lab.ErrorLog first.)

D. Worked examples

Worked example 1 — The audit trigger

CREATE OR ALTER TRIGGER Lab.trg_CatalogueProduct_Audit
ON Lab.CatalogueProduct
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Lab.CatalogueAudit
          (AuditAction, ProductID, ProductCode, OldName, NewName,
           OldListPrice, NewListPrice, OldIsActive, NewIsActive)
    SELECT  CASE WHEN d.ProductID IS NULL THEN 'I'
                 WHEN i.ProductID IS NULL THEN 'D'
                 ELSE 'U' END,
            COALESCE(i.ProductID, d.ProductID),
            COALESCE(i.ProductCode, d.ProductCode),
            d.ProductName, i.ProductName,
            d.ListPrice,   i.ListPrice,
            d.IsActive,    i.IsActive
    FROM    inserted AS i
    FULL OUTER JOIN deleted AS d ON d.ProductID = i.ProductID
    WHERE   d.ProductID IS NULL OR i.ProductID IS NULL
       OR   i.ProductName  <> d.ProductName
       OR   i.ListPrice    <> d.ListPrice
       OR   i.StandardCost <> d.StandardCost
       OR   i.IsActive     <> d.IsActive;
END;
GO

SELECT COUNT(*) AS AuditRows FROM Lab.CatalogueAudit;

Expected result: 0 — the trigger was created after the initial load, so those 30 inserts were not audited (a deliberate choice: the baseline is the load itself). The columns compared are all NOT NULL, so <> is safe here; Day 19 used IS DISTINCT FROM for nullable columns.

Worked example 2 — Create

CREATE OR ALTER PROCEDURE Lab.usp_Catalogue_Create
    @ProductCode  varchar(20),
    @ProductName  nvarchar(100),
    @CategoryID   int,
    @ListPrice    decimal(10,2),
    @StandardCost decimal(10,2),
    @NewProductID int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Err nvarchar(2048);
    SET @ProductCode = UPPER(TRIM(@ProductCode));
    SET @ProductName = TRIM(@ProductName);

    BEGIN TRY
        IF @ProductCode IS NULL OR NOT REGEXP_LIKE(@ProductCode, '^[A-Z]{3}-[0-9]{3}$')
            THROW 50300, N'Product code must look like ABC-123.', 1;
        IF NULLIF(@ProductName, N'') IS NULL
            THROW 50300, N'Product name is required.', 1;
        IF @ListPrice IS NULL OR @ListPrice <= 0 OR @StandardCost IS NULL OR @StandardCost < 0
            THROW 50300, N'List price must be positive and standard cost must not be negative.', 1;
        IF NOT EXISTS (SELECT 1 FROM Inventory.Category WHERE CategoryID = @CategoryID)
        BEGIN
            SET @Err = FORMATMESSAGE(N'Category %d does not exist.', @CategoryID);
            THROW 50304, @Err, 1;
        END;
        IF EXISTS (SELECT 1 FROM Lab.CatalogueProduct WHERE ProductCode = @ProductCode)
        BEGIN
            SET @Err = FORMATMESSAGE(N'Product code %s already exists.', @ProductCode);
            THROW 50301, @Err, 1;
        END;

        BEGIN TRANSACTION;
            INSERT INTO Lab.CatalogueProduct (ProductCode, ProductName, CategoryID, ListPrice, StandardCost)
            VALUES (@ProductCode, @ProductName, @CategoryID, @ListPrice, @StandardCost);
            SET @NewProductID = SCOPE_IDENTITY();
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        EXEC Lab.usp_LogError;
        IF ERROR_NUMBER() IN (2601, 2627)                 -- lost a race on the unique code
        BEGIN
            SET @Err = FORMATMESSAGE(N'Product code %s already exists.', @ProductCode);
            THROW 50301, @Err, 1;
        END;
        THROW;
    END CATCH;
END;
GO

DECLARE @id int;
EXEC Lab.usp_Catalogue_Create @ProductCode = ' slp-004 ', @ProductName = N'Trail Pillow',
     @CategoryID = 3, @ListPrice = 24.99, @StandardCost = 9.50, @NewProductID = @id OUTPUT;
SELECT @id AS NewProductID;
GO

DECLARE @id int;
BEGIN TRY
    EXEC Lab.usp_Catalogue_Create 'SLP-004', N'Trail Pillow', 3, 24.99, 9.50, @id OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
END CATCH;

BEGIN TRY
    EXEC Lab.usp_Catalogue_Create 'sleep4', N'Pillow', 3, 24.99, 9.50, @id OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
END CATCH;
GO

SELECT AuditAction, ProductID, ProductCode, NewName, NewListPrice FROM Lab.CatalogueAudit ORDER BY AuditID;

Expected result: NewProductID 31 (code stored as SLP-004); then 50301 Product code SLP-004 already exists.; then 50300 Product code must look like ABC-123.; the audit has one row: I 31 SLP-004 Trail Pillow 24.99. SCOPE_IDENTITY() is safe here because exactly one row is inserted (Day 15).

Worked example 3 — Read (optional filters)

CREATE OR ALTER PROCEDURE Lab.usp_Catalogue_Get
    @ProductCode     varchar(20) = NULL,
    @CategoryID      int         = NULL,
    @IncludeInactive bit         = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  p.ProductID, p.ProductCode, p.ProductName, c.CategoryName,
            p.ListPrice, p.StandardCost, p.IsActive, p.RowVer
    FROM    Lab.CatalogueProduct AS p
    JOIN    Inventory.Category   AS c ON c.CategoryID = p.CategoryID
    WHERE  (@ProductCode IS NULL OR p.ProductCode = UPPER(TRIM(@ProductCode)))
      AND  (@CategoryID  IS NULL OR p.CategoryID  = @CategoryID)
      AND  (@IncludeInactive = 1 OR p.IsActive = 1)
    ORDER BY p.ProductCode
    OPTION (RECOMPILE);
END;
GO

EXEC Lab.usp_Catalogue_Get @CategoryID = 2;
EXEC Lab.usp_Catalogue_Get @CategoryID = 2, @IncludeInactive = 1;
EXEC Lab.usp_Catalogue_Get @ProductCode = 'tnt-002';

Expected result: 3 rows (TNT-001, TNT-002, TNT-003); then 4 rows (adds TNT-004, IsActive 0); then 1 row, TNT-002 at 349.00, with its RowVer value — something like 0x00000000000007D3; the exact number depends on your database’s activity, so yours will differ. OPTION (RECOMPILE) lets each call get a plan for the filters actually supplied (Day 24 explains why “catch-all” queries need it). Day 28 shows the dynamic SQL alternative.

E. Guided practical lab — Update with optimistic concurrency

Step 1 — Create the procedure

1. Do: Only supplied values change; the caller must pass the rowversion it read; the new rowversion is returned.
2. Run:

   USE RetailLab;
   GO
   
   CREATE OR ALTER PROCEDURE Lab.usp_Catalogue_Update
       @ProductID      int,
       @ExpectedRowVer binary(8),
       @ProductName    nvarchar(100)  = NULL,
       @ListPrice      decimal(10,2)  = NULL,
       @StandardCost   decimal(10,2)  = NULL,
       @NewRowVer      binary(8)      OUTPUT
   AS
   BEGIN
       SET NOCOUNT ON;
       SET XACT_ABORT ON;
   
       DECLARE @Err nvarchar(2048);
       DECLARE @Changed TABLE (RowVer binary(8));
   
       BEGIN TRY
           IF (@ListPrice IS NOT NULL AND @ListPrice <= 0) OR (@StandardCost IS NOT NULL AND @StandardCost < 0)
              OR (@ProductName IS NOT NULL AND TRIM(@ProductName) = N'')
               THROW 50300, N'List price must be positive, cost not negative, and name not blank.', 1;
   
           BEGIN TRANSACTION;
   
           UPDATE Lab.CatalogueProduct
           SET    ProductName  = COALESCE(TRIM(@ProductName), ProductName),
                  ListPrice    = COALESCE(@ListPrice,    ListPrice),
                  StandardCost = COALESCE(@StandardCost, StandardCost)
           OUTPUT inserted.RowVer INTO @Changed (RowVer)
           WHERE  ProductID = @ProductID
             AND  RowVer    = @ExpectedRowVer;
   
           IF NOT EXISTS (SELECT 1 FROM @Changed)
           BEGIN
               IF NOT EXISTS (SELECT 1 FROM Lab.CatalogueProduct WHERE ProductID = @ProductID)
               BEGIN
                   SET @Err = FORMATMESSAGE(N'Product %d does not exist.', @ProductID);
                   THROW 50302, @Err, 1;
               END;
               SET @Err = FORMATMESSAGE(N'Product %d was changed by another user. Reload it and try again.', @ProductID);
               THROW 50303, @Err, 1;
           END;
   
           COMMIT TRANSACTION;
           SELECT @NewRowVer = RowVer FROM @Changed;
           RETURN 0;
       END TRY
       BEGIN CATCH
           IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
           EXEC Lab.usp_LogError;
           THROW;
       END CATCH;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: OUTPUT … INTO both proves a row was updated and captures the new rowversion — no reliance on @@ROWCOUNT after a trigger has run. The “not found or changed?” check happens only on failure.
5. Confirm: SELECT OBJECT_ID(N'Lab.usp_Catalogue_Update'); is not NULL.

Step 2 — Alice updates the price

1. Do: Read the row’s version, then update with it.
2. Run:

   DECLARE @AliceVer binary(8), @BobVer binary(8), @New binary(8);
   SELECT @AliceVer = RowVer, @BobVer = RowVer FROM Lab.CatalogueProduct WHERE ProductCode = 'TNT-002';
   
   EXEC Lab.usp_Catalogue_Update @ProductID = 2, @ExpectedRowVer = @AliceVer,
        @ListPrice = 359.00, @NewRowVer = @New OUTPUT;
   SELECT @AliceVer AS VersionRead, @New AS VersionAfterUpdate;
   
   -- Bob still holds the old version (keep this window open for step 3)
   BEGIN TRY
       EXEC Lab.usp_Catalogue_Update @ProductID = 2, @ExpectedRowVer = @BobVer,
            @ProductName = N'Summit 4-Person Tent (2026)', @NewRowVer = @New OUTPUT;
   END TRY
   BEGIN CATCH
       SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
   END CATCH;

3. Observe: Two different binary values (the version changed); then 50303 Product 2 was changed by another user. Reload it and try again.
4. Why it matters: Bob’s save would have silently overwritten Alice’s price if the WHERE had used only ProductID. With the version check, Bob is told to reload.
5. Confirm: SELECT ProductName, ListPrice FROM Lab.CatalogueProduct WHERE ProductID = 2; → Summit 4-Person Tent, 359.00.

Step 3 — Bob reloads and retries

1. Do: Read the current version, then save again.
2. Run:

   DECLARE @BobVer binary(8), @New binary(8);
   SELECT @BobVer = RowVer FROM Lab.CatalogueProduct WHERE ProductID = 2;
   EXEC Lab.usp_Catalogue_Update @ProductID = 2, @ExpectedRowVer = @BobVer,
        @ProductName = N'Summit 4-Person Tent', @NewRowVer = @New OUTPUT;   -- same name: no real change
   SELECT ProductName, ListPrice FROM Lab.CatalogueProduct WHERE ProductID = 2;

3. Observe: Succeeds; name and price unchanged (Bob decided to keep the name).
4. Why it matters: An update that changes nothing still changes the rowversion — any UPDATE of the row does — but the trigger compares values, so it adds no audit row.
5. Confirm: SELECT COUNT(*) FROM Lab.CatalogueAudit WHERE ProductID = 2; → 1 (Alice’s price change).

Step 4 — A product that doesn’t exist

1. Do: Call with an unknown ID.
2. Run:

   DECLARE @New binary(8);
   BEGIN TRY
       EXEC Lab.usp_Catalogue_Update @ProductID = 999, @ExpectedRowVer = 0x0000000000000001,
            @ListPrice = 10.00, @NewRowVer = @New OUTPUT;
   END TRY
   BEGIN CATCH
       SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
   END CATCH;

3. Observe: 50302 Product 999 does not exist.
4. Why it matters: Distinguishing “gone” from “changed” lets the caller show the right message.
5. Confirm: SELECT ErrorNumber FROM Lab.ErrorLog WHERE ErrorNumber BETWEEN 50300 AND 50399 ORDER BY ErrorLogID; → 50301, 50300, 50303, 50302.

F. Independent practice

Do the tasks in order: the expected results depend on it.

Task 1 — Apply the supplier price file

Write Lab.usp_Catalogue_ApplyPriceFile (no parameters). In one transaction, it replaces the contents of Lab.PriceFileResult with one row per Staging.ProductPriceUpdate row: the normalised code (UPPER(TRIM())), the parsed price, Outcome (Applied / Rejected) and Reason. Check, in this order: missing code; price not a number; price ≤ 0; unknown product; product inactive; superseded by a later row for the same code. Then update the prices of the Applied rows. Use the Day 20 error pattern.

Success criteria:

5 audit rows (action U) are added — one per applied price.

Task 2 — Delete: remove or retire

Write Lab.usp_Catalogue_Delete @ProductID int, @Outcome varchar(12) OUTPUT. If the product has ever been sold (Sales.SalesOrderLine), set IsActive = 0 and return Deactivated; otherwise delete it and return Deleted. Unknown ID → 50302. Call it for NAV-004 (29), TNT-001 (1) and SLP-004 (31).

Success criteria: Deleted, Deactivated, Deleted. Audit adds D for 29 and 31 and U for 1 (IsActive 1 → 0).

Task 3 — Reconciliation

Write a single query that returns, in one row: product count, active count, audit rows by action (I, U, D as three columns), and the number of 503xx errors logged. Predict the values before running it.

Success criteria: 29 products, 27 active, I 1, U 7, D 2, 4 errors.

Stretch challenge — Execute-only access

Create role CatalogueEditor with EXECUTE on the five catalogue procedures only, and user MerchUser (without login) in that role. As MerchUser, run Lab.usp_Catalogue_Get @ProductCode = 'TNT-002' and then SELECT * FROM Lab.CatalogueProduct. Explain why one works and the other doesn’t. Clean up the user and role.

Success criteria: the procedure returns TNT-002 at 359.00; the SELECT fails with Msg 229 … The SELECT permission was denied on the object ‘CatalogueProduct’…

G. Solutions and explanations

CREATE OR ALTER PROCEDURE Lab.usp_Catalogue_ApplyPriceFile
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        DELETE FROM Lab.PriceFileResult;

        WITH Src AS
        (
            SELECT  RowID,
                    NULLIF(UPPER(TRIM(ProductCode)), '')            AS ProductCode,
                    TRY_CAST(TRIM(NewListPriceText) AS decimal(10,2)) AS NewListPrice,
                    ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(ProductCode)) ORDER BY RowID DESC) AS rn
            FROM    Staging.ProductPriceUpdate
        )
        INSERT INTO Lab.PriceFileResult (RowID, ProductCode, NewListPrice, Outcome, Reason)
        SELECT  s.RowID, s.ProductCode, s.NewListPrice,
                CASE WHEN r.Reason IS NULL THEN 'Applied' ELSE 'Rejected' END,
                r.Reason
        FROM    Src AS s
        LEFT JOIN Lab.CatalogueProduct AS p ON p.ProductCode = s.ProductCode
        CROSS APPLY (SELECT CASE
                        WHEN s.ProductCode  IS NULL THEN N'Missing product code'
                        WHEN s.NewListPrice IS NULL THEN N'Price is not a valid number'
                        WHEN s.NewListPrice <= 0    THEN N'Price must be positive'
                        WHEN p.ProductID    IS NULL THEN N'Unknown product code'
                        WHEN p.IsActive = 0         THEN N'Product is inactive'
                        WHEN s.rn > 1               THEN N'Superseded by a later row'
                     END AS Reason) AS r;

        UPDATE p
        SET    ListPrice = r.NewListPrice
        FROM   Lab.CatalogueProduct AS p
        JOIN   Lab.PriceFileResult  AS r ON r.ProductCode = p.ProductCode
        WHERE  r.Outcome = 'Applied'
          AND  p.ListPrice <> r.NewListPrice;

        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        EXEC Lab.usp_LogError;
        THROW;
    END CATCH;
END;
GO

EXEC Lab.usp_Catalogue_ApplyPriceFile;

SELECT RowID, ProductCode, NewListPrice, Outcome, Reason FROM Lab.PriceFileResult ORDER BY RowID;
SELECT ProductCode, OldListPrice, NewListPrice FROM Lab.CatalogueAudit
WHERE  AuditAction = 'U' AND ProductID <> 2 ORDER BY ProductCode;

CREATE OR ALTER PROCEDURE Lab.usp_Catalogue_Delete
    @ProductID int,
    @Outcome   varchar(12) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @Err nvarchar(2048);

    BEGIN TRY
        BEGIN TRANSACTION;

        IF NOT EXISTS (SELECT 1 FROM Lab.CatalogueProduct WITH (UPDLOCK, HOLDLOCK) WHERE ProductID = @ProductID)
        BEGIN
            SET @Err = FORMATMESSAGE(N'Product %d does not exist.', @ProductID);
            THROW 50302, @Err, 1;
        END;

        IF EXISTS (SELECT 1 FROM Sales.SalesOrderLine WHERE ProductID = @ProductID)
        BEGIN
            UPDATE Lab.CatalogueProduct SET IsActive = 0 WHERE ProductID = @ProductID AND IsActive = 1;
            SET @Outcome = 'Deactivated';
        END
        ELSE
        BEGIN
            DELETE FROM Lab.CatalogueProduct WHERE ProductID = @ProductID;
            SET @Outcome = 'Deleted';
        END;

        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        EXEC Lab.usp_LogError;
        THROW;
    END CATCH;
END;
GO

DECLARE @o1 varchar(12), @o2 varchar(12), @o3 varchar(12);
EXEC Lab.usp_Catalogue_Delete 29, @o1 OUTPUT;
EXEC Lab.usp_Catalogue_Delete 1,  @o2 OUTPUT;
EXEC Lab.usp_Catalogue_Delete 31, @o3 OUTPUT;
SELECT @o1 AS NAV004, @o2 AS TNT001, @o3 AS SLP004;

SELECT AuditAction, ProductID, ProductCode, OldIsActive, NewIsActive
FROM   Lab.CatalogueAudit
WHERE  AuditAction = 'D' OR (AuditAction = 'U' AND OldIsActive = 1 AND NewIsActive = 0)
ORDER BY AuditID;

SELECT  (SELECT COUNT(*) FROM Lab.CatalogueProduct)                                AS Products,
        (SELECT SUM(CAST(IsActive AS int)) FROM Lab.CatalogueProduct)              AS ActiveProducts,
        (SELECT COUNT(*) FROM Lab.CatalogueAudit WHERE AuditAction = 'I')          AS AuditInserts,
        (SELECT COUNT(*) FROM Lab.CatalogueAudit WHERE AuditAction = 'U')          AS AuditUpdates,
        (SELECT COUNT(*) FROM Lab.CatalogueAudit WHERE AuditAction = 'D')          AS AuditDeletes,
        (SELECT COUNT(*) FROM Lab.ErrorLog WHERE ErrorNumber BETWEEN 50300 AND 50399) AS CatalogueErrors;

CREATE ROLE CatalogueEditor;
GRANT EXECUTE ON Lab.usp_Catalogue_Create          TO CatalogueEditor;
GRANT EXECUTE ON Lab.usp_Catalogue_Get             TO CatalogueEditor;
GRANT EXECUTE ON Lab.usp_Catalogue_Update          TO CatalogueEditor;
GRANT EXECUTE ON Lab.usp_Catalogue_Delete          TO CatalogueEditor;
GRANT EXECUTE ON Lab.usp_Catalogue_ApplyPriceFile  TO CatalogueEditor;

CREATE USER MerchUser WITHOUT LOGIN;
ALTER ROLE CatalogueEditor ADD MEMBER MerchUser;
GO

EXECUTE AS USER = 'MerchUser';
    EXEC Lab.usp_Catalogue_Get @ProductCode = 'TNT-002';     -- works
REVERT;
GO
EXECUTE AS USER = 'MerchUser';
    SELECT * FROM Lab.CatalogueProduct;                      -- Msg 229
REVERT;
GO

-- Cleanup
DROP USER IF EXISTS MerchUser;
DROP ROLE IF EXISTS CatalogueEditor;

H. Knowledge check

1. Understanding. What problem does a rowversion check solve, and why is it called optimistic?

2. Debugging. A colleague’s update procedure checks IF @@ROWCOUNT = 0 THROW … after the UPDATE, and occasionally reports a concurrency error when nothing was wrong. The table has an audit trigger. Suggest a more robust approach.

3. Query interpretation. After Task 1, why does the price file produce 5 audit rows and not 6, although six rows had valid, positive prices for known products?

4. Understanding. Why does usp_Catalogue_ApplyPriceFile record rejections in a table instead of throwing an error for each bad row?

5. Understanding. Name the object that enforces each rule: (a) a product code is unique; (b) a sold product is retired, not deleted; (c) every price change is recorded, even from an ad hoc UPDATE; (d) list price is positive.

I. Troubleshooting and cleanup

Cleanup. Optional. Days 22 onwards don’t use the catalogue objects:

DROP PROCEDURE IF EXISTS Lab.usp_Catalogue_Create, Lab.usp_Catalogue_Get, Lab.usp_Catalogue_Update,
                         Lab.usp_Catalogue_Delete, Lab.usp_Catalogue_ApplyPriceFile;
DROP TABLE IF EXISTS Lab.CatalogueAudit, Lab.CatalogueProduct, Lab.PriceFileResult;

Keep Lab.ErrorLog and Lab.usp_LogError. Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• Create returned ID 31 and the two expected errors.
• Bob’s stale update failed with 50303; Alice’s price (359.00) survived.
• The price-file receipt matches all 10 rows.
• Delete returned Deleted, Deactivated, Deleted.
• Reconciliation: 29, 27, 1, 7, 2, 4.

Recap

• Constraints guard values; triggers guard history; procedures carry business decisions; error handling makes failures visible and safe.
• rowversion + WHERE RowVer = @Expected prevents lost updates without long locks.
• OUTPUT … INTO is a robust way to know what an UPDATE actually did.
• Treat expected bad input as data (a receipt with reasons); treat unexpected failures as errors (roll back, log, throw).
• Prove a system with a reconciliation you predict in advance.
• EXECUTE-only roles plus ownership chaining give least-privilege access.

Next: Week 4 begins. Day 22 — Query Execution Plans: generate the large Perf dataset and learn to read estimated and actual plans, operators and logical reads.

References (Microsoft Learn)

• rowversion (Transact-SQL)
• OUTPUT clause (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• TRY…CATCH (Transact-SQL)
• REGEXP_LIKE (Transact-SQL)
• Tutorial: Ownership chains and context switching
• SET IDENTITY_INSERT (Transact-SQL)