Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Here is a question: When you move the Name into OtherInfo from Matrix, what will the default value be for Price ? –  RolandoMySQLDBA Dec 23 '11 at 19:34
    
null. There is a web front end that displays this information (using PHP Maker) so I want to be able to go to the OtherInfo table and see that the information hasn't been gathered about the price. –  AaronJAnderson Dec 23 '11 at 19:43

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
I get an error on the matrix_bu trigger. 0 15:19:55 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; Error Code: 1327. Undeclared variable: found –  AaronJAnderson Dec 23 '11 at 20:21
    
I had found instead of found_key. Fixed it. Try again !!! –  RolandoMySQLDBA Dec 23 '11 at 20:33
    
This version generates a lot of output ;) Here's a public link to it. dl.dropbox.com/u/2209048/trigger_output.txt –  AaronJAnderson Dec 23 '11 at 20:55
    
I fixed the matrix_ad trigger. It is not supposed to say NEW.Name but OLD.Name. I also fixed the other found_key issue in matrix_bu. Try again !!! –  RolandoMySQLDBA Dec 23 '11 at 20:59
    
Good work, sir! You've given me a lot of information to learn from. I really appreciate it. I think I will do some tweaking to this. ie, I don't need matrix_bu because the web-app won't allow duplicates anyway. I also think that matrix_ad needs to be a BEFORE style trigger because the web-app gives an error something like "Can't delete record because it exists in this other table" - Either way, brilliant work. I can't thank you enough. –  AaronJAnderson Dec 24 '11 at 2:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.