Results 1 to 6 of 6

Thread: trigger problem

  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unhappy Unanswered: trigger problem

    Hi,
    I have two tables:
    table A with key name, version
    table B with key id and foreing key A.name,A.version.

    The realtion A to B is 1:n

    The id in table B is integer, to count number of elements in B, So when I delete an element from A , I should delete all corresponding elements from B and shift ids up, for example:
    A:
    name version
    1 1
    1 2

    B:
    id name version
    1 1 1
    2 1 1
    3 1 2

    After delete of record 1 1 from A , table B should be:
    id name version
    1 1 2

    I'm trying to right the trigger that checks the deleted id and for all bigger is makes id = id-1...

    The following doesn't work:
    CREATE TRIGGER trUpdateID ON B
    FOR DELETE
    AS
    DECLARE @id int
    SELECT @id = id
    FROM deleted
    UPDATE B
    SET id =id- 1
    WHERE
    @id < id


    Please, Help....
    Thank you
    Yulia

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Triggers run once for each insert, not for each record inserted, and the Inserted and Deleted tables can hold more than one record. That is why your code does not work. I believe this code will do what you SAY you want to do:

    CREATE TRIGGER trUpdateID ON B
    FOR DELETE
    AS
    UPDATE B
    set id = id - 1
    from B
    inner join deleted on B.id > deleted.id

    ..but this is a goofy way to maintain relational integrity, and I think it is going to cause you more problems in the future. For instance, if you perform three individual DELETE statements, your record IDs will be decremented three times, but if you perform one DELETE that affects three records your IDs will only be decremented by one.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    Yes, you are right, if three records deleted all rest of records decremented only once, this was the reason I posted the thread: I 'm looking for the trigger with the loop that decrements records number of times as number of records deleted, to keep ids of table B contingue: 1,2,3...n

    Thanks
    Thank you
    Yulia

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    First of all, your trigger should be on table A (not on table B) so that when you delete a row from table A, the corresponding rows from table B should be deleted.
    The trigger will look like:

    CREATE TRIGGER trUpdateID ON A
    FOR DELETE
    AS
    DECLARE @id int, @rows_affected int

    select @id=B.id from B, deleted D
    where D.name = B.name
    and D.version = B.version

    delete B from deleted D
    where D.name = B.name
    and D.version = B.version

    select @rows_affected = @@rowcount

    UPDATE B
    SET id =id - @rows_affected
    WHERE
    @id < id

    Also, if your query which deletes row from table 'A' is deleting more than 1 row at a time, you need to use cursor in this trigger so that for each name+version combination it will delete the corresponding rows in table 'B'.

    Thanks.
    Pat

    -------------------------------------------------------------
    Originally posted by yuliam
    Yes, you are right, if three records deleted all rest of records decremented only once, this was the reason I posted the thread: I 'm looking for the trigger with the loop that decrements records number of times as number of records deleted, to keep ids of table B contingue: 1,2,3...n

    Thanks

  5. #5
    Join Date
    Nov 2003
    Posts
    33

    Thumbs up

    Thank you very much for you answer
    Thank you
    Yulia

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think that is going to give you the results you want. @ID and @rows_affected are only going to hold one value each, but if you are deleting a group of records then the amount that you decrement corresponding records might not be the same for each record.

    Try this logic in a trigger on table A:

    Update B
    set id = id - Subquery.DecrementValue
    from B
    inner join
    (select B.ID, count(*) DecrementValue from B inner join deleted on B.ID > deleted.ID) Subquery on B.ID = Subquery.ID
    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
  •