what is the syntax for insert trigger?

Question

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?

• ypercubeᵀᴹ: Why would you need 2 tables to contain the same duplicate information?
• Amanda_Q: It is a practice for trigger
• miracle173: what dbms do you use?

Answer 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';

• Amanda_Q: this works! thank you very much! The only confusion is what is IGNORE stands for?
• RolandoMySQLDBA: I used INSERT IGNORE in case the record for 'maine' was already there in warehouse_maine. This prevents entire duplicate shipperid values.