Difficult Temporal Cross-Table Database Constraint

Question

I have a particularly difficult business constraint that I would like to enforce at the database level. The data is financial in nature, and so must be protected from inconsistencies to the nth degree – no trusting the business layer with this stuff. I use the word "temporal" somewhat loosely, meaning that I intend to control how an entity can and cannot change over time.

Glossing over the details, here's the design:

• An invoice can feature several fees.
• Fees are assigned to an invoice shortly after creation of the invoice.
• The invoice reaches a stage in the process after which it is "locked."
• From this point on, no fee may be added to or removed from this invoice.

Here's a stripped down data definition:

CREATE TABLE Invoices
(
    InvoiceID INT IDENTITY(1,1) PRIMARY KEY,
)

CREATE TABLE Fees
(
    FeeID INT IDENTITY(1,1) PRIMARY KEY,
    InvoiceID INT REFERENCES Invoices(InvoiceID),
    Amount MONEY
)

You'll notice that the "lockable" nature of the invoice isn't represented here; how to represent it – and whether it needs to be directly represented at all – is still an open question.

I have come to believe that this is one of those arrangements that cannot be translated into domain-key normal form, though I may be wrong. (There really is no way to tell, after all.) That said, I still hold out hope for a highly-normalized solution.

I happen to be implementing this on SQL Server 2008 (the syntax may have been a hint), but I'm a curious guy, so if there are solutions that work on other DBMS's, I'd love to hear about those as well.

Answer 1

I cannot think of a way to do this with normalization. However, if I wanted to constrain this on the database, I'd do it 1 of two ways:

first, I would add a 'locked' column to Invoices, which is a bit of some sort, just a way to key it as locked.

Then, the two ways:

1. A "before insert" trigger, which would throw an error before an insert is made, if the invoice referred to is locked.
2. Do this logic in the stored procedure that creates the fee.

EDIT: I couldn't find a good MSDN article on how to do one of these, but IBM has one that works pretty well in SQL Server: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafybeforesql.htm

Answer 2

You can't just by using FK constraints and the like -- at least not in any way that makes much sense. I would suggest using an INSTEAD OF trigger in SQL Server to enforce this constraint. It should be fairly easy to write and pretty straightforward.

Answer 3

You can constrain additions to the FEES table by altering your data model to use:

INVOICES

• INVOICE_ID
• INVOICE_LOCKED_DATE, null

FEES

• FEE_ID (pk)
• INVOICE_ID (pk, fk INVOICES.INVOICE_ID)
• INVOICE_LOCKED_DATE (pk, fk INVOICES.INVOICE_LOCKED_DATE)
• AMOUNT

At a glance, it's redundant but as long as an INSERT statement for the FEES table doesn't include a lookup to the INVOICES table for the locked date (default being null) - it ensures that new records have the date the invoice was locked.

Another option is to have two tables regarding fee handling - PRELIMINARY_FEES and CONFIRMED_FEES.

While an invoices fees are still editable, they reside in the PRELIMINIARY_FEES table and once confirmed - are moved to the CONFIRMED_FEES. I don't like this one much for sake of having to maintain two identical tables along with query implications, but it would allow for using GRANTs (on a role, not user, basis) to only allow SELECT access to CONFIRMED_FEES while allowing INSERT, UPDATE, DELETE on the PRELIMINARY_FEES table. You can't restrict grants in a single FEES table setup because a grant isn't data aware - you can't check for a given status.

Answer 4

