Delete record from another table 2

[apex82]

I use the following SQL statement coded in ASP to delete a record from a SQL server database:

It deletes the record based on a dropdown menu which send the Business_ID.

SQL = "DELETE FROM Business"
SQL = SQL & " WHERE [Business_ID]='" & str_delete & "'"

I also want to delete an image that is associated with the record from a table called [Business_Logos].

Here is the structure of the [Business_Logos] table:

Logo_ID – Logo data

The field that links them is [Business].LogoID and [Business_ Logos].Logo_ID.

Can anyone help?

Thanks.

 

[foxbox]

Create a ON DELETE trigger for [Business].

Something in line with this:

CREATE TRIGGER DeleteLogo
 ON  Business AFTER DELETE
AS 
BEGIN

DECLARE @LOGOID
SELECT  @LOGOID = logoid FROM deleted

DELETE FROM [Buinsess_Logos] WHERE logo_id = @LOGOID

END

 

[gmmastros]

No. Not like this. This trigger assumes that only one row will be deleted at a time. While it may be true that only one row will be deleted MOST of the time, this may not always be true.

CREATE TRIGGER DeleteLogo
 ON  Business AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

DELETE [Business_ Logos]
FROM   [Business_ Logos]
       Inner Join Business
         On [Business_ Logos].Logo_ID = Business.Logo_id

END

Written this way, the effect will be the same, but it will accommodate multiple rows, too.

The cool thing about the trigger approach is that they occur automatically. Whenever you delete a row from the Business table, this code will run. You don't need to do anything to cause it.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[foxbox]

My trigger does not assume there is 1 record.
It is deleting *every* record with a certain Logo_id.

If Logo_id is the primary (thus unique) key of Business_logos then yes: it is always 1 record, but does not make the trigger bad, because then the functionality of this application is that there is 1 Logo..

"The field that links them is [Business].LogoID and [Business_ Logos].Logo_ID."

 

[apex82]

Thanks chaps.

If I add the following code the correct record from the [business] table is deleted however, it deletes everything other than the correct record from the [business_logos] table.

sql = "DELETE FROM Business"   & _
"sql = sql & " WHERE [Business_ID]='" & str_delete & "'"  & _

"CREATE TRIGGER DeleteLogo " & _
"ON  Business AFTER DELETE " & _
"AS " & _
"BEGIN " & _

"DECLARE @LOGOID " & _
"SELECT  @LOGOID = logoid FROM deleted " & _

"DELETE FROM [Business_Logos] WHERE [Business_Logos].Logo_ID = @LOGOID " & _

"END  " & _

Is there a simple tweak that can be made to sort this?

Thanks.

 

[gmmastros]

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.

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.

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.'

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).

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...

Drop Table TestBusiness
Drop Table TestBusinessLogo

Now, let's test it with the trigger I suggested:

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

 

[gmmastros]

apex82,

You only need to create the trigger once. Do this:

Open SQL Server Management Studio. Open a new query window. Select your database.

Copy/paste this:

CREATE TRIGGER DeleteLogo
 ON  Business AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

DELETE [Business_ Logos]
FROM   [Business_ Logos]
       Inner Join Business
         On [Business_ Logos].Logo_ID = deleted.Logo_id

END

Run the code above by pressing F5 on your keyboard. This trigger will permanently be stored in the database, so you do not need to re create it.

In your ASP page, all you need to do is to delete from the Business table, and the corresponding row(s) from the logos table will automatically be deleted for you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Thanks, I added the code and it gave an error:

Msg 4104, Level 16, State 1, Procedure DeleteLogo, Line 8
The multi-part identifier "deleted.LogoID" could not be bound.

On this line I changed from:

On [Company_Logos].Logo_ID = deleted.LogoID

To:

On [Business_Logos].Logo_ID = Business.LogoID

The trigger then worked but it deletes everything from [Business_Logos] apart from the record I want it to.

Do I change something in the trigger code?

Thanks.

 

[apex82]

In the post above

On [Company_Logos].Logo_ID = deleted.LogoID

Should have been

On [Business_Logos].Logo_ID = deleted.LogoID

 

[gmmastros]

CREATE TRIGGER DeleteLogo
 ON  Business AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

DELETE [Business_ Logos]
FROM   [Business_ Logos]
       Inner Join [!]deleted[/!]
         On [Business_ Logos].Logo_ID = deleted.Logo_id

END

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Thanks very much.

I'll try that.

I appear to have it now working using the following but I try your suggestion too:

Thanks again!

CREATE TRIGGER [dbo].[Delete_Logo]
ON [dbo].[Business] AFTER DELETE
AS
BEGIN

DECLARE @LogoID int

SELECT @LogoID = LogoID FROM deleted

DELETE FROM [Business_Logos] WHERE [Business_Logos].Logo_ID = @LogoID

END

 

[gmmastros]

I keep thinking about this.

I apologize for the code snippets that I posted. Seems like I kept making mistakes and I am truly sorry for that. But, the fact remains that there is a problem with the trigger the way that you implemented it. If multiple rows are removed from the business table with a single query, your trigger will not remove all of the rows from the logos table that is should.

In my opinion, the data you have in your database is more value than the code you write for the front end. It needs to be protected at all costs.

I encourage you to do a little research on "Foreign Key Constraints" and "Cascade Delete".

Set up properly, a foreign key constraint would prevent you from deleting rows from the business table if there is a corresponding row in the logos table. A cascade delete would automatically delete rows from the logos table when you delete rows from the business table.

These things exist in a SQL Database to help you protect your data. They are good things that you should know about.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

I only delete one record at a time so should that be okay.

I will research on "Foreign Key Constraints" and "Cascade Delete".

Thanks again.

 

[gmmastros]

Even if you only delete one record at a time now, things have a way of changing over time. Do it correctly now and you won't have to worry about it later.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom