Return to Revisions

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 FROM Matrix WHERE Name = NEW.Name;
    IF found = 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=NEW.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