Professional Web Applications Themes

Have server get value for column on Insert? - Microsoft SQL / MS SQL Server

Consider: CREATE TABLE Transactions ( TransactionGUID uniqueidentifier, TransactionNumber int, TransactionDate, SessionGUID, ...) CREATE TABLE [TransactionEntries] ( TransactionGUID uniqueidentifier NOT NULL , SessionGUID uniqueidentifier NOT NULL , --denormalized column TotalBuying money, TotalSelling money, ...) CREATE CLUSTERED INDEX IX_TransactionEntries_SessionGUIDTransactionGUID ON TransactionEntries (SessionGUID, TransactionGUID) For performance reasons, i will be denormalizing my "TransactionEntries" table with the SessionGUID that normally only sits in the "Transactions" table. Obviously, i could update the application code to add SessionGUID to the TransactionEntries INSERT statement, but it would be so much nicer to be able to get it all done server-side (since it really is a server side ...

  1. #1

    Default Have server get value for column on Insert?

    Consider:

    CREATE TABLE Transactions (
    TransactionGUID uniqueidentifier,
    TransactionNumber int,
    TransactionDate,
    SessionGUID,
    ...)

    CREATE TABLE [TransactionEntries] (
    TransactionGUID uniqueidentifier NOT NULL ,
    SessionGUID uniqueidentifier NOT NULL , --denormalized column
    TotalBuying money,
    TotalSelling money,
    ...)

    CREATE CLUSTERED INDEX IX_TransactionEntries_SessionGUIDTransactionGUID
    ON TransactionEntries (SessionGUID, TransactionGUID)


    For performance reasons, i will be denormalizing my "TransactionEntries"
    table with the SessionGUID that normally only sits in the "Transactions"
    table.

    Obviously, i could update the application code to add SessionGUID to the
    TransactionEntries INSERT statement, but it would be so much nicer to be
    able to get it all done server-side (since it really is a server side thing
    to improve performance - and makes little sense to the client apps).

    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.


    The matching transaction gets inserted before the TransactionEntries, and
    all of it wrapped in a BEGIN/COMMIT TRANSACTION.

    Keeping in mind that SessionGUID is the first part of the TransactionEntries
    table's cluster, can i get the value dynamically from the Transactions
    table?



    Ian Boyd Guest

  2. #2

    Default Re: Have server get value for column on Insert?

    > You can still use your insert trigger, but you need to update the base
    > table, TransactionEntries, in your trigger rather than the inserted table.
    > Join inserted, TransactionEntries, and Transactions in the trigger. The
    > trigger has access to the data that was inserted in the base table.
    This doesn't quite work:
    Cannot insert the value NULL into column 'SessionGUID', table
    'CashierManagementSystem.dbo.TransactionEntries'; column does not allows
    nulls.
    INSERT fails.
    > If you are ever able to update your application code, I would change your
    > dynamic SQL being sent from the application to a procedure that takes the
    > appropriate parameters. Then you could have placed this logic in the proc
    > rather than the trigger.
    i've always heard this, but have never seen a practical implementation.
    Here's most of the
    SQL that goes towards saving a transaction (i couldn't get anymore to show
    you when my
    insert above failed).

    UPDATE Patrons
    SET FirstName='DAN', MiddleInitial='', LastName='HARGROVE', EyeColor='BLUE',
    HairColor='BROWN', Height=76, Weight=332, Gender='M', IdentifyingMarks='',
    FacialHair=0, Nationality='Aboriginal', Birthday='06/30/1967',
    DriversLicenseNumber='332488', DriversLicenseRegion='ONTARIO',
    DriversLicenseCountry='CA', ResidentialAddress1='5622 ODESSA DRIVE',
    ResidentialAddress2='', ResidentialAddress3='', ResidentialCity='TESEH',
    ResidentialRegion='ON', ResidentialZipCode='N8N 2A5',
    ResidentialCountry='CA', CountryOfResidence='', ResidentialPhoneNumber='',
    PositionWithFirm='PRESIDNET', BusinessAddress1='', BusinessAddress2='',
    BusinessAddress3='', BusinessCity='', BusinessRegion='', BusinessZipCode='',
    BusinessPhone='', BusinessName='', BusinessCountry='', EmployerTelephone='',
    AccountType='', MailToCode='', NoMailReasonCode='', PhoneToCode='', IsVIP=0,
    PlayerStatusCode=''
    WHERE PatronGUID = '{3548B5AC-21EB-4162-A4B3-EEA79AC0294A}'

    UPDATE Patrons
    SET PendingUpdates='74Tg',
    ChangedUserGUID='{A5D46AB3-F6A4-468A-A76B-222AD4A7AB63}',
    ChangedDate=getdate(), ChangedWorkstation='OBSIDIAN'
    WHERE PatronGUID = '{3548B5AC-21EB-4162-A4B3-EEA79AC0294A}'

    INSERT INTO Transactions (TransactionGUID, TransactionDate,
    IsReverseTransaction, ReversalGUID, LoginGUID, SessionGUID, MALGUID,
    LCTGUID, LCDGUID, PatronGUID, Firstname, MiddleInitial, Lastname, Address1,
    Address2, Address3, City, Region, PostalCode, Country, CashierGUID,
    SupervisorGUID, IsVIPTransaction, IsPremiumTransaction,
    VIPRateEnabledBySupervisorGUID, PremiumRateAuthorizedBySupervisorGUID,
    TotalBuyAmount, TotalSellAmount, TransactionMode, NormalUSBuyingRate,
    NormalUSSellingRate, VIPUSBuyingRate, VIPUSSellingRate, PremiumRate, Notes,
    IsForceBalanceTransaction, EmployeeOverride)
    VALUES ('{F122AF48-AD1F-4C4E-BBA6-834CDD7420CA}', getdate(), 0, NULL,
    '{1B4E5BFA-0660-4524-88B3-1E90D0C28E58}',
    '{5D845710-554F-4D45-85F8-C9E43498C475}',
    '{2BDF9E11-3340-4792-82EE-9E7647E14148}',
    '{0996506F-1B3C-48DB-9597-3F5727AEB3C6}', NULL,
    '{3548B5AC-21EB-4162-A4B3-EEA79AC0294A}', 'DAN', '', 'HARGROVE', '5622
    ODESSA DRIVE', '', '', 'TESEH', 'ON', 'N8N 2A5', 'CA',
    '{A5D46AB3-F6A4-468A-A76B-222AD4A7AB63}',
    '{31956ED5-A883-4FD3-984C-C2B66DE02001}', 0, 0, NULL, NULL, 13400, 13400,
    'tmOther', 1.34, 1.38, 1.36, 1.36, 1, '', 0, '')

    DELETE FROM TransactionEntries
    WHERE TransactionEntries.TransactionGUID =
    '{F122AF48-AD1F-4C4E-BBA6-834CDD7420CA}'

    INSERT INTO TransactionEntries (TransactionEntryGUID, TransactionGUID,
    CategoryGUID, MoneyGUID, ExchangeRateGUID, CurrencyGUID, Rank, TotalBuying,
    TotalSelling, BuyingRate, SellingRate)
    VALUES ('{93C5432A-FC37-430C-B0C4-FAD5D0EBB2AC}',
    '{F122AF48-AD1F-4C4E-BBA6-834CDD7420CA}',
    '{59343EC5-3563-4308-9C52-8603067C7A8D}',
    '{59343EC5-3563-4308-9C52-8603067C7A8D}', NULL, NULL, 0, 10000, 0, 1.34,
    1.38)


    Is the theory to have stored procedure, with (i'm going to take a guess) 163
    parameters?
    And then the stored procedure does the inserts and updates for me?

    A transaction can have a variable amount of "entries" associated with it.
    What's the methodology to pass a variable number of records to be saved to a
    stored procedure?

    Like i said, i've never seen saving done practically with stored procedures,
    so maybe i just need to understand a few basic tricks. But i can't think fo
    any value in it. SQL was originally designed for data entry people to use
    themselves. Trying to make things work though stored procedures instead of
    just issuing the SQL myself seems like such a headache.


    Maybe with a real-world example?


    > "Ian Boyd" <dan.msnews001avatopia.com> wrote in message
    > news:%231OjKAyPDHA.3020TK2MSFTNGP10.phx.gbl...
    > > Consider:
    > >
    > > CREATE TABLE Transactions (
    > > TransactionGUID uniqueidentifier,
    > > TransactionNumber int,
    > > TransactionDate,
    > > SessionGUID,
    > > ...)
    > >
    > > CREATE TABLE [TransactionEntries] (
    > > TransactionGUID uniqueidentifier NOT NULL ,
    > > SessionGUID uniqueidentifier NOT NULL , --denormalized column
    > > TotalBuying money,
    > > TotalSelling money,
    > > ...)
    > >
    > > CREATE CLUSTERED INDEX IX_TransactionEntries_SessionGUIDTransactionGUID
    > > ON TransactionEntries (SessionGUID, TransactionGUID)
    > >
    > >
    > > For performance reasons, i will be denormalizing my "TransactionEntries"
    > > table with the SessionGUID that normally only sits in the "Transactions"
    > > table.
    > >
    > > Obviously, i could update the application code to add SessionGUID to the
    > > TransactionEntries INSERT statement, but it would be so much nicer to be
    > > able to get it all done server-side (since it really is a server side
    > thing
    > > to improve performance - and makes little sense to the client apps).
    > >
    > > 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.
    > >
    > >
    > > The matching transaction gets inserted before the TransactionEntries,
    and
    > > all of it wrapped in a BEGIN/COMMIT TRANSACTION.
    > >
    > > Keeping in mind that SessionGUID is the first part of the
    > TransactionEntries
    > > table's cluster, can i get the value dynamically from the Transactions
    > > table?
    > >
    > >
    > >
    >
    >

    Ian Boyd Guest

Similar Threads

  1. INSERT file to column
    By HugoSchmidt in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 14th, 05:47 PM
  2. Select after insert to the unique column
    By Bruno Wolff III in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 13th, 04:44 PM
  3. insert a hyperlink column to datgrid
    By TJS in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: March 2nd, 02:59 PM
  4. Index on Computed Column and ADO Insert
    By Peter Crickman in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 01:46 AM
  5. Which is the best column to insert index...
    By fabriZio in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 02:37 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