Results 1 to 14 of 14

Thread: Trigger help..

  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Trigger help..

    Hi, I'm new to the forum and to triggers so I may ask stupid questions!!

    My probem is that I want to create an Insert trigger that will populate a table with multiple rows. Let me explain. We have a System where to enter a new salesperson, you select a branch. This then gives you the option to add a SalespersonCode and a Salespersonname. Now we have multiple branches so, I also want to insert the salespersoncode and salespersonname for each of the other branches. I've experimented with raw SQL and came up with this that works (with 'CS' being the inserted branch and 'TTT' and 'DMCTEST' being the SalespersonCode and Salespersonname respectively):

    INSERT INTO SalSalesperson(Branch, Salesperson, Name)
    SELECT Distinct Branch,'TTT','DMCTEST'
    FROM SalSalesperson
    where (Branch <> 'CS')

    I'm having trouble converting this into an INSERT trigger. So far I have

    ALTER TRIGGER [dbo].[UpdateSalSalesPerson]
    ON [dbo].[SalSalesperson]
    AFTER INSERT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO SalSalesperson(Branch, Salesperson, Name)
    SELECT Branch,INSERTED.Salesperson,INSERTED.Name
    FROM SalSalesperson
    where INSERTED.Branch <> (select distinct Branch from SalSalesperson)


    END

    This doesn't work at all.

    Any help would be most appreciated.

    Thanks

    Dave

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Dave

    Almost as soon as you think "Aha! I need a trigger!" your next action should be to stop and double check your reasoning. Triggers are rarely required. It does not mean they are never required, just rarely.

    You have at least one, and very possibly a couple of, normalisation issues here. Is the gist that every Salesperson should be associated with every Branch?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK....square #1

    How does the first INSERT occur?

    Is data accessed or modified only by Stored Procedures?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2009
    Posts
    6

    Trigger help..

    Hi,

    the system we are using is an 'off the shelf' system so we have limited scope in what we can do at table level. As we have multiple branches, for consistency each salesperson is allocoated to each branch so I thought that if a salesperson was added to one branch, a trigger would automatically add them to the other branches (rather than the long winded way of using the software to add each salesman to each branch).

    Is there a better way of achieving what I want without using a trigger?

    By the way, thanks for the quick reply.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this is not your software then your options are more limited.

    This table is not in second normal form. The key is (Branch, Salesperson), yet Name is not wholly dependent on the key (it is only dependent on Salesperson).

    If this were my software, and you have not abstracted the problem, then I would have a Salesperson table and a Branch table, and I would get this information via a View using a CROSS JOIN (i.e. since all salespeople are associated with all branches there is no need to actually persist this relationship).

    However, since you are restricted by the software I would say based on the information the trigger is probably the best appraoch (better than relying on the operator to add it anyway).

    Are there any other factors you have omitted? Is this question a simplification of the problem or does it cover it thoroughly?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2009
    Posts
    6

    trigger help

    No, this is all the info.

    As I said, I can manage the SQL but, as this is my first trigger, I'm struggling to make it work.

    The trigger is completely wrong, from what I understand, it will only ever insert the values from the original software (after initially entering the branch and salesperson details.
    Basically, this is what I believe I need.

    INSERT INTO SalSalesperson(Branch, Salesperson, Name)


    SELECT Branch,INSERTED.Salesperson,INSERTED.Name
    FROM SalSalesperson
    where INSERTED.Branch <>

  7. #7
    Join Date
    Nov 2009
    Posts
    6

    trigger help

    No, this is all the info.

    As I said, I can manage the SQL but, as this is my first trigger, I'm struggling to make it work.

    The trigger is completely wrong, from what I understand, it will only ever insert the values from the original software (after initially entering the branch and salesperson details.
    Basically, this is what I believe I need.

    INSERT INTO SalSalesperson(Branch, Salesperson, Name)

    ** Branch for each distinct branch in SalSalesperson table, INSERTED.SalespersonCode,INSERTED.Salespersonname

    where salSalersperson.Branch <> INSERTED.Branch

    Correct me if I'm wrong.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Basics

    A Trigger will fire when a data modification event happens, and there is an associate trigger set up for that event

    When a modification occurs, SQL server creates to virtual tables that support the event

    One is called inserted, and one is called deleted

    They can ONLY be accessed via a trigger on the table that the modification is taking place

    The inserted table hold the data that is either being inserted, or the view of the data as it appears before an update

    The deleted table contains data as the data appeared before an update, or as the data appeared before a DELETE

    It seems you "know" about the virtual tables, but are not referencing them in the FROM clause

    It appears you have a DB2 or Oracle background...yes?

    Can you list for us, in Business Terms, (Likea spec) what it is that you want to happen

    1. An insert occurs to Table X
    2. When that occurs I want to do y
    3. etc


    Also, if you can post the DDL of the tables involved, that would help

    Thanks, and good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you give us the DDL of all the tables involved.

    For instance, the table containing the branches, the table containing the sales people...
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2009
    Posts
    6

    Trigger help..

    Hi Brett,

    I'll try and answer your questions. The only DB experience I have is using MS SQL server 2000 upwards. Most of the stuff we do is simple SQL.

    The process I want to happen is this.

    1/ the user uses the software to add a new salesperson. The user enter the branch ('CS'), the salespersoncode ('TTT') and the salespersonname ('DMCTEST') and presses add.

    2/ I want this trigger on the salSalesperson table so when this information is added, the trigger looks in the salSalesperson table, gets the distinct branch (not including what has already been entered on the original insert, eg'CS').

    3/ I then want to insert the Salepersoncode and salespersonname for each of the distinct branches.


    I don't know what you mean by posting a DDl of the tables (it only uses one table, the salSalesperson table).

    I'm sorry if I'm asking daft questions, but as I said, I'm new to triggers.

    hope this explains what I want to do.

    Thanks again

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I always have to play around with triggers - I think an AFTER trigger here is ok.

    Code:
    INSERT INTO SalSalesperson(Branch, Salesperson, Name)
        SELECT DISTINCT Branch,INSERTED.Salesperson,INSERTED.Name
        FROM SalSalesperson, INSERTED
        WHERE INSERTED.Branch <> SalSalesperson.Branch
    Also, if if fails please can you elaborate on "it doesn't work at all" e.g. does it error? If so what message? Is there any effect? If so, how does it differ from what you hoped for? etc.

    Also, we know this table is poorly designed. Therefore it is likely the database is too. Is there a separate Branch table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally, I was thinking of a cross join to produce every possible combination and then join that back to the original table o find out which variations we are missing and this would give you your new values...

    If there's not already an existing branches table then we can derive this too if required.

    I feel your pain. I've had cr*p like this to deal with in the past too.
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2009
    Posts
    6

    trigger help..

    SUCCESS!!!!

    I had to prefix the first Branch with SalSalesperson. but then it worked.

    Thanks to pootle flump and everyone elses input.

    Now for my next trigger!!

  14. #14
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    wouldn't this create a loop of inserts? How do you break out of it? My thought on this is that the first insert would insert a row that would then fire the trigger for that row. You need to ensure you have your trigger written to only insert for the branch(es) that do not already have a row.
    Dave Nance

Posting Permissions

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