Trigger the ORM

I’m not that a fan of ORM. Surely because I also don’t have to program with OOP language (C#, Java…). From the experience so far, it has mostly been a fight more than a cooperation. To be honest it may play well with the database but the software design decision are trickling down to the database.

Our 2 main systems were build on NHibernate with some less than optimal properties. The shared data access layer rely on the existence of deprecated tables or columns. If I try to remove/clean it, it breaks. If I try to hide it behind and updatable view or a synonym, it breaks. And I should not even consider stored procedure. But the show must go on so let’s deal with it.

We had an issue in some reports because of NULLs and bad values where we would have expected a yes/no status. ‘no’ being the default. The base table looks something like:

   create table dm_temp.test(col_not_null char(1), infos varchar(10)); -- can be null
    insert into dm_temp.test(col_not_null, infos)
        values (NULL, 'was NULL'), ('y', 'ok'), ('n', 'ok'), ('w', 'bad_value'); -- insert with unexpected NULL and 'w'

Assumption

We could have gone with a …ISNULL/COALESCE + CASE WHEN… hell but better was to treat our problems at the source to reflect the domain. The column get a nice … NOT NULL DEFAULT (‘no’). Let’s correct unexpected values, define the Foreign Keys (a CHECK was not appropriate here), and set all the required constraints:

    -- code table --
    create table dm_temp.tcod (cod char(1) NOT NULL PRIMARY KEY CLUSTERED);
        insert into dm_temp.tcod values ('y'), ('n');

    -- clean the place --
    update dm_temp.test set col_not_null = 'n' where col_not_null not in ('y', 'n') or col_not_null is null;

    -- set integrity --
    alter table dm_temp.test
        alter column col_not_null char(1) NOT NULL;
    alter table dm_temp.test
        add constraint def_val Default ('n') for col_not_null;
    alter table dm_temp.test
        add constraint fk_val FOREIGN KEY (col_not_null) REFERENCES dm_temp.tcod(cod);

Now we test. The application A is okay but with the app B … freeeeeze. After checking the logs, I see the ORM generated SQL statement is trying to insert NULLs in the NOT NULL column.

# Nhibernate
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'col_not_null', table 'fred_dev.dm_temp.test'; column does not allow nulls. INSERT fails.

# SQL Server
Msg 515, Level 16, State 2, Line 39
 Cannot insert the value NULL into column 'col_not_null', table 'fred_dev.dm_temp.test'; column does not allow nulls. INSERT fails.

The present column is irrelevant for application B, but part of a shared access layer. Here NHibernate is not setup to ignore columns where possible and tries to insert NULL. On top of that I naively considered NULL would be changed to the DEFAULT…. ok 2 lessons learned the hard way here.

Echo on the row

How to deal with it? The next tool on the list is… the dreaded INSTEAD OF trigger. To stay fit, I go to back to some recommendations here. The insert is slightly modified with a … COALESCE (col_not_null, ‘n’) … to ensure that the default is taken:

    CREATE TRIGGER dm_temp.bypass_orm_at_insert ON dm_temp.test
    INSTEAD OF INSERT AS BEGIN
        insert into dm_temp.test(col_not_null, infos)
            select coalesce(col_not_null, 'n'), infos
            from inserted;
    END

A test INSERT with NULL values goes fine. Back to test application A. ERROR… come on! App A was working. App B… still freezing. The debug throw me a nice

Error saving viewmodel. NHibernate.AdoNet.TooManyRowsAffectedException: Unexpected row count: 2; expected: 1
    bei NHibernate.AdoNet.Expectations.BasicExpectation.VerifyOutcomeNonBatched(Int32 rowCount, DbCommand statement)

What 2 rows?! I’m pretty sure I only inserted one row. I check the the Apps and the generated SQL statement. Definitely 1. Check the message out of a simple SQL INSERT. And …

1 + 1 = 2. Failing assumption again: NHibernate does not work on @@ROWCOUNT … but on message interpretation? Let’s revisit our trigger with a SET NOCOUNT sandwich:

CREATE TRIGGER dm_temp.bypass_orm_at_insert_v2 ON dm_temp.test
    INSTEAD OF INSERT AS BEGIN
        set nocount on;
        insert into dm_temp.test(col_not_null, infos)
            select coalesce(col_not_null, 'n'), infos
            from inserted;
        print 'v2 is fine !';
        set nocount off;

    END;

And now we’re finally done. App A is happy, App B is happy and Fred learned something valuable without inserting bugs in PROD by playing in a local DEV only environment.

Let’s review that.

Look for alternative

The current solution is only relevant in a given context, a local optimum. Neither the ORM settings nor the applications on top of it could have easily been changed.

Do not rush for the trigger, check that either the application can be updated or the ORM parameters changed. Triggers raise new issues and challenges. First on how the application may treat the DML: 1+1 = ERROR…. Then on long term, we have a non trivial insert relying on what is the current DEFAULT hard-coded in it.

Finally, do not just test the database but also the applications build on top and preferably in a fully isolated DEV environment! We did the extra miles to set it and it was just worth it. It may be trivial, but it does not hurt to stress it again.

…

But wait, what about the INSTEAD OF UPDATE part … well … this one is left as the exercise.

Good luck!

---

-- Playground script --

    drop table if exists dm_temp.test;
    drop table if exists dm_temp.tcod;

/* Step 0: Legacy with null allowed ======== */

    create table dm_temp.test(col_not_null char(1), infos varchar(10)); -- can be null
    insert into dm_temp.test(col_not_null, infos)
        values (NULL, 'was NULL'), ('y', 'ok'), ('n', 'ok'), ('w', 'bad_value'); -- insert with unexpected NULL and 'w'
    select col_not_null, infos from dm_temp.test;
    GO
 

/* Step 2: Not null with default ==== */
    -- Integrity --
    create table dm_temp.tcod (cod char(1) NOT NULL PRIMARY KEY CLUSTERED);
        insert into dm_temp.tcod values ('y'), ('n');
    -- clean the place --
    update dm_temp.test set col_not_null = 'n' where col_not_null not in ('y', 'n') or col_not_null is null;
    alter table dm_temp.test
        alter column col_not_null char(1) NOT NULL;
    alter table dm_temp.test
        add constraint def_val Default ('n') for col_not_null;
    alter table dm_temp.test
        add constraint fk_val FOREIGN KEY (col_not_null) REFERENCES dm_temp.tcod(cod);

    select col_not_null, infos from dm_temp.test;

 

    -- check default --
    insert into dm_temp.test(infos) values ('new def');
    begin try
        begin transaction
        insert into dm_temp.test(col_not_null,infos) values ('w','blocked');
        if @@TRANCOUNT > 0 BEGIN commit; END
    end try
    begin catch
        if @@TRANCOUNT > 0 BEGIN select 'saved!'; rollback; END
    end catch

    select * from dm_temp.test;
    GO


/* NULL in NOT NULL */

    insert into dm_temp.test values (NULL, 'trouble');
    GO

/* TRIGGER v1 */

    CREATE TRIGGER dm_temp.bypass_orm_at_insert ON dm_temp.test
    INSTEAD OF INSERT AS BEGIN
        insert into dm_temp.test(col_not_null, infos)
            select coalesce(col_not_null, 'n'), infos
            from inserted;
    END

    -- Second try --
    insert into dm_temp.test(col_not_null, infos) values (NULL, 'no troubl?');

    print 'nrow ' + cast(@@ROWCOUNT as varchar(3)); -- tsql is fine and only get 1 row
 

/*
...
ISSUE WITH THE APP  ??????????
...
*/
 

/* Trigger v2 */

    DROP TRIGGER dm_temp.bypass_orm_at_insert;
    GO

    CREATE TRIGGER dm_temp.bypass_orm_at_insert_v2 ON dm_temp.test
    INSTEAD OF INSERT AS BEGIN
        set nocount on;
        insert into dm_temp.test(col_not_null, infos)
            select coalesce(col_not_null, 'n'), infos
            from inserted
        ;

        print 'v2 is fine !';
        set nocount off;
    END;
    GO

    insert into dm_temp.test(col_not_null, infos) values (NULL, 'trig v2');