SQL Scripts

find_first_available_customernumber.sql

SELECT MIN(CustomerNumber + 1)
FROM Customer
WHERE NOT EXISTS (SELECT * FROM Customer WHERE CustomerNumber = MIN(CustomerNumber) + 1)

set_costumer_number.sql

-- This trigger will be executed after the insert statement. 
-- It will retrieve the maximum CustomerNumber value from the table and store it in a variable @max. 
-- It will then open a cursor that loops through the rows in the inserted virtual table where the CustomerNumber is NULL. 
-- For each row, it will update the CustomerNumber value in the Customers table to @max and increment @max by 1. 
-- This will ensure that each inserted row is assigned a unique CustomerNumber value.
-- Note that this trigger assumes that the CustomerNumber column is not an auto-incrementing column and that it allows NULL values. 
-- If the CustomerNumber column is an auto-incrementing column or if it does not allow NULL values, you will need to adjust the trigger accordingly.

CREATE TRIGGER set_customer_number
ON Customers
AFTER INSERT
AS
BEGIN
    DECLARE @max INT;

    SELECT @max = COALESCE(MAX(CustomerNumber), 0) + 1
    FROM Customers;

    DECLARE @id INT, @customer_number INT;
    DECLARE cursor_customers CURSOR FOR
        SELECT ID, CustomerNumber FROM inserted WHERE CustomerNumber IS NULL;

    OPEN cursor_customers;
    FETCH NEXT FROM cursor_customers INTO @id, @customer_number;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Customers
        SET CustomerNumber = @max
        WHERE ID = @id;

        SET @max = @max + 1;
        FETCH NEXT FROM cursor_customers INTO @id, @customer_number;
    END;

    CLOSE cursor_customers;
    DEALLOCATE cursor_customers;
END;