Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    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
    INSTEAD OF INSERT AS
    BEGIN
    INSERT INTO TestTable (TestField0, TestField1)
    SELECT TestField0, COALESCE(TestField1, 'X')
    FROM inserted
    END

    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?
    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    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
  •