Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Unanswered: Verify rows in Trigger after update

    The Trigger must verify one field for eveery row updated and roll back any incorrect ones. I have tried different ways but I can't figure out how to make it work.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In UPDATE trigger both INSERTED and DELETED virtual tables are populated. Those are the ones that you need to work with. Both contain the same number of records too.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Trigger updating

    I changed the code to

    IF UPDATE(vchrPwd)
    BEGIN
    -- select dbo.fdecrypt(vchrPwd) from inserted

    -- start reading virtual record set inserted
    select vchrPwd from inserted
    BEGIN
    .
    .
    .
    END

    which works except that it reads more records than were updated and produces

    (7 row(s) affected)

    Server: Msg 512, Level 16, State 1, Procedure trigPwd, Line 28
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    which causes no updating to occur

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Can you post the trigger?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I suspect that this is related to the other trigger problems that you've posted. It appears to have a "one row" orientation instead of a "set" orientation.

    Unfortunately, asking us to help you fix it without posting the source code is a lot like calling a doctor and telling them "I'm sick"... We know things aren't right, but have no clue what is wrong, so no idea how to help make things better.

    This may be sensitive code, and you may not want to post it. I can understand that, but at least I can't be much help without seeing the code in order to know what is wrong. If that is the case, you may want to consider hiring a consultant. I know that several of the folks here would be able to help you if that is the case, although I can not.

    -PatP

  6. #6
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Thumbs up Solution Found

    You're right about the code being sensitive and thank you guys for the support and help. I found the solution yesterday. I someting like this:

    set @count = @@rowcount
    while (@count <= @@rowcount)
    begin
    select dbo.fdecrypt(vchrPwd) from inserted
    set @count = @count + 1
    end

    I check each row one row at a time.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As much as I hate to have to say this, your code seems like a poster-child candidate for a cursor to me.

    -PatP

  8. #8
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Solution Found

    You're right. Because there are no SQL activities in this Trigger I had to step through the only recordset available.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Trigger-based solution always raises my eyebrows (like they are not raised enough already )
    Are you sure about all this?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If my eyebrows got to be too raised, I'd look like I had hair again!

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hair is something I can;t even talk about....
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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