New exception handling technique!!!
Let me preface this with RANT!!! (just a little frustrated...)
I've heard arguments against structured exception handling in my day, but never any as convincing as what I encountered in SQL Server Management Studio 2005 recently. [insert sarcasm here]
Why bother with all that overhead and extra code when all you really need to do is monitor output messages?
For example, say you wanted to know if a certain UPDATE statement violated a constraint? How could you use this exciting, new technique???
First we need a problem:
CREATE TABLE EncryptedSecret(
ID varchar(3) NOT NULL,
Text varbinary(512) NOT NULL,
CONSTRAINT [PK_EncryptedSecret] PRIMARY KEY CLUSTERED (ID ASC)
)
CREATE VIEW Secret AS
SELECT ID, CAST(DecryptByPassPhrase(N'pwd', Text) AS VARCHAR(255)) AS Text
FROM EncryptedSecret
CREATE TRIGGER Secret_Update ON Secret
INSTEAD OF UPDATE AS
BEGIN
UPDATE EncryptedSecret
SET Text = EncryptByPassphrase('pwd', CAST(I.Text as nvarchar(255)))
FROM EncryptedSecret E INNER JOIN inserted I ON I.ID = E.ID;
END
INSERT INTO EncryptedSecret (ID, Text) Values('MSG', EncryptByPassphrase('pwd', 'Hello Kitty!'));
"Hello Kitty"??? Any self-respecting developer knows the proper test is "Hello World!"…
So, if someone tries to fix the problem in the UI, how can we ensure the key isn't violated? By monitoring the return messages, of course! If the key hasn't been violated then we should get exactly one "(1 row(s) affected)" message, otherwise, we can pop the following modal dialogue:
"No row was updated.
The data in row 1 was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
Correct the errors and retry or press ESC to cancel the change(s)."
Perfect!
Wait a minute. Unless the trigger includes SET NOCOUNT ON the system will "count" the "update" to the view AND the update to the base table…
Oh well, let them eat cake!