Dynamically removing records when certain columns = 0; data cleansing

Question

I have a simple card table:

CREATE TABLE `users_individual_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `individual_card_id` int(11) NOT NULL,
  `own` int(10) unsigned NOT NULL,
  `want` int(10) unsigned NOT NULL,
  `trade` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`individual_card_id`),
  KEY `user_id_2` (`user_id`),
  KEY `individual_card_id` (`individual_card_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

I have ajax to add and remove the records based on OWN, WANT, and TRADE. However, if the user removes all of the OWN, WANT, and TRADE cards, they go to zero but it will leave the record in the database. I would prefer to have the record removed. Is checking after each "update" to see if all the columns = 0 the only way to do this? Or can I set a conditional trigger with something like:

//psuedo sql
AFTER update IF (OWN = 0, WANT = 0, TRADE = 0) DELETE

What is the best way to do this? Can you help with the syntax?

You cannot use a trigger to update [delete from] the same table as the trigger, otherwise it causes recursive triggers. Well, not in MySQL anyway, SQL Server and Oracle are happy with it when done well. — RichardTheKiwi, Jan 14 '11 at 02:48

score 1 · Accepted Answer · answered Jan 14 '11 at 02:40

1

Why not just fire two queries from PHP (or other front end)?

update `users_individual_cards` ...
delete `users_individual_cards` where ... (same condition) and own + want + trade = 0

answered Jan 14 '11 at 02:40

RichardTheKiwi

105,798
26
196
262

I thought of this. It feels like a 'dirty' way to do it. One is specific where the other is rather global. I am fine with it if this is how it should be done. But I am looking for a little more elegant solution... that is if one exists. – Chuck Burgess Jan 14 '11 at 02:44
This is the only option right now since triggers cannot manipulate the table they are assigned to. – Chuck Burgess Jan 14 '11 at 16:03

score 1 · Answer 2 · edited Feb 02 '11 at 23:43

1

The trigger will be:

CREATE TRIGGER users_individual_cards_trigger
    AFTER UPDATE ON users_individual_cards
    FOR EACH ROW
    BEGIN
    DELETE FROM users_individual_cards
    WHERE 'OWN' = 0 AND 'WANT' = 0 AND 'TRADE' = 0;
    END$$

The solutions throw the delete query will be better because not all versions of mysql support it.

edited Feb 02 '11 at 23:43

RichardTheKiwi

105,798
26
196
262

answered Jan 14 '11 at 02:48

Rodrigo Santellan

225
2
8

This is more along the lines what I am looking for, yes. I am running MySQL 5.1. This does not appear to work for the version I am running. I also think the 'into' was mistyped into the trigger (so I removed it). Thanks for a great solution! – Chuck Burgess Jan 14 '11 at 03:07
I was able to get the trigger added by chaning it to `CREATE TRIGGER users_individual_cards_trigger AFTER UPDATE ON users_individual_cards FOR EACH ROW DELETE FROM users_individual_cards WHERE 'OWN' = 0 AND 'WANT' = 0 AND 'TRADE' = 0;` ... however, it does not seem to be working. Do I need to 'activate' it somehow? – Chuck Burgess Jan 14 '11 at 03:12
So I figured out why the trigger is not working. Mysql cannot manipulate the table they are assigned too... http://stackoverflow.com/questions/1582683/mysql-trigger-stored-trigger-is-already-used-by-statement-which-invoked-stored-tr – Chuck Burgess Jan 14 '11 at 04:11
@cdburgess - welcome to the limitation of MySQL triggers. see my comment against your question about the trigger approach. Maybe you missed it. – RichardTheKiwi Jan 14 '11 at 10:32

Dynamically removing records when certain columns = 0; data cleansing

2 Answers2