CREATE TRIGGER dbo.trg_Contracts_insert ON dbo.Contracts INSTEAD OF INSERT AS
BEGIN
    DECLARE @Id INT, @ContractNumber VARCHAR(7), @NumberOfAttempts INT, @AdditionalAttempts INT, @GuessLength INT, @SmallestGuess INT,
        @LargestGuess INT, @StartDateTime DATETIME, @EndDateTime DATETIME, @VariableOdds BIT, @PercentOfAttempts FLOAT, @RNGOnly BIT, @HasPrimaryPrize BIT,
        @HasSecondaryPrize BIT, @HasDefaultPrize BIT, @HasDemographics BIT, @AttemptsTaken INT

    OPEN SYMMETRIC key pv_encrypt_prod decryption by certificate cert_pv;

    DECLARE ContractsCursor CURSOR FOR
        SELECT Id, ContractNumber, NumberOfAttempts, AdditionalAttempts, GuessLength, SmallestGuess, LargestGuess, StartDateTime, EndDateTime,
            VariableOdds, PercentOfAttempts, RNGOnly, HasPrimaryPrize, HasSecondaryPrize, HasDefaultPrize, HasDemographics, AttemptsTaken
        FROM INSERTED;

    OPEN ContractsCursor

    FETCH NEXT FROM ContractsCursor INTO @Id, @ContractNumber, @NumberOfAttempts, @AdditionalAttempts, @GuessLength, @SmallestGuess, @LargestGuess,
        @StartDateTime, @EndDateTime, @VariableOdds, @PercentOfAttempts,  @RNGOnly, @HasPrimaryPrize, @HasSecondaryPrize, @HasDefaultPrize, @HasDemographics, @AttemptsTaken

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@Id IS NULL)
        BEGIN
            INSERT INTO Contracts_base (ContractNumber, NumberOfAttempts, AdditionalAttempts, GuessLength, SmallestGuess, LargestGuess,
                StartDateTime, EndDateTime, VariableOdds, PercentOfAttempts, RNGOnly, HasPrimaryPrize, HasSecondaryPrize, HasDefaultPrize, HasDemographics, AttemptsTaken)
            VALUES ( @ContractNumber, @NumberOfAttempts, @AdditionalAttempts, @GuessLength, @SmallestGuess, @LargestGuess,
                @StartDateTime, @EndDateTime, @VariableOdds, @PercentOfAttempts, @RNGOnly, @HasPrimaryPrize, @HasSecondaryPrize, @HasDefaultPrize, @HasDemographics, @AttemptsTaken)

            SET @Id = scope_identity()

            UPDATE Contracts_base
                SET NumberOfAttempts = ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @NumberOfAttempts), 1, CONVERT(VARCHAR(MAX), @Id)),
                    AdditionalAttempts = ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @AdditionalAttempts), 1, CONVERT(VARCHAR(MAX), @Id)),
                    GuessLength = ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @GuessLength), 1, CONVERT(VARCHAR(MAX), @Id)),
                    SmallestGuess = ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @SmallestGuess), 1, CONVERT(VARCHAR(MAX), @Id)),
                    LargestGuess = ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @LargestGuess), 1, CONVERT(VARCHAR(MAX), @Id))
            WHERE Id = @Id

            SELECT Id FROM Contracts_base WHERE @@ROWCOUNT > 0 AND Id = @Id
        END
        ELSE
        BEGIN
            SET NOCOUNT ON
            SET IDENTITY_INSERT Contracts_base ON
            INSERT INTO Contracts_base (Id, ContractNumber, NumberOfAttempts, AdditionalAttempts, GuessLength, SmallestGuess, LargestGuess,
                StartDateTime, EndDateTime, VariableOdds, PercentOfAttempts, RNGOnly, HasPrimaryPrize, HasSecondaryPrize, HasDefaultPrize, HasDemographics, AttemptsTaken)
            VALUES (@Id, @ContractNumber,
                ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @NumberOfAttempts), 1, Convert(varchar(max), @Id)),
                ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @AdditionalAttempts), 1, Convert(varchar(max), @Id)),
                ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @GuessLength), 1, Convert(varchar(max), @Id)),
                ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @SmallestGuess), 1, Convert(varchar(max), @Id)),
                ENCRYPTBYKEY(key_guid('pv_encrypt_prod'), CONVERT(VARCHAR(MAX), @LargestGuess), 1, Convert(varchar(max), @Id)),
                @StartDateTime, @EndDateTime, @VariableOdds, @PercentOfAttempts, @RNGOnly, @HasPrimaryPrize, @HasSecondaryPrize, @HasDefaultPrize, @HasDemographics,
                @AttemptsTaken)

            SET IDENTITY_INSERT Contracts_base OFF
        END

        FETCH NEXT FROM ContractsCursor INTO @Id, @ContractNumber, @NumberOfAttempts, @AdditionalAttempts, @GuessLength, @SmallestGuess, @StartDateTime, @EndDateTime,
            @LargestGuess, @VariableOdds, @PercentOfAttempts, @RNGOnly, @HasPrimaryPrize, @HasSecondaryPrize, @HasDefaultPrize, @HasDemographics, @AttemptsTaken

    END

    CLOSE ContractsCursor
    DEALLOCATE ContractsCursor

    CLOSE SYMMETRIC key pv_encrypt_prod
END