Riaan Swart

Alex still has some VB6 code lurking in his environment. Like too much VB6 code, it’s littered with On Error Resume Next statements, which allow lazy programmers to simply ignore errors. It’s an easy way to make crash-proof applications in VB6.

One of their database programmers decided to compete with that anti-pattern . This programmer wrote a huge pile of database triggers which looked like this:

CREATE TRIGGER [dbo].[AccountOnInsertOrUpdate]
    ON [dbo].[Account]
    AFTER INSERT,UPDATE
AS
BEGIN
    BEGIN TRY
        SET NOCOUNT ON;

        DECLARE @CurrentUserID NVARCHAR(30) = dbo.getCurrentUserID()
        DECLARE @Now DATETIME = GETDATE()
        -- Update if deleted records exist, otherwise insert.
        DECLARE @Action CHAR = CASE
            WHEN (SELECT COUNT(1) FROM deleted) = 0
            THEN N'i'
            ELSE N'u'
            END

        INSERT INTO audit.Account
            SELECT @CurrentUserID, @Now, @Action, u.*
                FROM (SELECT * FROM inserted
                    EXCEPT SELECT * FROM deleted) AS u
    END TRY
    BEGIN CATCH 
        ROLLBACK TRANSACTION
        DROP TRIGGER dbo.AccountOnInsertOrUpdate
    END CATCH
END

Yes, if the trigger ever throws an error, it simply ceases to exist. Alex checked, and the trigger is still there… for now.

[Advertisement] BuildMaster 4.0 is here! Check out the brand-new UI and see how you can deploy directly from TeamCity (and other CI) to your own servers, the cloud, and more.