Trigger problem please help

[cjtjamandra]

i have created an after insert trigger but it is not working or not triggered when i use an insert select statement like this:

insert into cc(id) (select num from counter);

please help....

[Fumigator]

Does the trigger work on a regular insert?

[cjtjamandra]

At last... a reply... among the 3 forums i posted it finally..

It is working when i use an ordinary insert statement. but it is not when i use an insert select statement... have any idea why?:confused:

[Fumigator]

Well... can we see the CREATE TRIGGER syntax you used? And perhaps your error log contains something useful about what's going wrong. I don't see any reason why both insert methods wouldn't fire the trigger. Also make sure the insert itself actually worked. ;)

[cjtjamandra]

there is no error, that's why it's hard to debug.

This is my trigger code:

CREATE TRIGGER ccinc AFTER INSERT ON cc
FOR EACH ROW BEGIN
UPDATE counter SET num = num + 1 WHERE counter.loc = NEW.location;
END;


i have tested and tried a normal insert query and the trigger works. but using insert select, it doesn't work.

[cjtjamandra]

when inserting a record to table "cc" , iam accessing the table "counter" which is updated in my trigger. do you think this is the reason why it's not working?

[Fumigator]

No that shouldn't be a problem. You're doing AFTER INSERT, so the insert should use the current value in counter.num, then the trigger should update the counter table. Although... you are using "NEW.location", but you aren't assigning a value to that column in your INSERT statement. So how are you identifying which row in the counter table is going to get updated? Is there a default value on the cc.location column?