Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Post Unanswered: Trigger to update field from rows

    I'm tracking sales reps and the companies they represent.
    I have 2 tables.
    A header table that has the SalesRep (a key field) the name, phone, address, etc.

    A line file that will have the sales rep multiple times, each line having a vendor they represent.

    SalesRepHeader table:
    SalesRep name phone VendorList
    Bob Bob_Smith 111-222-3333
    John John_Young 123-456-56789
    Mary Mary_Kerns 567-876-98765

    SalesRepLines table:
    SalesRep Vendor
    Bob Samsung
    Bob Kenwood
    Bob JVC
    John APPLE
    John HP

    Whenever the SalesRepLine file is added to or updated I want the header file field VendorList to be updated with a concat of the vendors for this sales rep so the SalesRepHeader file would look like this.

    SalesRep name phone VendorList
    Bob Bob_Smith 111-222-3333 Samsung, Kenwood, JVC
    John John_Young 123-456-56789 Apple, HP

    I need the update to be automatic so I'm thinking a trigger would be best.

    Thanks for any help
    Craig

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This would breach database normalisation - you require more than one data point in a cell.

    Also, while you could probably build up a string of vendor names, how are you going to cope with removing one?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, this is not the way to go about doing this.
    First, you need to establish foreign keys between these two tables so that the tables can be joined to determine which vendors are currently associated with which sales reps.
    Second, you should NOT store data in your database as comma-separated lists. If you need this for reporting, then generate these lists when the report is called, either via a sproc or through some functionality of your reporting software.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2013
    Posts
    5
    Here is the code I'm using to create the tables....

    -- Creates Main table for the Sales Rep feature
    if not exists(select 1 from sysobjects where name = 'USER_SLS_REP_HDR' and type = 'U')
    begin
    create table USER_SLS_REP_HDR (
    REP_COD T_COD not null,
    NAM T_NAM null,
    PHONE_1 T_PHONE null,
    ADRS_1 T_ADRS null,
    ADRS_2 T_ADRS null,
    CITY T_CITY null,
    STATE T_STATE null,
    ZIP_COD T_ZIP_COD null,
    EMAIL_1 T_EMAIL_ADRS null,
    LST_MAINT_DT T_DT null,
    LST_MAINT_USR_ID T_USR_ID null,
    LST_LCK_DT T_DT null,
    ROW_TS timestamp null,
    REP_INFO T_NOTE null,
    VEND_LST T_LONG_FILENAME null,
    constraint PK_USER_SLS_REP_HDR primary key (REP_COD)
    )
    end
    go

    -- Create child table to hold vendors for rep
    if not exists(select 1 from sysobjects where name = 'USER_SLS_REP_LIN' and type = 'U')
    begin
    create table USER_SLS_REP_LIN (
    REP_COD T_COD not null,
    VEND_NO T_VEND_NO not null,
    REP_COD_SEQ_NO T_SEQ_NO not null,
    constraint CK_REP_COD_SEQ_NO check(REP_COD_SEQ_NO >= 0),
    LST_MAINT_DT T_DT null,
    LST_MAINT_USR_ID T_USR_ID null,
    ROW_TS timestamp null,
    constraint PK_USER_SLS_REP_LIN primary key (REP_COD, REP_COD_SEQ_NO),
    constraint FK_USER_SLS_REP_HDR foreign key (REP_COD)
    references dbo.USER_SLS_REP_HDR (REP_COD)
    )
    end
    go

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    To add to Blindman's comment: Your approach to tables in SQL is fundamentally wrong. You have more NULLs in ONE table than entire payroll systems. The use of user defined “t_<something>” data types are a really bad idea. In ANSI/ISO SQL we had the CREATE DOMAIN construct which is a data type and load of CHECK() constraints. The point of the CREATE DOMAIN was the CHECK() part; we expected really complicated definitions not simple stuff.

    Let me dissect just your “city t_city NULL” for its bad design:
    1) These names are not ISO-11179; we need “city_<attribute property>” as defined by basic data modeling. Is it a name? Size? Postal code?
    2) We do not affix data element names with meta data. Like “t_” for “user data type”.
    3) You over-abbreviated made names unreadable. When I started FORTRAN I and II allowed only six letters for a name. You also truncated name in weird ways: “zip_cod zip_cod t_zip_cod NULL,” should have been “zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')”
    4) We do not use TIMESTAMP; it an old non-relational Sybase legacy that sucks. We do not timestamp the musical rows of a table; that was file systems, not RDBMS.
    5) There are no “parent” and “child” tables in RDBMS. That was network database. We have referenced and referencing tables. This is where you failed to get the mindset.
    6) Your “_Hdr” is a piece of metadata; HOW it is used, not what it is by its nature. Try “Sales_Representatives”

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I have no clue where in your USER_SLS_REP_LIN you're storing that CSV list of vendors...

    This naming convention looks more typical of old-school Oracle than SQL Server. Did you inherit this database, or was it ported from Oracle, or is your background in Oracle?

    Regardless, your concatenated lists of vendors for each line should be calculated dynamically, not stored.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2013
    Posts
    5
    This is an existing application that I'm adding to. The vendor table is a pre-existing table.

    Here is the trigger I have. I works, but it updates all sales rep records when there is a change to one record. I would prefer to only update the sales rep that is being changed rather than all sales reps.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Trigger code.....?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2013
    Posts
    5
    Sorry, can't you see what I intended to send ;-)

    -- -------------------------------------------------------------------------------------------
    -- create trigger
    create trigger USER_TR_UPDATE_SLS_REP_HDR on USER_SLS_REP_LIN after insert, update
    as
    -- -------------------------------------------------------------------------------------------
    begin -- begin trigger
    -- -------------------------------------------------------------------------------------------

    set nocount on
    set ansi_warnings off

    --
    UPDATE User_Sls_Rep_Hdr
    SET User_Sls_Rep_Hdr.Vend_Lst = (SELECT DISTINCT Vend_No + ', '
    FROM User_Sls_Rep_Lin
    WHERE User_Sls_Rep_Lin.Rep_Cod = User_Sls_Rep_Hdr.Rep_Cod
    FOR xml path (''))

    --

    -- -------------------------------------------------------------------------------------------
    end -- end trigger
    -- -------------------------------------------------------------------------------------------

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't use the FOR XML PATH method myself. It just seems hacking to me (not what it was intended for). So I'm not sure that it will support multi-statement updates (return a separate XML Path concatenated string for each affected User_Sls_Rep_Lin group).

    You'll probably need a recursive CTE to handle this logic, and if you put it in a trigger you'll need to join to the virtual "inserted" table to limit the rows affected. But again, this type of data does NOT belong in a table. This is such a poor idea, that there has even been a discussion among the dbforums moderators as to whether we should assist you with this...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2013
    Posts
    5
    I'm new to this, what method would you use. I was thinking of a computed column but didn't know if a computed column could get it's data from a different table.
    I need the field VEND_LST in the USER_SLS_REP_HDR table to contain all the Vendors this Sales Rep works for. This is needed for lookups in the application I'm customizing. I'm limited in what I can do. But if I can get that column to contain all vendors for that sales rep then lookups in the parent application can search the field.

    I don't want to over load you with details. For the application I'm customizing required any custom tables or trigger to start with USR or USER. There are other requirements as well.

    Thanks for any guidance you can offer.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If your reporting tool can provide this functionality, that is by far the best answer available. Nearly every reporting tool has some method for dealing with this kind of problem, it is very common.

    If the reporting tool can't help with this problem, do you or your DBA know if there is any SQL CLR code running on your database server now? Enabling CLR is not for the faint of heart, but if it is already running there is a relatively clean/simple solution at GROUP_CONCAT string aggregate for SQL Server - Home that would let you build a view to work around most of the parts of this issue. You will probably need a dot net programmer and/or SQL Server expert (which might be one person) to install this option if you are not using the SQL CLR now.

    If the CLR isn't an easy option, then a CTE (Common Table Expression) or a UDF (User Defined Function) is probably your last resort. Blindman is correct, the FOR XML solution you posted will only work on one row at a time. All of the SQL based solutions are kludges to work around the problem, and will probably cause significant performance issues too.

    If the reporting tool offers any solution (and it almost certainly does), that will almost certainly be your best solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    [QUOTE=craig.youngman;6583709]
    I need the field VEND_LST in the USER_SLS_REP_HDR table to contain all the Vendors this Sales Rep works for. This is needed for lookups in the application I'm customizing.[/QUOTE
    Oh dear god no.
    Storing CSV data is bad enough, but using that data for look-up is far far worse.

    You have a table of vendor/rep relationships already. Do your lookups by joining that table to your rep data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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