triggers

[RussellReal]

basically I had something like this:

DELIMITER ~
CREATE TRIGGER updTrgr BEFORE INSERT ON tester
FOR EACH ROW BEGIN
    UPDATE tester SET tester.order = tester.order + 1 WHERE new.order <= tester.order;
END;~
DELIMITER ;

 

now this works (or the one that I used last time did)

 

but I get an error message on inserting.. it says something like..

 

#1442 - Can't update table 'tester' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

 

so how do I do this? :S

[mikosiko]

I just posted the answer in other post 

 

"A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

 

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

[RussellReal]

ok I Just read most of that article and that doesn't help me, how would I do this WITH A TRIGGER to achieve the same effect I am looking for

 

Thanks for the help though much appreciated

[mikosiko]

ok I Just read most of that article and that doesn't help me, how would I do this WITH A TRIGGER to achieve the same effect I am looking for

 

Thanks for the help though much appreciated

 

well... seems that you didn't read enough or you don't have a good understanding about how triggers work... which is ok.. we all started in some point (no offense intended)

 

lets see...

what you wrote was:

DELIMITER ~
CREATE TRIGGER updTrgr BEFORE INSERT ON tester
FOR EACH ROW BEGIN
    UPDATE tester SET tester.order = tester.order + 1 WHERE new.order <= tester.order;
END;~
DELIMITER ;

 

just look the code... and ask yourself

- why if what I wrote is a BEFORE INSERT trigger I'm trying to UPDATE a row that is not there? (you are just inserting that row)... this doesn't make sense.

 

seems that what you want to do is

DELIMITER ~
CREATE TRIGGER updTrgr BEFORE UPDATE ON tester
FOR EACH ROW BEGIN
    IF NEW.order <= OLD.order THEN
        SET NEW.order = OLD.order + 1;
    ENDIF;
END;~
DELIMITER ;

 

 

[RussellReal]

ok I Just read most of that article and that doesn't help me, how would I do this WITH A TRIGGER to achieve the same effect I am looking for

 

Thanks for the help though much appreciated

 

well... seems that you didn't read enough or you don't have a good understanding about how triggers work... which is ok.. we all started in some point (no offense intended)

 

lets see...

what you wrote was:

DELIMITER ~
CREATE TRIGGER updTrgr BEFORE INSERT ON tester
FOR EACH ROW BEGIN
    UPDATE tester SET tester.order = tester.order + 1 WHERE new.order <= tester.order;
END;~
DELIMITER ;

 

just look the code... and ask yourself

- why if what I wrote is a BEFORE INSERT trigger I'm trying to UPDATE a row that is not there? (you are just inserting that row)... this doesn't make sense.

 

seems that what you want to do is

DELIMITER ~
CREATE TRIGGER updTrgr BEFORE UPDATE ON tester
FOR EACH ROW BEGIN
    IF NEW.order <= OLD.order THEN
        SET NEW.order = OLD.order + 1;
    ENDIF;
END;~
DELIMITER ;

 

nooo you misunderstand man <3 I didn't read too far you're right but I'm not trying to do what you're doing

 

I'm trying to update ALL OTHER rows in the table?

 

for example..

 

order

1

2

3

4

5

 

I add a row with the order "2"

 

I want it to do this..

 

order

1

2

2 -> 3

3 -> 4

4 -> 5

5 -> 6

 

understand?

[mikosiko]

gotcha....  so.. my first answer was correct..... that is not possible to do that WITH A TRIGGER  for this reason:

 

"A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."