CODEWITHSUNDEEP
mysqltriggers
Gilberg
Gilberg

Reputation: 2564

MySQL trigger on Delete, update a record?

Can I achieve this without the use of stored procedures. Here are my tables:

--- account table

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_god` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` int(11) DEFAULT '0',
  `history` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--- trigger

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER account_update AFTER UPDATE ON account FOR EACH ROW INSERT INTO account_history VALUES(NEW.id, NEW.id_god, NEW.date_created, NEW.deleted, NEW.history) */;;

--- account history table

DROP TABLE IF EXISTS `account_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account_history` (
  `id` int(11) DEFAULT NULL,
  `id_god` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `deleted` int(11) DEFAULT '0',
  `history` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

My first table, 'account' is pretty much a normal table, but notice I have a field called 'deleted' default to 0. I have a working trigger 'on update' to copy the account record to the account_history table. I also have a similar trigger for insert that I have omitted. When I delete a record I want to set the value of deleted to 1 to track it's deletion in my account_history table.

Initially I was going to run an update query to set delete to 1 in my account table, which would trigger 'on update' and then just delete the record with a second query. I want to know if I can achieve my desired action with only a delete. Can I have a trigger that sets deleted=1 'on delete' either in my account table or directly in my account_history table? And yes, I actually want to delete the record from my account table permanently. I don't want to use a stored procedure.

Upvotes: 0

Views: 1010

Answers (1)

Gilberg
Gilberg

Reputation: 2564

Got it, just needed to add the insert statement into my trigger

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER account_delete BEFORE DELETE ON account
FOR EACH ROW BEGIN
INSERT INTO account_history (id,id_god,date_created,deleted,history) VALUES (OLD.id,OLD.id_god,OLD.date_created,1,OLD.history);
END */;;

Upvotes: 1

Related Questions