SQL Noob wagt sich an einen Trigger

Hy,
also ich hab jetzt ma ein bisschen in der Deutschen Doku gelesen und hier im Forum so über Trigger, wollte mal wissen ob ich das richtig verstanden habe und hab auch noch ein paar kleine Fragen.

Also ein Trigger ruft immer eine Funktion auf, die dann handelt.

Diese Funktion kann Werte alles mit den Werten machen.

Ein AfterTrigger wartet bis der “Auslöser” fertig ist und ruft dann die Funktion auf

Ein BeforeTrigger schnapp sich zum Beispiel beim “Instert” von Daten immer gleich die Datei und bearbeitet oder ergänzt sie und schreibt sie erst dann in die Datenbank.


Doch wie funktioniert die Variablenübergabe in so einer SQL-Funktion?

Mal angenommen ich möchte:


Wert1,Wert2 importieren, möchte aber, dass mir die Funktion noch einen Wert3 ausspuckt (Also " (Wert1+Wert2) as Sum) und mit in die Tabelle schreibt in die Zeile Summe. Ich also der Funktion sage, ich rechne hier oben ´was aus dass ich Sum nenne und das sollst du dann in die Zelle “Wert3” eintragen und das jedes mal, wenn eine neue Zeile “Insert” wird.


Kurz:

Insert into tabelle1 (
Wert1,Wert2)
Values (
5,2)

löst einen Trigger aus,dessen Funktion:

(Wert1+Wert2) as Sum

Insert into tabelle1 (
Wert3)
Values
(Sum)


macht.


Wie übergebe ich die Variablen, also dass er das auch für jede Zeile durchführt und nicht einfach ne neue Zeile anfängt in der Wert1 und Wert2 leer sind und nur Wert3 steht
oder andersrum …


Gruß BoOnOdY

:slight_smile:

Hallo,

gib dir mal diese Dokumentation:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html

Du kannst in einer Triggerfunktion auf die Spalten NEW.spaltenname und OLD.spaltenname zugreifen, abhängig davon, was für ein Trigger das ist.

Bei einem BEFORE Trigger kannst du Werte in NEW.spaltenname schreiben, damit änderst du die Werte, die in die Tabelle geschrieben werden. Bei einem AFTER Trigger kannst du die Werte zwar auch ändern, aber da die Daten schon geschrieben sind, findet das keine Beachtung mehr.

Was du also suchst, ist eine Zuweisung an NEW.

CREATE OR REPLACE FUNCTION f_destination() RETURNS trigger AS $$
BEGIN
(SELECT destination.dialcode
           FROM destination
          WHERE call_test.ausgangsnummer ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1) AS "location"
INSERT INTO Call_test (destination) VALUES (NEW.[location]);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ich will mit dem “As” die variable Location definieren damit ich se einfügen kann, der meint aber die Klammern um das ganze wären Syntax fehler und wenn ich es ohne klammern schreibe ist der Syntaxfehler bei dem “As”,


So wieder zu meiner Frage, wie definiere ich variablen?

Variable Location definieren? Bahnhof …

Du sagst doch schon, das die Funktion einen Datentyp vom Typ “Trigger” zurückliefert.

Was für Variablen willst du denn definieren …

Also ich glaube ich muss noch ein NEw ergänzen …

CREATE OR REPLACE FUNCTION f_destination() RETURNS trigger AS $$
BEGIN
(SELECT destination.dialcode
           FROM destination
          WHERE NEW.[call_test.ausgangsnummer] ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1) AS "location"
INSERT INTO Call_test (destination) VALUES (NEW.[location]);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

So ich will das ich also:

Var location = (SELECT destination.dialcode
FROM destination
WHERE NEW.[call_test.ausgangsnummer] ~~ (destination.dialcode || ‘%’::text)
ORDER BY char_length(destination.dialcode) DESC
LIMIT 1)

und dann will ich
diesen Wert location in die Datenbank packen, zusammen mit den anderen Werten, die ja schon in der Auslöseaktion eingetragen werden

Ja, langsam verstehe ich, was du willst.

Magst du nicht erst mal in die Dokumentation lesen zu plpgsql? Das mag dir hier niemand abnehmen und das wäre die Grundlage, um einen Trigger schreiben zu können.

Gut sorry, bin ich schon drann und find da nichts so recht,
also dazu hab ich auch noch ne Frage:

Wenn man eine Aktion macht die einen Trigger auslöst, dann hat führt der doch weiter die Aktion aus oder?

Also wenn ich ihm sage:

Wenn jemand Daten in die Tabelle einfügt, dann hänge noch eine Zeile drann.

Dann muss ich der Funktion doch nur sagen, dass sie die Zeile dran hängen soll und das Einfügen der restlichen Datensätze ist ihr egal, oder hab ich das falsch verstanden?

Da fehlt auch ein ; am Ende vom Select, aber nicht ist noch nicht das ganze Problem. Wenn Du innerhalb einer plpgsql-Function mit dem Result eines Selects arbeiten willst, suchst Du SELECT INTO.
Lies dazu bitte http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS



So wieder zu meiner Frage, wie definiere ich variablen?

Mit DECLARE am Anfang.
http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html

Tut mir leid, dass ich mir da so schwer tue :astonished:

Aber dann müsste ja es entweder so aussehen

CREATE OR REPLACE FUNCTION f_destination() RETURNS trigger AS $$
BEGIN
SELECT INTO call_test.destination destination.dialcode
           FROM destination
          WHERE (NEW.[call_test.ausgangsnummer]) ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1 AS "location" ;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Da sagt er der Fehler würde bei den INTO liegen, ersetzt das Select into das Insert into?


oder mit einem Declare:

CREATE OR REPLACE FUNCTION f_destination() RETURNS trigger AS $$
BEGIN
declare ort alias for (
SELECT destination.dialcode
           FROM destination
          WHERE (NEW.[call_test.ausgangsnummer]) ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1) ;
INSERT INTO Call_test (destination) VALUES (ort);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

oder?

ohne groß zu testen:

CREATE or REPLACE function … as $$
DECLARE
my_var text;
BEGIN




Steht aber auch so in den Bleistiften…


Andreas

CREATE TRIGGER desti
After INSERT
  ON call_test
  FOR EACH ROW
  EXECUTE PROCEDURE f_destination();



CREATE OR REPLACE FUNCTION f_destination()
  RETURNS "trigger" AS
$BODY$
declare ort text;
begin
SELECT destination.dialcode
           FROM destination, call_test
          WHERE (call_test.ausgangsnummer) ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1;
INSERT INTO Call_test (destination) VALUES (ort);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

jetzt sagt der mir “Select query has no destination for result data”

Sorry ich kann die Fehlermedlungen noch nicht richtig deuten …

Ach jetzt hab ich das kapiert …
mit einem select into definiert man also so eine Variable …

CREATE TABLE call_test
(
  identify serial NOT NULL,
  callrefnummer varchar(50) NOT NULL,
  datum date,
  uhrzeit time,
  eingangsport int4,
  ausgangsport int4,
  eingangsnummer text,
  ausgangsnummer text,
  dauer int4,
  releasecode varchar(50),
  dialcode text,
  CONSTRAINT call_test_pkey PRIMARY KEY (identify)
) 
WITHOUT OIDS;
ALTER TABLE call_test OWNER TO tim;
 
 
-- Trigger: desti on call_test
-- DROP TRIGGER desti ON call_test;
CREATE TRIGGER desti
  BEFORE INSERT
  ON call_test
  FOR EACH ROW
  EXECUTE PROCEDURE f_destination();



CREATE OR REPLACE FUNCTION f_destination()
  RETURNS "trigger" AS
$BODY$declare ort text;
begin
SELECT into ort destination.dialcode
           FROM destination
          WHERE (NEW.ausgangsnummer) ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1;
INSERT INTO Call_test (destination) VALUES (ort);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION f_destination() OWNER TO tim;



ausgeben tut er mir:

SQL Statement "Insert into call_test(destination) values ($1) "



Ich hab doch bestimmt nur noch nen kleinen Logigfehler drinnen oder?

Sorry für die Mühe die ich euch mache, aber ich find für so was nirgend richtig Beispiele …

wenn das ne normale Programmiersprache wäre würde ich tippen, dass da irgend eine “Schleife” drummherum ist und die irgendwie um das “INsert into” liegt und sich immer wieder ausführt und so jedesmal den gleichen wert ausgibt …

Keine Ahung warum das nicht funktioniert

viele Grüße BoOnOdY :astonished:

Es strengt langsamm an…

Ich wollte Dir heute schon fast antworten, aber du hast dem ads ja signalisiert, daß nun alles okay wäre, also hielt ich die Finger still.

ich hatte mal just for fun dieses probiert:

create or replace function f_test() returns trigger as $$
declare out text; 
begin select into out name from foo where id=10; 
raise notice '%', out; 
insert into bar values (out); 
return new; 
end; 
$$language plpgsql;

Und das tut, was es soll.

Du sagst uns nun, daß etwas nicht tut, was es soll. Aber Dinge wie Fehlermeldungen sind scheinbar top secret. Und das, was ads vorschlug, also den ganzen Kram zu entsorgen und es noch mal sauber zu machen, hast Du auch in den Wind geschlagen.

Also, es ist anstrengend, Dir zu helfen. Am Willen fehlt es nicht.


Andreas, müde für heute…

Es tut mir echt leid dass es so anstrengend ist mir zu helfen :astonished: nur es ist so:

Ich setze mich an so ne Sache und denke nach wie ich se verwirklichen kann, dann fange ich an es in einer logig aufztuschreiben die einer funktion ähnelt und dann ersetze ich die begriffe durch die Spracheigene Syntax und dann kommts … mir fällt die Syntax nicht ein weil ich noch nicht viel Erfahrung habe… aber irgendwie in so vielen Programmiersprachen schon ma reingeschnuppert habe,… da gehts in meinem Kopf manchmal drunter und drüber…
dann gehts los, ich suche in einem Buch das allgemein über SQL ist und nur für Einsteiger und finde natürlich kaum was. Dann mache ich mich auf die Suche nach der Dokumentation, die ja nur auf Englisch wirklich vollständig ist und lese das durch aber finde auch kein Beispiel was mir sagt wie ich mein Problem umsetzten soll …
Also gehe ich ins Forum und frage Leute mit Erfahrung.
Doch anscheinend stelle ich meine Fragen irgendwie doof.

Zu den Fehlermeldungen ist es so:
Ich arbeite zum Importieren mit einem Phytonscript und das gibt mir dann immer Zeilen aus die ich nicht deuten kann, sonst würde ich euch liebend gerne die Fehlermedlung sagen.

Also ich will mich echt herzlich dafür bedanken, dass ihr so hilfsbereit seit, find ich echt spitze. :slight_smile: und ohne euch hätt ich das alles schon längst in den Wind werfen können.

Fragen werden also in Zukunft nicht ausbleiben, aber wenn ihr mir sagt wie ich sie besser stellen soll, dann mach ich das gerne :slight_smile: und ich hoffe dass ihr mir weiterhin so gut helft :slight_smile:
Wenn ich euch irgendwo ma treffe geb ich euch auf jedenfall mindestens mal ein Bier aus :wink:


Ich setz mich gleich morgen an das was du da aufgeschrieben hast und teste es aus nur eine frage :
ist bar nur eine Tabelle oder eine Spalte?

Eine table mit nur einer Spalte.


Andreas

Moin moin,

also die Syntax davon ist aber so grundliegen richtig oder??

declare ort text;
begin
SELECT into ort destination.dialcode
           FROM destination
          WHERE (NEW.ausgangsnummer) ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC;
INSERT INTO Call_test (destination) VALUES (ort);
END;

ich denke schon

dann kommt der Trigger

CREATE TABLE call_test
(
  identify serial NOT NULL,
  callrefnummer varchar(50) NOT NULL,
  datum date,
  uhrzeit time,
  eingangsport int4,
  ausgangsport int4,
  eingangsnummer text,
  ausgangsnummer text,
  dauer int4,
  releasecode varchar(50),
  destination text,
  CONSTRAINT call_test_pkey PRIMARY KEY (identify)
) 
WITHOUT OIDS;
ALTER TABLE call_test OWNER TO tim;
 
 
-- Trigger: desti on call_test
-- DROP TRIGGER desti ON call_test;
CREATE TRIGGER desti
  BEFORE INSERT
  ON call_test
  FOR EACH ROW
  EXECUTE PROCEDURE f_destination();

so und jetzt die Fehlermeldung:

Du hast da eine Rekursion: bei jedem Insert in Call_test erzeugst Du einen Insert in Call_test, welcher wieder einen Insert in Call_test erzeugt …

Hab doch gesagt dass es für mich wie ne schleife aussieht :smiley:

aber naja was macht man gegen so was? ne neue Tabelle anfangen? und den Trigger in die schreiben lassen?, würd aber schon gern alles in die eine schreiben …

oder soll ich einfach ein “UPDATE” mit After trigger machen?
Also dass ich sage
Where Identify = NEW.indentfy ??
oder gibts da ne standartlösung?


Gruß BoOnOdY