Trigger oder Stored Procedure

[Tom]

Hello,

also erstmal grundsätzlich breche ich mir hier einen ab,  um in die MySQL-DB 5.0.32 einen Trigger hineinzubekommen.

Tabelle adresse
Feld    writecounter

Für's erste würde es mir genügen, wenn writecounter bei jedem Update um eins hochgezählt werden würde. Ist ein Trigger dafür nicht geeignet?

Wie muss das Statement aussehen, damit ich

• auf der Konsole
• über PHP
  in die Liste eintragen lassen kann?

Was hat es mit den Delimiters auf sich bei der Definition von Triggern?

Eigentlich sollte der Trigger und ggf. zugehörige Stored Procedure dafür sorgen, dass das Update abgelehnt wird, wenn der übergebene writecounter ungelich dem eingetragenen im Datensatz ist und dann anschließend bei Erfolg diesen um eins erhöhen.
Kann man das bei MySQL auch noch in einem Trigger unterbringen, oder benötigt man dann eine Stored Procedure

Letzte Frage zu diesem Thema:
Ich habe die Aufgabe bei jeder Tabelle.
Kann man da _eine_ universelle Stored Procedure erstellen, die man dann für alle Tabellen benutzen kann? Das Feld heißt überall writecounter

Ich hoffe, dass mir jemand auf die Sprünge helfen kann.
Ein Erfolg wäre schon, wenn ich überhaupt mal einen Trigger hinbekommen würde...

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Tom]

Hello,

hat das überhaupt schon mal einer außerhalb des Labors hinbekommen, so einen dämlichen Trigger zu setzen?

Ich habe über Google erstmal nur eine dürftige Anzahl von Hits zum Thema gefunden und dann keimt in mir der Verdacht, dass noch keiner von denen, die drüber geschreieben haben, das jemals selber ausprobiert haben...

***stinksauer***

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Rouven]

Hello,

ich habe nur gerade mal kurz ins Manual geschaut bzw. in meinem Hirn gekramt. Also vorweg: ja, ein Trigger dürfte für deine Zwecke geeignet sein. Ob das DBMS auch Trigger auf Schema-Ebene beherrscht, na ja, MySQL tuts wohl nicht, dementsprechend kommst du schätze ich um Trigger pro Tabelle nicht herum.

Zur Frage nach dem Delimiter: Das ist nur für das Beispiel. Wenn du dir den Trigger anschaust, dann stehen da mehrere SQL-Statements drin. Die müssen syntaktisch voneinander getrennt sein -> Notwendigkeit eines ";". Nun ist aber ";" gleichzeitig das Anweisungstrennzeichen für die Konsole, über die diese CREATE-Statements abgesetzt werden, die Parser würde also einen Syntaxfehler melden, weil mitten in der Definition das Statement zu Ende ist. Ergo: Wir tauschen kurz den Delimiter ";" durch "|" aus (gilt auf der Ebene der ganzen CREATE-Statements) und können damit ";" normal im Text verwenden.

Was deine konkrete Aufgabe angeht - hmh, mal so ins Blaue geschrieben:

CREATE TRIGGER w_counter
AFTER UPDATE ON adresse
FOR EACH NEW ROW BEGIN
   UPDATE adresse SET writecounter = writecounter + 1 WHERE id = NEW.id -- vorausgesetzt, deine Adresse hat eine Spalte 'id'
END

So, jetzt KÖNNTE es ein Problem mit Kaskadierung geben (Update->Trigger->Update->...), dazu hab ich allerdings gerade keine Lösung parat - MySQL 5 aber glaub ich auch kein Problem...

Was das Ausführen von zusätzlichen Prüfungen angeht verweise ich dich einfach mal an die Dokumentation , insbesondere den Kommentar 'Tim H on October 19 2006 10:16pm'

MfG
Rouven

[Rouven]

Hello,

Nachtrag: zugegeben, MySQL sträubt sich ganz schön, keine Ahnung warum.
Syntaktisch hab ich definitiv einen Fehler drin:
FOR EACH ROW BEGIN
aber auch ohne das NEW spielt die DB nicht mit...

MfG
Rouven

[Tom]

Hello,

Nachtrag: zugegeben, MySQL sträubt sich ganz schön, keine Ahnung warum.
Syntaktisch hab ich definitiv einen Fehler drin:
FOR EACH ROW BEGIN
aber auch ohne das NEW spielt die DB nicht mit...

