BEFORE INSERT TRIGGER on one table but insert should be done on another table

Question

I have two tables say table1 and table2.

fields of table1 - id and count
fields of table2 -  id and count

both tables have same fields. Now I want to create BEFORE INSERT TRIGGER on table1 which will check if count = 2 then insert into table2 otherwise in table1.

I created the trigger but when I fire this query ..example-

insert into table1 (id, count) values (1323, 2);

then one row is inserted into table2 (this I want) and same row is inserted into table1 also.

I want row should be inserted in table1 or table not in both tables.

what I do to achieve this ?

you want to fir the insert trigger that will insert into table2 only.and trigger get fire only after /before insert into Tbale1 which u have mention above — user2001117, Feb 19 '13 at 07:05
yes..that I want..if value of count = 2 then and only then row should be inserted into table2 otherwise no any action. — Himanshu Matta, Feb 19 '13 at 07:07
possible duplicate of [How to abort INSERT operation in MySql trigger?](http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger) — Nanne, Feb 19 '13 at 07:14
Please, don't use all caps. That is not really polite, is it? And while you're at it, format your question a bit, using the buttons on the editor. Anyway, you have an `insert trigger` on a table, and you don't want to insert something in that table. That means you want to cancel the insert. Another word for cancel is 'abort'. So yes, I think this is duplicate. — Nanne, Feb 19 '13 at 07:43

score 0 · Answer 1 · answered Feb 19 '13 at 07:13

0

Try with this:

delimiter $$
CREATE TRIGGER myTrigger
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
IF NEW.count = 2 THEN
insert into table2 (id, count) values (New.id,New.Count);
ELSE
<<no action>>
END IF;
END$$

answered Feb 19 '13 at 07:13

user2001117

3,727
1
18
18

I am doing same thing but when I send count= 2 then it insert into table2 and table1. – Himanshu Matta Feb 19 '13 at 07:20
what is your sql query and you doing insert into table1. – user2001117 Feb 19 '13 at 07:23
Here is my code.. BEGIN IF (NEW.count = 2 ) THEN INSERT INTO db2.table2 SET id = NEW.id, count = NEW.count; END IF; END – Himanshu Matta Feb 19 '13 at 07:27
Trigger get fire only when you will insert into table1 – user2001117 Feb 19 '13 at 07:31
.....yes and this I want..I'll always insert into table1 but if count = 2 then insert into table2. – Himanshu Matta Feb 19 '13 at 07:35
This I don't understand. Your question says cleary (and yes, I read carefully) `not in both tables`. This will always insert into both tables. – Nanne Feb 19 '13 at 07:48
Can I avoid it or Should I think something different not trigger to achieve this ? – Himanshu Matta Feb 19 '13 at 07:51

score 0 · Answer 2 · edited May 23 '17 at 10:32

I'm not sure why you want this, so I suspect I might be giving advice on some sort of solution that should be fixed differently, but here we go.

As you can see in this question here it is not possible to neatly stop an insert. You could try and make an error so you'll not actually insert, but I think that is really painful.

What you can do is make a fake table dummytable and put your insert trigger there. Your row will always insert in the dummy table, but you either insert it in table1 or table2 according to your wishes. The meat of the trigger would look like

IF NEW.count = 2 THEN
INSERT INTO table2 (id, count) VALUES (New.id,New.Count);
ELSE
INSERT INTO table2 (id, count) VALUES (New.id,New.Count);
END IF;

You'll have to clean the dummytable now and then.

maybe you should post your actual problem. This kinda feels like the [xy problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) to me. — Nanne, Feb 19 '13 at 08:38

BEFORE INSERT TRIGGER on one table but insert should be done on another table

2 Answers2