0

I am very new to sql. feel a little stupid to ask this question

Say I have this table Warehouse(shipperid, name , state). Then I define another table warehouse_maine (shipperid, name, state) which should only contain the warehouse located in maine. after I insert all the data for warehouse. I want to create a trigger for warehouse_maine, which insert the warehouse data only in maine state.

CREATE TRIGGER TRIGGER01 ON WAREHOUSE_MAINE
FOR INSERT
AS select * from warehouse where warehouse.state= maine

This is what I thought but it's obviously wrong. what should be the syntax after keyword AS?

3
  • Why would you need 2 tables to contain the same duplicate information? Dec 11 '14 at 17:56
  • It is a practice for trigger
    – Amanda_Q
    Dec 12 '14 at 18:53
  • what dbms do you use?
    – miracle173
    Dec 14 '14 at 3:16
1

What you are looking for is to actually do the INSERT into warehouse_maine after Warehouse

DELIMITER $$
DROP TRIGGER IF EXISTS Warehouse_InsertAfter $$
CREATE TRIGGER Warehouse_InsertAfter AFTER INSERT ON Warehouse
FOR EACH ROW
BEGIN
    IF NEW.state = 'maine' THEN
        INSERT IGNORE INTO warehouse_maine (shipperid, name, state)
        VALUES (NEW.shipperid, NEW.name, NEW.state);
    END IF;
END;
DELIMITER ;

This trigger will do the job. If you are doing a bulk INSERT of rows into the Warehouse table, this trigger will slow things down a bit. If you plan to do such bulk INSERTs, you are better off doing that in a single INSERT:

INSERT IGNORE INTO warehouse_maine
SELECT * FROM Warehouse WHERE state='maine';
2
  • this works! thank you very much! The only confusion is what is IGNORE stands for?
    – Amanda_Q
    Dec 11 '14 at 16:08
  • I used INSERT IGNORE in case the record for 'maine' was already there in warehouse_maine. This prevents entire duplicate shipperid values. Dec 11 '14 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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