Das geht mir genauso.
Ich habe alle auffindbaren Lösungsvorschläge durch.
Dabei gibt es sogar Fehlermeldungen.

Nur ein paar Beispiele

Delimiter |
CREATE TRIGGER w_counter
before UPDATE ON adresse
FOR EACH ROW BEGIN
set new.writecounter = 100

/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Delimiter | CREATE TRIGGER w_counter before UPDATE ON adresse FOR EACH ROW BE' at line 1 */

oder

CREATE TRIGGER w_counter
before UPDATE ON adresse
FOR EACH ROW BEGIN
  set new.writecounter = 100     ## hier geht das Statement natürlich noch weiter...

/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set new.writecounter = 100' at line 4 */

CREATE TRIGGER w_counter
before UPDATE ON adresse
FOR EACH ROW BEGIN
  set new.writecounter = 100;
end;

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Tom]

Hello,

Hello,

Nachtrag: zugegeben, MySQL sträubt sich ganz schön, keine Ahnung warum.
Syntaktisch hab ich definitiv einen Fehler drin:
FOR EACH ROW BEGIN
aber auch ohne das NEW spielt die DB nicht mit...

Das geht mir genauso.
Ich habe alle auffindbaren Lösungsvorschläge durch.
Dabei gibt es sogar Fehlermeldungen.

Nur ein paar Beispiele

Delimiter |
CREATE TRIGGER w_counter
before UPDATE ON adresse
FOR EACH ROW BEGIN
set new.writecounter = 100

/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Delimiter | CREATE TRIGGER w_counter before UPDATE ON adresse FOR EACH ROW BE' at line 1 */

oder

CREATE TRIGGER w_counter
before UPDATE ON adresse
FOR EACH ROW BEGIN
  set new.writecounter = 100     ## hier geht das Statement natürlich noch weiter...

/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set new.writecounter = 100' at line 4 */

CREATE TRIGGER w_counter
before UPDATE ON adresse
FOR EACH ROW BEGIN
  set new.writecounter = 100;
end;

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Tom]

Hello,

Ich krieg den Trigger nicht eingetragen bei MySQL.

Hat noch jemand eine Idee, wie man es machen muss?

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[dedlfix]

echo $begrüßung;

Ich krieg den Trigger nicht eingetragen bei MySQL.
Hat noch jemand eine Idee, wie man es machen muss?

Der phpMyAdmin hat, so wie es aussieht, noch Probleme beim Anlegen von Triggern und/oder Stored Procedures. Das Auftrennen der Statements an den ; ist hier kontraproduktiv. Es gelang mir aber zumindest einfache Trigger anzulegen:

create trigger trigger_name before insert on table_name for each row set new.feld_name=irgendwas

Trigger mit mehr als einem Statement, die BEGIN ... END enthalten, scheitern am ;. Die konnte ich zumindest über das CLI anlegen, wobei hier die Geschichte mit dem DELIMITER zu beachten ist.

delimiter #
  create trigger trigger_name before insert on table_name for each row begin set new.feld_name=irgendwas; end#
  delimiter ;

Für dein anderes Problem, des Abbrechens eines UPDATE- oder INSERT-Vorgangs, gibt es anscheinend keine offizielle Lösung. Der Tipp in den Userkommentaren, man solle ein RETURN FALSE verwenden, wird abgelehnt, weil RETURN nur für Funktionen verwendet werden kann. Es gibt ein LEAVE-Statement, aber das beendet nur den Trigger, nicht den gesamten Vorgang. Statements um einen Fehler zu erzeugen (raise oder throw und dergleichen) fand ich nicht. Meine Versuche, einen absichtlichen Fehler auszulösen, um ein Update abzubrechen, verliefen zwar letzlich erfolgreich, aber schön ist was anderes. Erster Versuch: Division durch 0. Sollte eigentlich immer was ganz Fatales sein. Aber denkste. Bei meiner Server-Konfiguration kam immer NULL raus. Diese NULL in Felder einzutragen, die NOT NULL stehen, machten daraus den Default-Wert und trugen den ein. Die Server-Konfiguration zu ändern scheint zwar erfolgversprechend zu sein, doch das ist - so nehme ich an, ich hab da nicht weitergeforscht - nicht jedem Anwender möglich. Einer UNSIGNED Spalte einen negativen Wert zu übergeben verursachte nur ein leises Jammern in Form einer Warnung inklusive stillschweigendem Umschreiben des eingefügten Wertes nach 0. Erst das Einfügen eines bereits vorhandenen Wertes in eine UNIQUE Spalte in einer anderen Tabelle brachte einen Fehler.

