Let me try to clarify.
Your trigger is designed to delete rows from the Business_Logos table whenever the row in Business is deleted.
If one row is deleted from Business, your trigger will work properly, even if there are multiple rows in BusinessLogos.
If multiple rows are deleted from Business, your trigger will fail to delete all the relevant rows from business logos.
Here's some code that demonstrates what I am trying to say.
First, create some test tables to play with.
Code:
Create Table TestBusiness(BusinessId Int, LogoId Int)
GO
Insert Into TestBusiness Values(1,100)
Insert Into TestBusiness Values(2,200)
Insert Into TestBusiness Values(3,300)
GO
Create Table TestBusinessLogo(LogoId Int, FileName VarChar(20))
GO
Insert Into TestBusinessLogo Values(100,'Red.jpg')
Insert Into TestBusinessLogo Values(200,'Blue.jpg')
Insert Into TestBusinessLogo Values(300,'Green.jpg')
Notice there are 3 businesses, each with a logo.
Now, we create the trigger the way you suggested.
Code:
Create TRIGGER DeleteLogo
ON TestBusiness AFTER DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @LOGOID Int
SELECT @LOGOID = logoid FROM deleted
DELETE FROM TestBusinessLogo WHERE LogoId = @LOGOID
END
Now (and here's the key point I am trying to make), let's delete multiple rows from the business table.'
Code:
Delete From TestBusiness Where BusinessId In (1,2)
We just deleted 2 businesses, so we should also be deleting 2 logos (because each business had 1 logo associated with it).
Code:
Select * From TestBusinessLogo
Notice that only the first logo was deleted from the TestBusinessLogo table. When multiple rows are affected by a query, the trigger will only fire once. In this case, a single query caused 2 rows to be deleted. Within the trigger, the deleted table would have 2 rows in it, but your trigger would only select the first one in to the @LOGOID variable, and would subsequently only delete the logo(s) for one business.
To remove the test tables...
Code:
Drop Table TestBusiness
Drop Table TestBusinessLogo
Now, let's test it with the trigger I suggested:
Code:
Create Table TestBusiness(BusinessId Int, LogoId Int)
GO
Insert Into TestBusiness Values(1,100)
Insert Into TestBusiness Values(2,200)
Insert Into TestBusiness Values(3,300)
GO
Create Table TestBusinessLogo(LogoId Int, FileName VarChar(20))
GO
Insert Into TestBusinessLogo Values(100,'Red.jpg')
Insert Into TestBusinessLogo Values(200,'Blue.jpg')
Insert Into TestBusinessLogo Values(300,'Green.jpg')
Go
Create TRIGGER DeleteLogo
ON TestBusiness AFTER DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE TestBusinessLogo
FROM TestBusinessLogo
Inner Join Deleted
On TestBusinessLogo.LogoId = [!]Deleted[/!].LogoId
END
Go
Delete From TestBusiness Where BusinessId In (1,2)
go
Select * From TestBusinessLogo
go
Drop Table TestBusiness
Drop Table TestBusinessLogo
When you run the code above, you'll see that there is only one row remaining in the BusinessLogo table.
I will admit that the original code I posted was flawed. I sincerely hope that code was not executed because it may have deleted all of the logo from the business logo table. You see, instead of joining to the deleted table, it was joining to the real table. I sincerely apologize if this caused any problems.
foxbox, I hope you run this query. The important thing to realize is the triggers can fire when multiple rows are affected. This can occur for inserts, updates and deletes. Whenever you write a trigger, you MUST code it with the assumption that multiple rows will be affected. If 99.999% of the time it is only one row, that's fine.
apex82, I sincerely apologize if my previous code suggestion caused you to lose data from the Business Logo table.
-
George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
