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?
-
Why would you need 2 tables to contain the same duplicate information?ypercubeᵀᴹ– ypercubeᵀᴹ2014年12月11日 17:56:34 +00:00Commented Dec 11, 2014 at 17:56
-
It is a practice for triggerAmanda_Q– Amanda_Q2014年12月12日 18:53:04 +00:00Commented Dec 12, 2014 at 18:53
-
what dbms do you use?miracle173– miracle1732014年12月14日 03:16:43 +00:00Commented Dec 14, 2014 at 3:16
1 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 INSERT
s, you are better off doing that in a single INSERT
:
INSERT IGNORE INTO warehouse_maine
SELECT * FROM Warehouse WHERE state='maine';
-
this works! thank you very much! The only confusion is what is IGNORE stands for?Amanda_Q– Amanda_Q2014年12月11日 16:08:30 +00:00Commented Dec 11, 2014 at 16:08
-
I used
INSERT IGNORE
in case the record for 'maine' was already there inwarehouse_maine
. This prevents entire duplicateshipperid
values.RolandoMySQLDBA– RolandoMySQLDBA2014年12月11日 16:28:44 +00:00Commented Dec 11, 2014 at 16:28