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:
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.
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.