I think you will be best off explicitly storing the 'locked/unlocked' state for the invoice in the invoice table, and then apply triggers on INSERT and DELETE (and UPDATE, though you don't actually say you want the fees on the invoice frozen) to prevent modifications if the invoice is in the locked state.

The locked flag is necessary unless there is a reliable algorithmic method to determine when the invoice is locked - perhaps 2 hours after it is produced. Of course, you have to update the invoice row to lock it - so an algorithmic method is better (fewer updates).

Answer 5

Why not just have a 'Locked' column that's a boolean (or single char, 'y', 'n' for example) and tweak your update query to use a subquery :

INSERT INTO Fees (InvoiceID, Amount) VALUES ((SELECT InvoiceID FROM Invoices WHERE InvoiceID = 3 AND NOT Locked), 13.37);

Assuming you have a not-null constraint on the InvoiceID column, the insertion will fail when the invoice is locked. You can handle the exception in your code and thus prevent fee additions when the invoice is locked. You'll also avoid having to write and maintain complicated triggers and stored procedures as well.

PS. The insertion query above uses MySQL syntax, I'm afraid I'm not that familiar with SQL Server's TQL variant.

Answer 6

Don't complicate it, I'd go with triggers. There is no shame in using them, this is what they are there for.

To avoid lots of logic in the triggers, I add an "Editable" bit column into the header table, then basically use a divide with Editable to either work or cause a divide by zero error, which I CATCH and convert to a Invoice is not editable, no changes permitted message. There are no EXISTS used to eliminate extra overhead. Try this:

CREATE TABLE testInvoices
(
     InvoiceID   INT      not null  IDENTITY(1,1) PRIMARY KEY
    ,Editable    bit      not null  default (1)  --1=can edit, 0=can not edit
    ,yourData    char(2)  not null  default ('xx')
)
go

CREATE TABLE TestFees
(
    FeeID     INT IDENTITY(1,1) PRIMARY KEY
   ,InvoiceID INT REFERENCES testInvoices(InvoiceID)
   ,Amount    MONEY
)
go

CREATE TRIGGER trigger_testInvoices_instead_update
ON testInvoices
INSTEAD OF UPDATE
AS
BEGIN TRY
    --cause failure on updates when the invoice is not editable
    UPDATE t 
        SET Editable =i.Editable
           ,yourData =i.yourData
        FROM testInvoices            t
            INNER JOIN INSERTED      i ON t.InvoiceID=i.InvoiceID
        WHERE 1=CONVERT(int,t.Editable)/t.Editable    --div by zero when not editable
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO


CREATE TRIGGER trigger_testInvoices_instead_delete
ON testInvoices
INSTEAD OF DELETE
AS
BEGIN TRY
    --cause failure on deletes when the invoice is not editable
    DELETE t
    FROM testInvoices            t
        INNER JOIN DELETED       d ON t.InvoiceID=d.InvoiceID
        WHERE 1=CONVERT(int,t.Editable)/t.Editable    --div by zero when not editable
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_TestFees_instead_insert
ON TestFees
INSTEAD OF INSERT
AS
BEGIN TRY
    --cause failure on inserts when the invoice is not editable
    INSERT INTO TestFees
            (InvoiceID,Amount)
        SELECT
            f.InvoiceID,f.Amount/i.Editable  --div by zero when invoice is not editable
            FROM INSERTED                f
                INNER JOIN testInvoices  i ON f.InvoiceID=i.invoiceID
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_TestFees_instead_update
ON TestFees
INSTEAD OF UPDATE
AS
BEGIN TRY
    --cause failure on updates when the invoice is not editable
    UPDATE f 
        SET InvoiceID =ff.InvoiceID
           ,Amount    =ff.Amount/i.Editable --div by zero when invoice is not editable
        FROM TestFees                f
            INNER JOIN INSERTED     ff ON f.FeeID=ff.FeeID
            INNER JOIN testInvoices  i ON f.InvoiceID=i.invoiceID
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_TestFees_instead_delete
ON TestFees
INSTEAD OF DELETE
AS
BEGIN TRY
    --cause failure on deletes when the invoice is not editable
    DELETE f
    FROM TestFees                f
        INNER JOIN DELETED      ff ON f.FeeID=ff.FeeID
        INNER JOIN testInvoices  i ON f.InvoiceID=i.invoiceID AND 1=CONVERT(int,i.Editable)/i.Editable --div by zero when invoice is not editable
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

here is a simple test script to test out the different combinations:

INSERT INTO testInvoices VALUES(default,default) --works
INSERT INTO testInvoices VALUES(default,default) --works
INSERT INTO testInvoices VALUES(default,default) --works

INSERT INTO TestFees (InvoiceID,Amount) VALUES (1,111)  --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (1,1111) --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (2,22)   --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (2,222)  --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (2,2222) --works

update testInvoices set Editable=0 where invoiceid=3 --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (3,333) --error<<<<<<<

UPDATE TestFees SET Amount=1 where feeID=1 --works
UPDATE testInvoices set Editable=0 where invoiceid=1 --works
UPDATE TestFees SET Amount=11111 where feeID=1 --error<<<<<<<
UPDATE testInvoices set Editable=1 where invoiceid=1 --error<<<<<<<

UPDATE testInvoices set Editable=0 where invoiceid=2 --works
DELETE TestFees WHERE invoiceid=2 --error<<<<<

DELETE FROM testInvoices where invoiceid=2 --error<<<<<

UPDATE testInvoices SET Editable='A' where invoiceid=1 --error<<<<<<< Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'A' to data type bit.

Answer 7

I agree with the general consensus that a lock bit should be added to the Invoices table to indicate whether fees may be added. It is then necessary to add TSQL code to enforce the business rules related to locked invoices. Your original post doesn't seem to include specifics about the conditions under which an invoice becomes locked, but it is reasonable to assume that the lock bit can be set appropriately (this aspect of the problem could become complicated, but let's settle that in another thread).

Given this consensus, there are 2 implementation choices that will effectively enforce the business rule in the data tier: triggers and standard stored procedures. To use standard stored procedures, one would of course DENY UPDATES, DELETES, AND INSERTS for the Invoices and Fees tables and require that all data modification be done using stored procedures.

The advantage of using triggers is that application client code can be simplified because the tables can be accessed directly. This could be an important advantage if you are using LINQ to SQL, for example.

I can see a couple of advantages to using stored procedures. For one thing, I think using a stored procedure layer is more straightforward and hence more understandable to maintenance programmers. They, or you several years from now, may not remember that clever trigger you created, but a stored procedure layer is unmistakable. On a related point, I would argue that there is a danger of accidentally dropping a trigger; it is less likely that someone would accidentally change the permissions on these tables to make them directly writable. While either scenario is possible, if there's a lot riding on this I would opt for the stored procedure option for safety's sake.

It should be noted that this discussion is not database agnostic: we're discussing SQL Server implementation options. We could use a similar approach with Oracle or any other server that provides procedural support for SQL, but this business rule can't be enforced using static constraints, nor can it be enforced in a database neutral manner.