I'm building a recycle bin like a windows trash can. I have 3 tables.
when the parent table is deleted, change parent.isdeleted = 1 and the parent is One to many table, so every time delete the parent table will also delete the same id items(trigger child table after update parent ). and trigger the insert record in the recycle table(triggered after update parent).
When deleting the child table, also trigger insert record in the recycle table
so when delete my parent table will trigger the child's trigger
when I delete parent table will show:
---------------------------------------------
parent_id child_id from
---------------------------------------------
1 (2,3,4) parent
1 2 child
1 3 child
1 4 child
I only want first row.
my trigger for parent:
DROP TRIGGER IF EXISTS `after_recorditems_update`;
CREATE DEFINER=`root`@`localhost` TRIGGER `after_recorditems_update`
AFTER UPDATE ON `sn_recorditems`
FOR EACH ROW BEGIN
UPDATE sn_recordlog SET is_deleted = new.is_deleted WHERE new.id = `recordid`;
INSERT INTO sn_recycle(deleted_at,recorditem_id,log_id,from_table_name) VALUES(new.time,new.id,(SELECT GROUP_CONCAT(sn_recordlog.id SEPARATOR', ') as id FROM sn_recordlog WHERE new.time= sn_recordlog.time), 'sn_recorditems');
END
my trigger for child:
BEGIN
INSERT INTO
sn_recycle(deleted_at,recorditem_id,log_id,from_table)
VALUES(new.time, new.recordid, new.id, 'sn_recordlog');
END