Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    Unanswered: Trigger Hell ... HELP!

    I have two tables:

    table1
    field1 = identity
    field2 ...
    field3 ...
    field4 ...

    table 2
    field1 = identity
    field2 = foreign key (table1 field1)
    field3 ...

    When I delete a row in table1, a cascade delete relationship deletes the appropriate table2 row(s). Since I have a trigger on table2 that updates a few fields in table1 (field3, field4), when I try to delete table1, I get an error. The cascade delete tries to fire off the trigger in table2, which in turn tries to update table1 fields and thus fails. How do I circumvent the triggers from firing?

    Triggers look something like this:

    CREATE TRIGGER trg_delete_table1_field3_field4
    ON dbo.table2
    FOR DELETE
    AS
    BEGIN
    DECLARE @newField3Value as money
    DECLARE @newField4Value as money
    Set @newField3Value = (SELECT SUM(field3) FROM table2 WHERE key = (SELECT key FROM deleted))
    Set @newField4Value = (SELECT SUM(field4) FROM table2 WHERE key = (SELECT key FROM deleted))
    UPDATE table1
    SET field3 = @newField3Value ,
    field4 = @newField4Value
    WHERE key IN (SELECT key FROM deleted)
    END
    Last edited by FastCougar; 10-27-04 at 09:32.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why would you care? The UPDATE that you posted should fail silently (zero rows updated), but not cause any error.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    42
    If I remove the trigger on Table2 and try to delete the row in Table1, it works just fine. Remember, Table1 and Table2 are linked on Table1's PK, which is an FK in Table2 with cascade update/delete. So, when I delete the row in Table1, it in turn deletes the corresponding key related row in Table2. It's VERY important to note that there are multiple rows in Table2 with the PK/FK cascade relationship. What I think is happenings is this:

    1) Table1 row is deleted
    2) Table2 row(1) is deleted
    3) Table2 Trigger fires, which tries to update Table1 row, which is now "gone"
    4) The entire "transaction" is rolled back because of the error

    When I run this code:
    DELETE FROM Table1
    WHERE something = 'some value'

    I get this error:
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah-ha! To minimally kludge your existing trigger, I'd use:
    Code:
    CREATE TRIGGER trg_delete_table1_field3_field4
    ON dbo.table2
    FOR DELETE
    AS
    BEGIN 
       DECLARE @newField3Value as money
       DECLARE @newField4Value as money
    
       Set @newField3Value = (SELECT SUM(field3) FROM table2 WHERE key
          IN (SELECT key FROM deleted))
       Set @newField4Value = (SELECT SUM(field4) FROM table2 WHERE key
          IN (SELECT key FROM deleted))
    
       UPDATE table1
          SET
             field3 = @newField3Value
    ,        field4 = @newField4Value
          WHERE key IN (SELECT key FROM deleted)
    
    END
    -PatP

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    Thanks Pat, that worked! Looking at it now, I fully understand why this works and the = in the where clause doesn't. The subquery was returning multiple records and '=' will only apply to a single record, thus the error. Thanks again!!!

Posting Permissions

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