Professional Web Applications Themes

Nulls and Defaults in Views with instead of triggers - Microsoft SQL / MS SQL Server

Hi Andrew, I used this solution as well recently. You can get around the restriction by wrapping the columns that are not null and defaut with NULLIF in the view, i.e.: SELECT NULLIF(<not null column with default>, <value that doesn't appear in that column>) You can ofcourse create a CHECK constraint on the column to prevent the value you use in NULLIF from actually appearing in the column. This solution also works with identity columns. You might actually not have to use triggers on the views, depending on the client applications/data access method you use. Some access methods actually get ...

Sponsored Links
  1. #1

    Default Re: Nulls and Defaults in Views with instead of triggers

    Hi Andrew,

    I used this solution as well recently. You can get around the restriction by
    wrapping the columns that are not null and defaut with NULLIF in the view,
    i.e.:

    SELECT NULLIF(<not null column with default>, <value that doesn't appear in
    that column>)
    You can ofcourse create a CHECK constraint on the column to prevent the
    value you use in NULLIF from actually appearing in the column. This solution
    also works with identity columns.

    You might actually not have to use triggers on the views, depending on the
    client applications/data access method you use. Some access methods actually
    get the information about the base tables from the database, bypassing the
    view (something you can prevent by declaring the view with the WITH
    VIEW_METADATA option). For example if you use Enterprise Manager and trace
    it with Profiler you will see that behind the scenes EM just updates the
    base tables behind the views.
    I don't have an overview of which data access methods/applications will
    bypass the views, but if you are going to rely on instead of triggers on
    views, it is a good idea to declare the views with the with view_metadata
    option to prevent applications from trying to update the base tables
    directly.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Andrew John" <com> wrote in message
    news:#phx.gbl... 
    on a view. 
    Access, I have created views that 
    triggers on the views. The code is 
    the right order so foreign keys 
    columns that require the Default to be 
    a lot of columns are null. And 
    the ones that are Not Null in the 
    the constraints instead would 


    Sponsored Links
    Jacco Guest

  2. #2

    Default Re: Nulls and Defaults in Views with instead of triggers

    i do it by preparing an empty table with null-able columns and then use
    UNION ALL to hint the db engine i need those nulls. e.g.

    CREATE TABLE MyTable_Current (
    Col1 INT PRIMARY KEY
    , Col2 INT NOT NULL
    )
    GO
    CREATE TABLE MyTable_Nulls (
    Col1 INT NULL
    , Col2 INT NULL
    )
    GO
    CREATE VIEW v_MyTable
    AS
    SELECT * FROM MyTable_Current -- never use * in production code :-)))
    UNION ALL
    SELECT * FROM MyTable_Nulls
    GO

    CREATE TRIGGER trg_v_MyTable_Insert
    ON v_MyTable
    INSTEAD OF INSERT
    AS
    INSERT MyTable_Current (Col1, Col2)
    SELECT COALESCE(Col1, <col1_default>)
    , COALESCE(Col2, <col2_default)
    FROM inserted
    GO
    [code for the update trigger snipped]

    (not tested)

    btw, if you add MyTable_History and put some CHECK constraints (on a
    partitioning column) you might get some nice local partitioned views.

    HTH,
    </wqw>

    "Andrew John" <com> wrote in message
    news:#phx.gbl... 
    on a view. 
    Access, I have created views that 
    triggers on the views. The code is 
    the right order so foreign keys 
    columns that require the Default to be 
    a lot of columns are null. And 
    the ones that are Not Null in the 
    the constraints instead would 


    Vlad Guest

  3. #3

    Default Re: Nulls and Defaults in Views with instead of triggers

    Thank you both. I will give them a try.


    Andrew Guest

Similar Threads

  1. NULLS first ...
    By Ralph in forum MySQL
    Replies: 4
    Last Post: November 7th, 02:00 AM
  2. Replies: 1
    Last Post: September 27th, 01:51 PM
  3. Replies: 4
    Last Post: July 8th, 07:00 AM
  4. dealing with nulls....
    By Ken Schaefer in forum ASP
    Replies: 3
    Last Post: July 7th, 05:49 AM
  5. ADO.NET with Instead-Of-Insert Triggers, sets NULLs
    By Brian Cook in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 3rd, 05:07 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139