Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: Need a trigger based on a row

    Hello, everyone

    I need to create a trigger to update a FLAG column by row modification. The table likes,

    Col1 FLAG
    a 0
    b 0
    c 0
    d 0
    e 0

    If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.

    Anyhelp will be approciated.

    Thanks a lot

    ZYT

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:

    Code:
    Update	YourTable
    set	FLAG = 1
    from	YourTable
    	inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
    where	YourTable.Col1 <> Deleted.Col1
    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 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So what happens when the code that launches the trigger is something like:
    Code:
    UPDATE yourtable SET col1 = 'a'
    All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    All of the rows except for one change.-PatP
    I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.

    But yes, as I mentioned in my post, he is going to need a primary key on the table.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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