How to set up triggers in MySQL for copying a field

Question

I have an in-house application that I've put together, and I'm VERY new to SQL in general.

I have a table that contains most of our data. Let's pretend it looks like this.

Matrix

|Name       | Description        |
----------------------------------
|Server01   | First Server       | 

Now I've added another table and let's pretend it looks like this.

OtherInfo
|Name       | Price              |
----------------------------------
|Server01   | 100                |

I've done an initial copy of data from Matrix to OtherInfo. People will be adding entries to Matrix, and I want the Name from the Matrix to go into the OtherInfo table.

Basically, the Name field should always replicate what is in Matrix. If something gets changed or deleted on Matrix, the same thing should happen on OtherInfo.

How do I set this up?

Answer 1

Create the following four(4) Triggers on the Matrix table

DELIMITER $$   
CREATE TRIGGER matrix_ai AFTER INSERT ON Matrix FOR EACH ROW   
BEGIN   
    INSERT IGNORE INTO OtherInfo (Name) VALUES (NEW.Name);
END; $$   
CREATE TRIGGER matrix_bu BEFORE UPDATE ON Matrix FOR EACH ROW   
BEGIN
    DECLARE found_key,dummy INT;
    SELECT COUNT(1) INTO found_key FROM Matrix WHERE Name = NEW.Name;
    IF found_key = 1 THEN
        -- Abandon the Trigger of Name already exists
        SELECT data_length INTO dummy FROM information_schema.tables;
    END IF;
END; $$   
CREATE TRIGGER matrix_au AFTER UPDATE ON Matrix FOR EACH ROW   
BEGIN
    UPDATE OtherInfo SET Name=NEW.Name WHERE Name=OLD.Name;
END; $$   
CREATE TRIGGER matrix_ad AFTER DELETE ON Matrix FOR EACH ROW   
BEGIN   
    DELETE FROM OtherInfo WHERE Name=OLD.Name;
END; $$   
DELIMITER ;

What does each trigger do?

• matrix_ai INSERTs the new Name into OtherInfo after it is inserted into Matrix
• matrix_bu performs a validation to make sure you do not change the old Name into a New Name that already exists in the Matrix table
• matrix_au UPDATEs the old Name in OtherInfo to the new Name you updated in Matrix
• matrix_ad DELETEs the Name from OtherInfo that you just deleted from Matrix