In MySQL, how would I duplicate an existing record

Let's say I have a table of core and 2 supporting tables we'll call activity and steps.

Core might contain the following:

coreID(int) | coreLabel(varchar)
--------------------------------
1           | Wash Car
12          | Wash Dog
15          | Vacuum the carpet

Activity might contain the following:

aID(int auto) | coreID(int fk) | menuID(int fk)
-----------------------------------------------
1             | 1              | 268
2             | 1              | 269
3             | 12             | 269
4             | 12             | 239
5             | 12             | 230
6             | 15             | 237
7             | 15             | 269
8             | 15             | 244
9             | 15             | 242

Steps might contain the following:

stepID(int auto) | coreID(int fk) | aID(int fk)
-----------------------------------------------
1                | 1              | 1
2                | 1              | 2
3                | 12             | 5
4                | 12             | 6
5                | 12             | 9
6                | 15             | 10
7                | 15             | 11
8                | 15             | 26

The website will show the core items. The user should be able to click a button to duplicate an item (e.g. duplicate wash dog, coreID=2) such that Core would now have the values

coreID(int) | coreLabel(varchar)
--------------------------------
1           | Wash Car
12          | Wash Dog
15          | Vacuum the carpet
16           | Wash Dog (copy)

...and the data in Activity should look like this:

aID(int auto) | coreID(int fk) | menuID(int fk)
-----------------------------------------------
1             | 1              | 268
2             | 1              | 269
3             | 12             | 269
4             | 12             | 239
5             | 12             | 230
6             | 15             | 237
7             | 15             | 269
8             | 15             | 244
9             | 15             | 242
10            | 16             | 268
11            | 16             | 269

...and then Steps should look like this:

stepID(int auto) | coreID(int fk) | aID(int fk)
-----------------------------------------------
1                | 1              | 1
2                | 1              | 2
3                | 12             | 5
4                | 12             | 6
5                | 12             | 9
6                | 15             | 10
7                | 15             | 11
8                | 15             | 26
9                | 16             | 10
10               | 16             | 11

Duplicating the records in Core and Activity is straight forward, I have no issues there. What I don't know how to do is duplicate the supporting records in Steps because when a record is inserted into Activity, the inserted value for aID is required for the insertion into Steps and that specifically is where my dilemma is... Can MySQL handle this for me with a trigger such as what I found here: https://dba.stackexchange.com/questions/37470/cascading-inserts-in-mysql and if so, how would I pass in non default values?

Update A trigger on the table isn't going to work. I need to be able to add data to the Activity table without the db also adding a row into the step table. Normally I think that solution would have worked but not under this scenario.

Update #2 I created a database fiddle. This is made up sample data derived from my true source data but has been simplified so hopefully the sample data is sufficient.

CREATE TABLE `core` (
    `coreID` INT(11) NOT NULL AUTO_INCREMENT,
    `coreLabel` VARCHAR(150) NOT NULL DEFAULT 'Untitled',
    PRIMARY KEY (`coreID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `activity` (
    `aID` INT(11) NOT NULL AUTO_INCREMENT,
    `coreID` INT(11) NULL DEFAULT NULL,
    `menuID` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`aID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `steps` (
    `stepID` INT(11) NOT NULL AUTO_INCREMENT,
    `coreID` INT(11) NULL DEFAULT NULL,
    `aID` INT(11) NOT NULL,
  PRIMARY KEY (`stepID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

insert into core (corelabel) values ('Wash Car');
insert into core (corelabel) values ('Wash Dog');
insert into core (corelabel) values ('Vacuum Rug');

insert into activity (coreID, menuID) values (1,268);
insert into activity (coreID, menuID) values (1,269);
insert into activity (coreID, menuID) values (12,268);
insert into activity (coreID, menuID) values (12,239);
insert into activity (coreID, menuID) values (12,230);
insert into activity (coreID, menuID) values (15,237);
insert into activity (coreID, menuID) values (15,269);
insert into activity (coreID, menuID) values (15,244);
insert into activity (coreID, menuID) values (15,242);
insert into activity (coreID, menuID) values (4,268);


insert into steps (coreID, aID) values (1,1);
insert into steps (coreID, aID) values (1,2);
insert into steps (coreID, aID) values (12,5);
insert into steps (coreID, aID) values (12,6);
insert into steps (coreID, aID) values (12,9);
insert into steps (coreID, aID) values (15,10);
insert into steps (coreID, aID) values (15,11);
insert into steps (coreID, aID) values (15,26);

select * from core;
select * from activity;
select * from steps;
Select
    c.coreID,
    c.coreLabel,
    a.aID,
    a.menuID,
    s.stepID
From
    core c Left Join
    activity a On a.coreID = c.coreID Left Join
    steps s On s.aID = a.aID;

-- If we pretend a button was clicked to duplicate "Wash Car", we'd expect the following inserts to occur;
-- the issue is actually getting these values programmatically;

insert into core (corelabel) values ('Wash Car (copy)');
insert into activity (coreID, menuID) values (4,269);   
insert into steps (coreID, aID) values (4,10);
insert into steps (coreID, aID) values (4,11);

Select
    c.coreID,
    c.coreLabel,
    a.aID,
    a.menuID,
    s.stepID
From
    core c Left Join
    activity a On a.coreID = c.coreID Left Join
    steps s On s.aID = a.aID;

The last select query (see above) returns 4 records. This should be correct since "Wash Car" has two steps. The scenario is that the user wants to use the "Wash Car" as a template for another procedure. After clicking the duplicate button next to "Wash Car" on the web site, we should see 6 records returned and we should see an entry for "Wash Car (copy)"

回答1:

Duplicating the records in Core and Activity is straight forward

And would probably look something like this:

set @sourceCoreID = 12;

insert into core (coreLabel)
  select coreLabel
  from core
  where coreID = @sourceCoreID;

set @newCoreID = last_insert_id();

insert into activity (coreID, menuID)
  select @newCoreID, menuID
  from activity
  where coreID = @sourceCoreID;

All you need now, is to copy some data from the new activity rows to steps (At least it looks like that in your sample result). And there is not much magic needed:

insert into steps(coreID, aID)
  select coreID, aID
  from activity
  where coreID = @newCoreID;

db-fiddle

回答2:

CREATE TRIGGER steps_ti 
AFTER INSERT ON `Activity` 
FOR EACH ROW 
INSERT INTO Steps (aID,coreID) VALUES (NEW.aID,NEW.coreID);