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:
CREATE TRIGGER tr_Delete_Actions
-- 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)
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?
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