MySQL扩展表需求：基于s_ogr与s_kontrol生成新表s_kontrol_k

Alright, let's break this down into actionable steps to build your new s_kontrol_k table, populate it with existing data, and keep it in sync with your existing tables.

1. Create the New Table s_kontrol_k #

First, we'll define the table structure with all your required fields. I'll use MySQL-style syntax for the auto-incrementing primary key—adjust this if you're using PostgreSQL (use SERIAL), SQL Server (use IDENTITY(1,1)), or another database:

CREATE TABLE s_kontrol_k (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ogr_id INT NOT NULL,
    ogr_numara VARCHAR(50) NOT NULL, -- Match the data type from s_ogr.numara
    ogr_ad VARCHAR(100) NOT NULL, -- Match the data type from s_ogr.ad
    tur_id INT,
    durum_id INT,
    tarih DATE, -- Or DATETIME if you need time values
    egitim_id INT,
    yurt_id INT,
    -- Optional: Add foreign keys for referential integrity
    FOREIGN KEY (ogr_id) REFERENCES s_ogr(id),
    FOREIGN KEY (tur_id) REFERENCES your_tur_table(id), -- Replace with actual table name
    FOREIGN KEY (durum_id) REFERENCES your_durum_table(id) -- Replace with actual table name
);

2. Populate s_kontrol_k with Existing Data #

Since s_kontrol already has a large dataset, we'll use a JOIN to pull student details from s_ogr and combine them with records from s_kontrol:

INSERT INTO s_kontrol_k (ogr_id, ogr_numara, ogr_ad, tur_id, durum_id, tarih, egitim_id, yurt_id)
SELECT
    sk.ogr_id,
    so.numara AS ogr_numara,
    so.ad AS ogr_ad,
    sk.tur_id,
    sk.durum_id,
    sk.tarih,
    sk.egitim_id,
    sk.yurt_id
FROM s_kontrol sk
INNER JOIN s_ogr so ON sk.ogr_id = so.id;

• Use INNER JOIN if you only want records where the student exists in s_ogr. If there are s_kontrol entries with no matching s_ogr record, switch to LEFT JOIN and handle NULL values for ogr_numara/ogr_ad as needed.

3. Keep s_kontrol_k Synced with Future Changes #

To ensure s_kontrol_k stays up-to-date when data is added, updated, or deleted in s_kontrol or s_ogr, you'll need triggers. Here's an example for MySQL:

Trigger for New s_kontrol Records #

DELIMITER //
CREATE TRIGGER trg_s_kontrol_after_insert
AFTER INSERT ON s_kontrol
FOR EACH ROW
BEGIN
    INSERT INTO s_kontrol_k (ogr_id, ogr_numara, ogr_ad, tur_id, durum_id, tarih, egitim_id, yurt_id)
    SELECT
        NEW.ogr_id,
        so.numara,
        so.ad,
        NEW.tur_id,
        NEW.durum_id,
        NEW.tarih,
        NEW.egitim_id,
        NEW.yurt_id
    FROM s_ogr so
    WHERE so.id = NEW.ogr_id;
END //
DELIMITER ;

Trigger for Updated s_kontrol or s_ogr Records #

DELIMITER //
CREATE TRIGGER trg_s_kontrol_after_update
AFTER UPDATE ON s_kontrol
FOR EACH ROW
BEGIN
    UPDATE s_kontrol_k
    SET
        tur_id = NEW.tur_id,
        durum_id = NEW.durum_id,
        tarih = NEW.tarih,
        egitim_id = NEW.egitim_id,
        yurt_id = NEW.yurt_id
    WHERE ogr_id = NEW.ogr_id;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_s_ogr_after_update
AFTER UPDATE ON s_ogr
FOR EACH ROW
BEGIN
    UPDATE s_kontrol_k
    SET
        ogr_numara = NEW.numara,
        ogr_ad = NEW.ad
    WHERE ogr_id = NEW.id;
END //
DELIMITER ;

Trigger for Deleted s_kontrol Records #

DELIMITER //
CREATE TRIGGER trg_s_kontrol_after_delete
AFTER DELETE ON s_kontrol
FOR EACH ROW
BEGIN
    DELETE FROM s_kontrol_k
    WHERE ogr_id = OLD.ogr_id;
END //
DELIMITER ;

Adjust these triggers to match your database's syntax if you're not using MySQL (e.g., PostgreSQL uses CREATE FUNCTION paired with TRIGGER).

内容的提问来源于stack exchange，提问作者Samir