Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10

    Question Unanswered: Triggers don't fire with mass deletes/updates

    I have used triggers on many of the tables in my database. They seem to work just fine most of the time. For instance, a trigger that fires on this update works perfectly:

    Code:
    UPDATE	Contact
    SET	ContactTypeID = 1
    WHERE	ContactID = 1234
    BUT...
    The same trigger will not work in this query:

    Code:
    UPDATE	Contact
    SET	ContactTypeID = 1
    WHERE	ContactID BETWEEN 1000 AND 2000
    It seems like when I try a mass update, the trigger doesn't like to fire. Any ideas?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Post the code for your trigger. Most people don't write triggers to handle multi record updates. A trigger acts on a batch which could contain one or more records.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10
    I didn't know that there was a different way to write triggers to handle multi record updates. Here is an example of one of my triggers:

    Code:
    CREATE TRIGGER SetPrimaryContactInfo ON dbo.ContactInfo
    FOR INSERT, UPDATE
    AS
    
    -- Declare variables
    DECLARE	@ContactID AS INT, @ContactInfoID AS INT, @isPrimary AS BIT
    
    -- Set variables
    SELECT	@ContactID = ( SELECT ContactID FROM Inserted ),
    	@ContactInfoID = ( SELECT ContactInfoID FROM Inserted ),
    	@isPrimary = ( SELECT isPrimary FROM Inserted )
    
    -- When inserting or updating an address, set isPrimary to off for all other addresses if this one is primary
    IF @isPrimary = 1
    BEGIN
    	UPDATE	ContactInfo
    	SET	isPrimary = 0
    	WHERE	ContactID = @ContactID
    	AND	ContactInfoID <> @ContactInfoID
    END

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    you might try:

    Code:
    CREATE TRIGGER SetPrimaryContactInfo ON dbo.ContactInfo
    FOR INSERT, UPDATE
    AS
    
    Update ContactInfo
       set ContactInfo.isPrimary = 0
      from ContactInfo
      join insrted on ContactInfo.ContactID = inserted.ContactID
     where ContactInfo.ContactInfoID <> inserted.ContactInfoID
       and inserted.isPrimary = 1
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10

    Lightbulb

    Interesting. So I should be thinking of Inserted as a table with multiple records. Until now I've always thought of it has having one and only one record - the record that is currently being modified. But I guess in a mass update there may be many records in it at any one time. So the query in your trigger makes sense. Thanks!

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Very common mistake made by almost everyone, until they see the light!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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