Create trigger on update

dparsons · May 1st, 2007, 08:06 AM

TSQL Code Follows: (this won't work in MySQL but the conversion should be easy)

DECLARE @i int

SET @i = (SELECT refcount from [table] where rowid = a)
SET @i = @i - 1

if @i = 0
Begin
DELETE from table where rowid = a
end
else
begin
--UPDATE
end

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

bvpsekhar · May 1st, 2007, 08:21 AM

well thanks for that, i am using sqlite. Not sure whether one can use what u have mentioned in sqlite.

dparsons · May 1st, 2007, 08:28 AM

As I said, it is TSQL (the type of SQL that MSSQL Server uses) however converting that simple statement so that it will work within your environment should be a rudimentary task.

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

happygv · May 2nd, 2007, 01:29 AM

You could also try Delete and Update, like this.

Code:

--Deleting the row that can have ZERO if updated
DELETE FROM <table> WHERE rowid = a AND refcount - 1 = 0
--If no rows deleted, then obviously it is ready for update
IF @@ROWCOUNT = 0
    UPDATE TABLE SET refcount = refcount - 1 where rowid = a

Hope that helps.
Cheers.

_________________________
- Vijay G
Strive for Perfection

bvpsekhar · May 2nd, 2007, 03:42 AM

Hey Vijay,
Thats one of the things which i wanted thanks for that info. Let me try to check out whether it works out in sqlite. Can we use the expression "IF @@ROWCOUNT = 0"? Is the return value of any statement stored in ROWCOUNT?

happygv · May 2nd, 2007, 04:14 AM

If DELETE statement deleted any of the rows, then @@ROWCOUNT the server variable should have value greater than ZERO(number of Rows deleted). When something has been deleted, then you dont have that row to be updated.

_________________________
- Vijay G
Strive for Perfection

bvpsekhar · May 2nd, 2007, 04:25 AM

Which SQLServer r u using?
Hey can it be done using single query? what i meant is, the worst canse should be only one search on the table. If you see your soln you will be searching the table twice.

dparsons · May 2nd, 2007, 08:35 AM

No, you can not have a DELETE statement as a sub query in the manner that you are asking. Even if you could, each statement would be evaluated and would search the database each time.

For example:
SELECT [column] FROM [table] WHERE name IN(SELECT name from table where id = x)

This will cause 2 queries to be executed it just happens to be with in on statement so it doesn't reduce the number of times the server searches your 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
================================================== =========

happygv · May 2nd, 2007, 08:40 AM

In the code that I posted, you may avoid UPDATE if DELETE succeeded, otherwise DELETE won't find any matching and only UPDATE will be done. Still considering the code, I dont think you are doing anything heavy there to worry about the performance hit.

_________________________
- Vijay G
Strive for Perfection

bvpsekhar · May 2nd, 2007, 11:51 PM

dParsons,
Yeah i am aware of it. But i am curious to know why doesn't SQL allow us to do it in a single search which is possible pratically. Is this a limitation of SQL?

happygv,
The performance does worry me thats why i have opened a new thread. The worst part is its possible pratically, but then in my knowledge SQL doesn't allow.