Professional Web Applications Themes

Have trigger supply value for NOT NULL column on insert - Microsoft SQL / MS SQL Server

i want a FOR INSERT trigger to supply the value for a NOT NULL column, so that the client app doesn't have to. Possible?...

Sponsored Links
  1. #1

    Default Have trigger supply value for NOT NULL column on insert

    i want a FOR INSERT trigger to supply the value for a NOT NULL column, so
    that the client app doesn't have to.

    Possible?


    Sponsored Links
    Ian Boyd Guest

  2. #2

    Default Re: Have trigger supply value for NOT NULL column on insert

    you can not use triggers because constraints are checked before triggers are
    fired and your statement will result in an error if you are trying to insert
    null values into not null column.

    --
    -Vishal
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:uz$eaDXQDHA.3088TK2MSFTNGP10.phx.gbl...
    > you can use default .
    > Ex:
    > create table #t1 (idd int, dt datetime not null default getdate())
    >
    > insert into #t1 (idd) values(1)
    >
    > select * from #t1
    >
    > --
    > -Vishal
    > "Ian Boyd" <dan.msnews001avatopia.com> wrote in message
    > news:O2hhLAXQDHA.2316TK2MSFTNGP12.phx.gbl...
    > > i want a FOR INSERT trigger to supply the value for a NOT NULL column,
    so
    > > that the client app doesn't have to.
    > >
    > > Possible?
    > >
    > >
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Have trigger supply value for NOT NULL column on insert

    default can be of the following you can not use select statement.

    Constant
    Built-in function
    Mathematical expression

    as i 've told you constraints are checked before triggers are fired, you can
    not use triggers .May be you've to drop the constraint of not null or while
    inserting value itself pick up the value from Transactions table and insert
    the row with the correct value rather than firing update through triggers.

    --
    -Vishal
    "Ian Boyd" <dan.msnews001avatopia.com> wrote in message
    news:eeRQuGXQDHA.2212TK2MSFTNGP12.phx.gbl...
    > Consider:
    >
    > CREATE TABLE Transactions (
    > TransactionGUID uniqueidentifier,
    > SessionGUID,
    > ...)
    >
    > CREATE TABLE [TransactionEntries] (
    > TransactionGUID uniqueidentifier NOT NULL ,
    > SessionGUID uniqueidentifier NOT NULL ,
    > ...)
    >
    > i've tried setting the SessionGUID column's "default" value to:
    > SELECT SessionGUID FROM Transactions t WHERE t.TransactionGUID =
    > TransactionGUID
    >
    > but that isn't valid.
    >
    > i've tried setting the SessionGUID column's "formula" to the same thing -
    > again it's invalid syntax.
    >
    > i've tried creating a trigger:
    > CREATE TRIGGER DenormalizeTransactionEntriesWithSessionGUID on
    > [dbo].[TransactionEntries]
    > FROR INSERT
    > AS
    > UPDATE inserted
    > SET SessionGUID = t.SessionGUID
    > FROM inserted
    > INNER JOIN Transactions t
    > ON inserted.TransactionGUID = t.TransactionGUID
    >
    > But you cannot update the "inserted" table.
    >
    > Since SessionGUID is NOT NULL, SQL Server complains whenever the user
    didn't
    > specify a value - which is fine because i want the server to supply it.
    >
    >
    >
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:uz$eaDXQDHA.3088TK2MSFTNGP10.phx.gbl...
    > > you can use default .
    > > Ex:
    > > create table #t1 (idd int, dt datetime not null default getdate())
    > >
    > > insert into #t1 (idd) values(1)
    > >
    > > select * from #t1
    > >
    > > --
    > > -Vishal
    > > "Ian Boyd" <dan.msnews001avatopia.com> wrote in message
    > > news:O2hhLAXQDHA.2316TK2MSFTNGP12.phx.gbl...
    > > > i want a FOR INSERT trigger to supply the value for a NOT NULL column,
    > so
    > > > that the client app doesn't have to.
    > > >
    > > > Possible?
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

Similar Threads

  1. Allowing update of column only from trigger
    By Shawn Harrison in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 31st, 06:59 PM
  2. Before update or delete trigger to insert ?
    By Cindy Gold in forum IBM DB2
    Replies: 2
    Last Post: August 12th, 01:38 PM
  3. How to ensure if column A is null, column B has to be null
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:09 PM
  4. Trigger - wont allow me to insert
    By pat in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 3rd, 05:51 PM
  5. Trigger: Insert if not exists?
    By FC in forum Oracle Server
    Replies: 1
    Last Post: December 10th, 08:40 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