mysql - 如何创建一个触发器，在更新表中的列之前将整个旧行保存到新表中？

Question

我有 team_members 表，其结构如下，其中仅更新 project_request_id。我使用相同的数据结构创建了 log_team_members ，因此每当我更新 team_members 时，我都会丢失读取所需的旧数据，这就是为什么我正在考虑创建一个 trigger 在更新 team_members project_request_id 之前，它会将整个旧行保存到此表中。

你能帮我从 Latavel 迁移中构建这个触发器吗？

public function up()
{
    Schema::create('team_members', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('title');
        $table->string('picture');
        $table->string('email')->unique();
        $table->string('phone_number');
        $table->text('background_information')->nullable();
        $table->timestamps();
        $table->integer('project_request_id')->unsigned();
        $table->foreign('project_request_id')->references('id')->on('project_requests')->onDelete('cascade');
    });
}

public function up()
{
    Schema::create('team_members', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('title');
        $table->string('picture');
        $table->string('email')->unique();
        $table->string('phone_number');
        $table->text('background_information')->nullable();
        $table->timestamps();
        $table->integer('project_request_id')->unsigned();
        $table->foreign('project_request_id')->references('id')->on('project_requests')->onDelete('cascade');
    });
}

这就是 team_members 更新发生的地方:

public function createProjectTeam(Request $request){
            try {
                $projectRequest = 
ProjectRequest::create(['project_title' => $request->projectTitle, 
'client_id' => $request->projectClientId]);

                TeamMember::whereIn('email', $request->projectTeamEmails)
                ->update([
                    'project_request_id' => $projectRequest->id 
                ]);

                $projectTeam = TeamMember::where('project_request_id', $projectRequest->id)->get();

            return response()->json( [
                'success'=> true,
                'projectRequest' => $projectRequest,
                'projectTeam' => $projectTeam,
            ]);

            } catch(\Exception $e){
                return ['success' => false, 'message' => 'project team creation failed'];
            }

        }

Answer 1

// use DB; 
$data = UpdateModelName::find(1);
$copy = $data->replicate()->toArray();
// store data in new table
$newTable = new NewTableModel;
$newTable->insert($copy);