Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: stuck creating delete trigger

    Hoping someone can help me understand how to create a delete trigger.

    I have a table containing actions that I want to delete after the company has been deleted from the main table.

    I am trying the following without much success:
    Code:
    CREATE TRIGGER tr_Delete_Actions
       ON  dbo.tblActions
       AFTER DELETE
    AS 
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for trigger here
        DELETE FROM dbo.tblActions
        WHERE dbo.tblActions.acti_comp_id = (SELECT deleted.comp_id FROM deleted)
    END
    But I keep getting the error:
    Invalid column name 'comp_id'.
    Is there any way of getting the comp_id after it has been deleted from the table?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A few points:
    1) If this is 2005+ you can do this by cascading deletes as part of the foreign key definition. This would be better practice than using a trigger
    2) Your trigger assumes only one row is ever deleted at a time and will error if there are more than one
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    3) Also, looking at it you have created the trigger on the wrong table. What is the name of the "main table"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    Thanks poots, ended up using a stored procedure in the end.

    I did try and set up a foreign key and add cascade to delete the record, but it wouldn't let me. Gave an error when linking to the primary key in the main table.


    Edit: Just tried to create the foreign key again and now it works, weird
    <- Hides behind a rock.

Posting Permissions

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