CREATE TABLE trigger\_error (
    x int(11) NOT NULL,
    UNIQUE KEY x (x)
  ) ENGINE=MyISAM;
  INSERT INTO trigger\_error VALUES (1);

delimiter #
  create trigger triggerx before insert on tablex for each row
  begin
    if old.feld <> new.feld then
      insert trigger_error values (1);
    end if;
  end#
  delimiter ;

Die DELIMITER-Statements müssen beim Ausführen der Statements über die MySQL-API, also z.B. mit den mysql-Funktionen aus PHP heraus, weggelassen werden, ebenso das abschließende #.

echo "$verabschiedung $name";

[Vinzenz Mai]

Hallo Tom,

also erstmal grundsätzlich breche ich mir hier einen ab,  um in die MySQL-DB 5.0.32 einen Trigger hineinzubekommen.

Tabelle adresse
Feld    writecounter

Für's erste würde es mir genügen, wenn writecounter bei jedem Update um eins hochgezählt werden würde. Ist ein Trigger dafür nicht geeignet?

Ja. Kannst Du tun. Da Du den neuen Datensatz verändern willst,
musst Du einen BEFORE-UPDATE-Trigger verwenden, wie Dir das Handbuch verrät:

<zitat>
In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or that are used to update a row.
</zitat>

CREATE TRIGGER w_counter  
BEFORE UPDATE ON adresse  
FOR EACH ROW SET NEW.writecounter = NEW.writecounter + 1

läuft problemlos (MySQL 5.0.37 unter Windows XP SP2)

Wie muss das Statement aussehen, damit ich

• auf der Konsole

Genau dieses Statement.

• über PHP

Keine Ahnung. Ich teste SQL-Statements selten mit PHP. Sollte aber "genau dieses Statement" sein

in die Liste eintragen lassen kann?

Ach ja: Erster Test mit MySQL-Query-Browser :-)

Freundliche Grüße

Vinzenz

[Tom]

Hello Vinzenz,

In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or that are used to update a row.

Ich krieg den Trigger gar nicht angemeldet, weder mit "normalem Namen", noch mit datenwankweit eindeutigem Namen "database.trigger_name", so wie ich es aus einem Stückchen Kofler (aus dem Netz) herauslesen konnte für alle DB > 5.0.1

Welches Recht benötige ich denn für das Setzen von Triggern?
Kann es sein, dass ich mir da eine Bremse eingabaut habe, weil ich meinen "superuser" nur mit "grant all ... on *.* to superuser identified ... with grant option" angelegt habe?
Und Root habe ich für "nicht loocalhost" ausgeschaltet...

Wie muss ich einen User anlegen, der die notwendigen Rechte hat?
Das ist die einzige Chance, die ich noch sehe.

Ich habe die Statemants über die ssh-Konsole und über Heidi-SQL ausgetestet. Alles Andere funktioniert ja auch.

Die Fehlermeldungen sagen aber nichts von "access denied" oder ähnlich

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Vinzenz Mai]

Hallo

Welches Recht benötige ich denn für das Setzen von Triggern?

Du benötigst das SUPER-Privileg. Das ist verdammt viel, weil es ein globales Recht ist.

Kann es sein, dass ich mir da eine Bremse eingabaut habe, weil ich meinen "superuser" nur mit "grant all ... on *.* to superuser identified ... with grant option" angelegt habe?

Sollte reichen :-)

Die Fehlermeldungen sagen aber nichts von "access denied" oder ähnlich

Tja, die kriegst Du, wenn der Benutzer _nicht_ über das SUPER-Privileg verfügt.

Du kannst einem Benutzer auf folgende Weise die Rechte auf eine Datenbank beschränken - und ihm dennoch das SUPER-Privileg zuweisen:

GRANT ALL PRIVILEGES ON <datenbank>.* TO <benutzer>;  
GRANT SUPER ON *.* TO <benutzer>;

(aus den Benutzerkommentaren von http://dev.mysql.com/doc/refman/4.1/en/grant.html)

Vergiß bitte nicht: FLUSH PRIVILEGES :-)
bevor Du als der neue Benutzer was machen willst.

