trigger in mysql 5.0

[feuervogel]

hallo,

ich habe eine tabelle test:

id | a | b

und ich möchte, das maximal drei gleiche einträge in der spalte b existieren. ich habe hier folgenden trigger:

DELIMITER |
DROP TRIGGER b_count_check;
CREATE TRIGGER b_count_check BEFORE INSERT ON test
FOR EACH ROW BEGIN
DECLARE amount INT;
SELECT COUNT('NEW.b') INTO amount FROM `test`;
IF amount <= 3 THEN
INSERT INTO `test` SET `a` = 'NEW.a',`b` = 'NEW.b';
END IF;
END; |

DELIMITER ;

dieser trigger wird auch ohne fehlermeldung installiert, will ich aber nun etwas einfügen, kommt folgende fehlermeldung:

ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

wo liegt das problem?

[steffenk]

im Bugtracker von MySQL gibts dazu folgende Anmerkung:

I dont think this is a bug. Its clearly stated in MySql manual under trigger limitations
that one can not select from the table on which the trigger is firing from within that
trigger.
As far as I know, this not allowed in any database, for example Oracle will generated
table mutating error under similar circumstances.

[Stuck Mojo]

naja... sollte das so ablaufen, gerät das Teil doch in eine Endlosschleife, da der Trigger IMMER vor dem Insert aufgerufen wird. Es also so gesehen zu keinem wirklichen Insert kommt bis die Trigger durch sind...

Zusätzlich musst du einfach nur ein RETURN FALSE in deiner Bedingung setzten und das Ergebnis sollte passen...

Allerding denke ich, dass die NEW.b usw. in single quotes fehlerhaft sind.

...und gerade kommt Steffens Kommentar reingeflogen: Das kann natürlich auch sein :-)

Gruß
Jan

[feuervogel]

danke erstmal für die antworten, das klingt logisch.

@stuck mojo: hm, naja, so:

DELIMITER |
DROP TRIGGER b_count_check;
CREATE TRIGGER b_count_check BEFORE INSERT ON test
FOR EACH ROW BEGIN
DECLARE amount INT;
SELECT COUNT('NEW.b') INTO amount FROM `test`;
IF amount > 3 THEN
RETURN FALSE;
END IF;
END; |

DELIMITER ;

? dann erhalte ich nämlich beim erstellen des triggers das hier:

ERROR 1313 (42000) at line 2: RETURN is only allowed in a FUNCTION

[Stuck Mojo]

ach... hatte kurz in den User-Comments in dem Manual geschaut und da hatte einer genau diesen Tip gegeben... Aber andere haben dies dann doch wiederlegt. Dann passts wohl doch nicht.

Schau mal selber: http://dev.mysql.com/doc/refman/5.0/...e-trigger.html

...oder du überlegst dir einen anderen Weg.

[feuervogel]

das einzige was mir jetzt noch einfällt ist ein trigger nach dem insert der dann die zeile wieder löscht...ich war zwar in den vorlesungen datenbanksysteme 1 + 2 gar nicht mal so schlecht, aber so wirklich erinnern kann ich mich halt auch nicht mehr :-)

edit: löschen geht auch nicht, ist ja wieder die gleiche tabelle => gleiche fehlermeldung wie oben. gnarf. naja, dann gibts eben einen workaround.

[feuervogel]

so, hier der korrektheits halber noch mal der getestete und für richtig befundene trigger:

DELIMITER |
DROP TRIGGER b_count_check;
CREATE TRIGGER b_count_check BEFORE INSERT ON test
FOR EACH ROW BEGIN
DECLARE amount INT;
SELECT COUNT(`id`) INTO amount FROM `test` WHERE `b` = NEW.b;
IF amount > 5 THEN
SET NEW.flag = 2;
END IF;
END; |

DELIMITER ;

von zeit zu weit werden die einträge mit flag = 2 gelöscht und sonst nur die mit flag < 2 genutzt. das ist jetzt mein workaround.