Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Unanswered: INSTEAD OF trigger - error inserting into NOT NULL field

    I've just noticed some strange behavior that seems like a bug to me.
    It's much easier to follow an example of it that to outright explain it, so here goes.

    I have a table defined with a NOT NULL constraint on a column and a default clause:
    -- DROP TABLE TestTable
    CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )

    I have a view defined on the table, in this example case, the view just mirrors the table one to one:
    -- DROP VIEW TestView
    CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable

    So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field:
    INSERT INTO TestView (TestField0) SELECT 'test'

    Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt:
    -- DROP TRIGGER TestTrigger
    CREATE TRIGGER TestTrigger ON TestView
    INSERT INTO TestTable (TestField0, TestField1)
    SELECT TestField0, COALESCE(TestField1, 'X')
    FROM inserted

    Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error:
    INSERT INTO TestView (TestField0) SELECT 'test'

    Server: Msg 233, Level 16, State 2, Line 1
    The column 'TestField1' in table 'TestView' cannot be null.

    Am I missing something or is this a bug?

  2. #2
    Join Date
    Aug 2003
    I have found the answer. The docs state that this is the defined behavior, although I don't think it is good or desirable behavior.

    from Books Online:
    If a NOT NULL column with a DEFAULT definition is referenced by a simple expression in a view that has an INSTEAD OF INSERT trigger, however, any INSERT statement referencing the view must supply a value for the column. This value is required to build the inserted table passed to the trigger. A convention is required for a value that signals to the trigger that the default value should be used. The best convention is for the INSERT statement to supply the default value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts