I am working with mysql and I have a table with the following structure (a summary):
CREATE TABLE `costs` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `utility` DECIMAL(9,2) UNSIGNED NOT NULL, `tax` DECIMAL(9,2) UNSIGNED NOT NULL, `active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1', `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (`id`), )
where the active field defaults to 1 when inserting, then i would like when saving a new record all other rows update the active field as 0, so i try to create a trigger for this but i am getting a mysql error.
DELIMITER // CREATE TRIGGER after_costs_insert AFTER INSERT ON costs FOR EACH ROW BEGIN UPDATE costs SET active = 0 WHERE id <> NEW.id; END; // DELIMITER ;
I think it is not possible to do this, so how can I update these rows?