Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: Help with triggers

    I'm fairly new to writing triggers and it's giving me a beatdown. What I'm trying to do is create a single trigger that does the following. 1) Corrects the common mistakes of our users input, which mostly consists of adding erroneous spaces. Then creating the data for two columns from the previous columns' data entered. I will also add the upper(text) later on. 2) To check to a column to verify that the entry is on our master list.

    I have been able to make 1 & 2 work as separate triggers. When I try to combine them into one trigger it appears that everything runs simultaneously and gives me an error.

    Can someone help?

    Thanks,
    JB


    CREATE TRIGGER SNC ON [DATA].[a44]
    FOR INSERT, UPDATE
    AS
    UPDATE DATA.a44
    /* THIS IS PART 1 OF MY CODE */
    SET COL1 = rtrim(COL1)
    UPDATE DATA.a44
    SET COL1 = ltrim(COL1)
    UPDATE DATA.a44
    SET COL2 = rtrim(COL2)
    UPDATE DATA.a44
    SET COL2 = ltrim(COL2)
    UPDATE DATA.a44
    SET COL3= rtrim(COL3)
    UPDATE DATA.a44
    SET COL3 = ltrim(COL3)
    UPDATE DATA.a44
    SET COL4 = rtrim(COL4)
    UPDATE DATA.a44
    SET COL4 = ltrim(COL4)
    UPDATE DATA.a44
    SET COL5 = rtrim(COL5)
    UPDATE DATA.a44
    SET COL5 = ltrim(COL5)
    UPDATE DATA.a44
    SET COL6 = COL2 + ' ' + COL3 + ' ' + COL4
    UPDATE DATA.a44
    SET COL6 = rtrim(COL6)
    UPDATE DATA.a44
    SET COL6 = ltrim(COL6)
    UPDATE DATA.a44
    SET COL7 = COL1+ ' ' + COL6+ ' ' + COL5
    UPDATE DATA.a44
    SET COL7 = rtrim(COL7)
    UPDATE DATA.a44
    SET COL7 = ltrim(COL7)
    /* THIS IS PART 2 OF MY CODE */
    IF (select count (*) from a37) = 0
    BEGIN
    IF (select count (*) from master_list, inserted
    WHERE master_list.COL6 = inserted.COL6) = 0
    BEGIN
    RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
    END
    END
    ELSE
    IF (select count (*) from master_list, a37, inserted
    WHERE master_street_list.COL6 = inserted.COL6 or a37.COL6 = inserted.COL6) = 0
    BEGIN
    RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
    END

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First, why aren't you doing one update, second that's going to cut into performance, third you probably want an INSTEAD OF TRIGGER, fourth, you look like you're working with a single row, when triggers may reference n rows, fifthyou need to reference the virtual table inserted, sixth you should probably not do this and only exec authorit to developers against stored procedures...there's probably more...
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Step zero is read Books Online sections on triggers and virtual inserted/deleted tables.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    First of all, your trigger is for both INSERT and UPDATE. If you do an update within the trigger then it is repeated again. Secondly, you have multiple update statements forcing the trigger to be executed for each update. Thirdly, the update statement will update the entire table because you have not used WHERE predicate. You can combine all your updates into a single update if this is what you want to do.

    UPDATE <table> SET <col> = LTRIM(RTRIM(<col>)), <col1> = LTRIM(RTRIM(<col1>)), ...
    WHERE ...

    During the INSERT, DELETE, UPDATE operations, both 'inserted' and 'deleted' logical tables are created to temporarily store the data. You may want to reference these tables to restrict the selection criteria if your intention is to modify the recently entered data. Please refer to booksonline, as suggested by others, for further info.
    Last edited by skrishnamurthy; 09-23-05 at 16:22.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by skrishnamurthy
    First of all, your trigger is for both INSERT and UPDATE. If you do an update within the trigger then it is repeated getting executed.
    Nope.
    Quote Originally Posted by skrishnamurthy
    Secondly, you have multiple update statements forcing the trigger to be executed for each update.
    Nope.
    Quote Originally Posted by skrishnamurthy
    Thirdly, the update statement will update the entire table because you have not used WHERE predicate.
    Hey! Got one!
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Sep 2005
    Posts
    28

    Wink

    Yep! when the RECURSIVE_CURSORS (Direct recursion) option is set (ON). Since, he said that it was erroring, I kind of assumed this was on.

    Quote Originally Posted by blindman
    Nope.
    Nope.
    Hey! Got one!

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    OMG, he said the 'A' word.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd better dollars against doughnut holes that he does not have recursive triggers set on.

    "A" word?
    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 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    No, the OTHER "A" word...assume.

    Sorry...leftover chastisement from another life, I guess
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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