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?

miracle173
7,79728 silver badges42 bronze badges
asked Dec 11, 2014 at 14:41
3
  • Why would you need 2 tables to contain the same duplicate information? Commented Dec 11, 2014 at 17:56
  • It is a practice for trigger Commented Dec 12, 2014 at 18:53
  • what dbms do you use? Commented Dec 14, 2014 at 3:16

1 Answer 1

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';
answered Dec 11, 2014 at 16:00
2
  • this works! thank you very much! The only confusion is what is IGNORE stands for? Commented Dec 11, 2014 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. Commented Dec 11, 2014 at 16:28

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.