I have a table in which has a column named "count".

How to write a SQL statement that will delete the row (automatically) in the given table when the count column value becomes 0.

Regards,
Phani

__________________
Regards,
Phani

Normally you would use an Instead Of clause in your trigger but MySQL doesnt support it

So you would have to do something like

CREATE Trigger tgr_Delete After Update on [tbl]
Begin
  Delete from [tbl] where [CountColumn] = 0
End

You will have adjust this so MySQL likes it (adding ; and such) but the concept is the same.


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

dParsons,
  Here you are not deleting it automatically you are doing query for count = 0, in your delete sql statement. Cant it be done automatically?

Well, define automatically. Automatically to me means that I don't have to issue a speicfic query against the database and, in this case, the trigger handles the delete hence automatically.

In anycase, if MySQL supported Instead Of you could check to see if the value getting updated to the count column was 0 and just discard the update, but it doesnt. You can use BEFORE INSERT/UPDATE and AFTER INSERT/UPDATE Again this will require you to write logic so I guess it isnt "automatic".

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

What i meant was the row should get delete automatically without searching for it "where count =0". May be its not possible in sql. Thanks for your inputs

The problem i am facing with the trigger is, it will get invoked whenever there is an update on the table, which is reducing the efficiency of the query. What i would like to have is it should not be triggered every time when there is an update, but it should be triggered only when the value becomes 0.

Then you can't use a trigger. Triggers will fire everytime unless you explicitly disable them in your code.

I am curious though, how do you expect SQL to delete a single row, if you don't want to let it use a where clause? This seems a bit ubsurd.

In any case this IS possible in SQL using a Trigger, however, not within MySQL as I have previously stated. You will need to come up with some other logic that will eliminate or completely ignore the row from being inserted into the table.



================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Of course you cant delete a single row without using the where clause.

But after the updation of the refcount you cant check (immediately) whether the value has become 0, and if it is you can delete that row at that time, without searching the table for values with refcount 0.

its like

Update refcount = refcount - 1 where rowid = ?.
if(refcount == 0)
delete rowid = ?

The problem is that, here in this case you need to search for the given rowid in the table only once, whereas in sqlite you will need to search the table twice:
onc for updating the refcount for the given id.
Another for deleting the row whose refcount value is zero.

Will th query given below will do what i want?

CREATE Trigger tgr_Delete After Update on [tbl]
Begin
  Delete from [tbl] where [CountColumn] = 0 and rowid = old.rowid
End

But here also there is search involved for rowid in the given table.

Is there any sql query that will delete the row when the refcount value becomes zero, without searching the table twice?

I am not sure that I understand what you are trying to do? You want to make an update to a new row and delete an old row, is that correct?

If that is the case, I might consider using a stored procedure that will insert a new row and also test the value of column in question and then do something based on that value.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Update refcount = refcount - 1 where rowid = A.

if(refcount == 0)
delete rowid = A

I want the above two lines to be executed in SQL. That is, when i update the refcount of a particular rowid (assume A) and if the refcount value becomes 0, i want to delete that row (A). Basically i dont want to search the table twice. I need a solution which does search the table only once.