Freundliche Grüße

Vinzenz

[Tom]

Hello,

es lag oder liegt eindeutig an HeidiSQL (Ver 3.0 RC4)

Vielleicht gibt es auch einen Trick in HEIDI, den ich übersehen habe bisher.

Auf der Konsole funktioniert es sowohl mit root, als auch mit meinem "superuser"

mysql> delimiter #

// Delimiter für das Ende der Befehlseingabe steht nun auf #

mysql> create trigger inc_x
    -> before update
    -> on trigger_error
    -> for each row begin
    ->   if (old.x > 10) then
    ->     set new.x = 0;
    ->   else
    ->     set new.x = new.x *2;
    ->   end if;
    -> end; #

// Befehl wird ausgeführt

mysql> select * from trigger_error; #           // hier sowohl ; für Ende des einzelnen Statements
+---+                                           // als auch    # für das Ende der Eingabe von
| x |                                           // Statements, weil delimiter noch nicht wieder
+---+                                           // zurückgestellt war (absichtlich)
| 0 |
| 1 |
| 2 |
+---+
3 rows in set (0.00 sec)

Nun habe ich endlich begriffen, wie das mit dem Delimiter gemeint ist.
Das DBMS kann damit nichts anfangen. Das ist nur für die Eingabe in der Konsole.

Wie kann ich nun dem Trigger beibringen, dass er das Update ablehnt bei einer bestimmten Bedingung  und wie bekomme ich eine qualifizierte Fehlermeldung zurück zum Aufrufer des Update?

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Vinzenz Mai]

Hallo Tom,

mysql> delimiter #

// Delimiter für das Ende der Befehlseingabe steht nun auf #

Hinweis:

  
-- Zeilenkommentare werden in SQL mit -- eingeleitet,  
-- nicht mit //. 

Wie kann ich nun dem Trigger beibringen, dass er das Update ablehnt bei einer bestimmten Bedingung

Wie ich bereits sagte: in MySQL derzeit gar nicht.

und wie bekomme ich eine qualifizierte Fehlermeldung zurück zum Aufrufer des Update?

Wie dedlfix bereits vermutete: in MySQL derzeit gar nicht.
Wie ich bereits schrieb: Nimm eine Stored Procedure.

Freundliche Grüße

Vinzenz

[Tom]

Hello,

-- Zeilenkommentare werden in SQL mit -- eingeleitet,

Danke.

Wie kann ich nun dem Trigger beibringen, dass er das Update ablehnt bei einer bestimmten Bedingung

Habe eine fiese Methode gefunden:

delimiter #
create trigger forbid_x
before Update
on trigger_error
for each row begin
  if (old.x = 3)
  then call udf_break(1);
  end if;
end; #
delimiter ;

Query OK, 0 rows affected (0.01 sec)

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Tom]

Hello,

kann man denn nicht gezielt eine Exception auslösen, die das aktuelle Update für die ROW abbricht aber mit den anderen fortfährt?

Oder ein LOOP?

Ich kriegs nur nicht hin...

Ist wohl nicht vorgesehen?

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[Vinzenz Mai]

Hallo Tom,

Eigentlich sollte der Trigger und ggf. zugehörige Stored Procedure dafür sorgen, dass das Update abgelehnt wird, wenn der übergebene writecounter ungelich dem eingetragenen im Datensatz ist und dann anschließend bei Erfolg diesen um eins erhöhen.
Kann man das bei MySQL auch noch in einem Trigger unterbringen,

Nach meinem Wissensstand: Nein.

oder benötigt man dann eine Stored Procedure

Ja. Und mit einer Stored Procedure - nicht einer Stored Function - solltest Du in der Lage sein, diese Procedure so zu schreiben, dass sie in Deiner DB universell anwendbar ist, siehe Handbuchabschnitt Restrictions on Stored Routines and Triggers:

<zitat>
The following statements are disallowed:

[...]

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE). Implication: You cannot use dynamic SQL within stored routines (where you construct dynamically statements as strings and then execute them). This restriction is lifted as of MySQL 5.0.13 for stored procedures; it still applies to stored functions and triggers.
</zitat>

Freundliche Grüße

Vinzenz

[Tom]

Hello,

nur zur Vervollständigung des Threads:
kleine Power Point Präsentation zu Triggers und Stored Procedures

http://www.fhso.ch/~reber/DB2T05/DB05-StoredProcedures.PPT

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom