Generatorwerte setzen /Trigger deaktivieren

[Hansa]

Hi,

folgendes Problem : ich habe eine alte Tabelle, die muß ich in eine Interbase-Table umbauen. Die alte Tabelle hat ein Autoinc-Feld und die neue eine ID, die mit Generator/Trigger hochgezählt wird. Soweit so gut. Ich übernehme die alten AutoInc-Felder als ID und fertig ! Sollte man aber nur meinen. Wegen dubioser Fehler kam ich der Sache erst jetzt auf die Spur: die Autoinc-Felder können so gar nicht übernommen werden, zumindest nicht, sofern irgendwann einmal etwas gelöscht wurde.

In diesem Falle würde der Generatorwert der ID "hinterher" laufen. Soviel zur Problemstellung. Um dies zu umgehen muß ich also den Trigger, den ich später natürlich brauche, deaktivieren und später wieder aktivieren. So ungefähr habe ich es probiert:

Delphi-Quellcode:

DS.SelectSQL.Text := 'ALTER TRIGGER DS_BI0 INACTIVE BEFORE INSERT POSITION 0';
DS.open;

Das scheint so aber nicht zu gehen. Was ist falsch ?
In der DB steht der Trigger so drin :

SQL-Code:

ALTER TRIGGER DS_BI0
INACTIVE BEFORE INSERT POSITION 0
AS
begin
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID (GEN_DS_ID,1);
end

Muß ich das ganze vielleicht in eine Zeile schreiben ? Beim Setzen des neuen Generatorwertes zum Schluß, weiß ich auch nicht wie.

[Robert_G]

in IB müsste es doch auch sowas geben:

SQL-Code:

ALTER TRIGGER DS_BI0 disable

und anschalten mit "enable"

gibts jedenfalls in Oracle

[Hansa]

Thx,

aber es geht mehr um Delphi. Wenn ich nur mit SQL die DB ändere und wieder zurück ändere wird es so schon gehen, aber das soll automatisch programmgesteuert ablaufen.

[Robert_G]

Wenn der Generator aus IB mit den Sequences aus Oracle verglichen werden kann, dann müsste das doch gehen:
-größte ID vor erster Aktivierung des Triggers abfragen
-sequence/Generator hochzählen bis sie gleich sind
-trigger aktivieren
-die nächste ID ist um 1 größer als die ehemals größte


Sicher ist das alles durch SQL machbar - deshalb kannst du es ja auch in deine Apps einbauen.
Das Prozedere kann bei jedem Deaktivieren/Aktivieren wiederholt werden um Key violations vorzubeugen.

Ich hoffe, ich habe dich da nicht falsch verstanden...

[Generalissimo]

Hi,

SQL-Code:

ALTER TRIGGER DS_BI0
INACTIVE BEFORE INSERT POSITION 0
AS
begin
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID (GEN_DS_ID,1);
end

sieht schon gut aus. Ist aber nur die Änderung des Triggers.
Sicher das der auch in die Datenbank geschrieben wurde (Commit oder CommitRetaining)?
Mit was für ner Console arbeitest du (IBOConsole, IBExpert)?

Ich hab jetzt dein Problem so verstanden, das du aus ner alten Tabelle die AutoInc-ID's in
ne IB-Tabelle übernehmen willst und dann neue übern Trigger und Generator hochzählen lassen willst.

Der Trigger springt ja nur an, falls du beim Insert nichts für ID angibst.
Eigentlich sollte er sobald ein Insert so aussieht:

SQL-Code:

Insert into table(id,spalte2,spalte3) values (1,Hallo,Welt)

nicht anspringen, da ja nun

SQL-Code:

new.id not null

ist.

Probier mal ein Backup/Restore.
Arbeitest du eigentlich mit Interbase oder Firebird. Welche Versionen?

Ansonsten kannst du ja den Genratorwert mit

SQL-Code:

 Set Generator GEN_DS_ID to x

auf jede beliebige Zahl setzen.

[Hansa]

Ja, das da kommt der Sache schon sehr, sehr nahe. Der Effekt fiel genau deshalb nicht auf, weil der Trigger nicht zum Zuge kommt, es sei denn in der alten Table ist das AutoInc Feld nicht besetzt. Eben wegen :

SQL-Code:

IF (NEW.ID IS NULL) THEN

ist diese eben NICHT Null, sondern der alte AutoInc-Wert :!: Und da die Table große Lücken hat, ist in Interbase (bzw. FB 1.0) die Gefahr nicht so groß, bei kurzen Testeingaben diesen Fehler überhaupt zu sehen. Aber der wird mit Sicherheit kommen. :mrgreen: Die IDs müssen so bleiben, wegen der referentiellen Integrität der DB. Das geht jetzt so mit einer Query :

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
begin
  pFIBQuery1.SQL.Text := 'SET GENERATOR GEN_DS_ID TO 59';
  pFIBQuery1.ExecQuery;
  pFIBTransAction1.Commit;
  pFIBDataBase1.Close;
end;

Ist mit IBExpert überprüft. Allerdings taucht da auch noch eine Ungereimtheit auf. Das Hauptproblem besteht nun eigentlich nur noch darin, wo und wie ich den Trigger kurzfristig programmgesteuert deaktiviere.

[Generalissimo]

Hi du kannst das aber auch so machen:

SQL-Code:

CREATE PROCEDURE SET_GENERATOR_TO_MAX_ID
AS
DECLARE VARIABLE MAXID INTEGER;
BEGIN
 SELECT MAX(ID) FROM your_table
 INTO :MAXID;
 SET GENERATOR GEN_DS_ID TO :MAXID;
END


CREATE TRIGGER DS_BI0
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  IF (NEW.ID IS NULL) THEN
  begin
    EXECUTE PROCEDURE SET_GENERATOR_TO_MAX_ID;
    NEW.ID = GEN_ID (GEN_DS_ID,1);
  end
end

Edit5:

Bissl die Erklärung vergessen. Also jetzt ist es so, wenn du
ein Insert machst und dort ein Wert für die ID (der AutoInc-Wert aus der
alten Tabelle) drin steht, wird der Trigger nicht ausgelöst.
Steht nix drin, wird die ID des letzten Insert ermittelt und
der Generator angepasst. Somit muss der Trigger überhaupt
nicht deaktiviert werden.

Somit musst du überhaupt nix mehr vom Client aus machen und
lässt den DB-Server die Arbeit machen. Da der sowieso erstens
meistens auf nem Server läuft und zweitens es direkt in der DB
stattfindet, bringst Performance.
Is aber ungetestet, aber vom Prinzip her müsste es gehen.

[Hansa]

Fast Volltreffer !! :lol: Das mit dem MAX habe ich mir auch schon überlegt. Aber das ganze muß/soll schon auf Clientseite passieren, also aus dem Delphi Programm raus. Die Datenbank soll nur für den Zeitraum der Datenübernahme aus der alten Table in Anspruch genommen/modifiziert werden.

Und zwar aus folgendendem Grund: Es muß gewährleistet sein, daß der Generator auf dem höchsten Wert steht, der sinnvoll ist. Selbst dann, wenn 100.000 Datensätze durchnummeriert sind und nur einer noch mit ID =50.000.000 folgt. Die Daten sind teilweise sehr alt (> 10 Jahre !!) und ich kann einen solchen Extremfall nicht ausschließen. Auch nicht, daß eine einzige ID auf NULL steht, obwohl das unmöglich sein sollte. Wer weiß, wieviele Festplatten bereits benutzt wurden ? Wieviele Bytes da eventuell mittlerweile zerstört sind durch fehlerhafte Rücksicherung oder sonst was. Mir aber soll all das egal sein.

Sofern der Trigger ab dann sauber läuft ists gut. Um alles absolut wasserdicht zu halten werden ich trotzdem solche extremen ID-Werte abfangen, direkt schon beim Lesen der alten Daten und dann mit der MAX Funktion den ab dann gültigene Wert eintragen, wie von Generalissimo beschrieben.

Allerdings wird das erst passieren, nachdem die alten AutoIncs komplett in der Table als ID drinne stehen. Deshalb ist nur noch folgendes offen: Wie schalte ich den Trigger aus Delphi heraus einmalig temporär ab ?

[Generalissimo]

Also mit Alter-Trigger wird das nichts werden, denke
ich. Das alte Änderungsspeicher-Problem mit IB/FB.
Nach einer Weile ist die Max-Anzahl von Änderungen erreicht
und du müsstest ein Backup-Restore durchführen.

Probier mal den Gedanken:

1. Generator für Boolean-Wert

SQL-Code:

CREATE GENERATOR GEN_TRIGGER_STATUS;
SET GENERATOR GEN_TRIGGER_STATUS TO 0;

2. Schreibe die Prozeduren:

SQL-Code:

CREATE PROCEDURE SET_GENERATOR_TO_MAX_ID 
AS 
DECLARE VARIABLE MAXID INTEGER; 
BEGIN 
SELECT MAX(ID) FROM your_table 
INTO :MAXID; 
SET GENERATOR GEN_DS_ID TO :MAXID; 
END

SQL-Code:

Create Procedure SET_TRIGGER_STATUS ( STATUS:SMALLINT)
AS
BEGIN
 If (:Status) = 1 then Set Generator GEN_TRIGGER_STATUS TO 1
 If (:Status) = 0 then Set Generator GEN_TRIGGER_STATUS TO 0
END

3. Der Trigger sollte so aussehen

SQL-Code:

CREATE TRIGGER DS_BI0
ACTIVE BEFORE INSERT POSITION 0
AS
begin
 IF (GEN_ID(GEN_TRIGGER_STATUS,0)=1) THEN
 BEGIN
  IF (NEW.ID IS NULL) THEN
    EXECUTE PROCEDURE SET_GENERATOR_TO_MAX_ID;
    NEW.ID = GEN_ID (GEN_DS_ID,1);
 END
end

Durch GEN_ID(GEN_TRIGGER_STATUS,0) fragt der Trigger ab, welchen Stand der Generator hat.
Wenn er 1, also True ist, soll der Trigger ausgeführt werden.
In deinem Programm müsstest du nur noch die StoredProc in einem TIBStoredProc ausführen
und den Input-Parameter je nachdem ob der Trigger an oder aus sein soll 1 oder 0 übergeben.

[Hansa]

Das von Generalissimo erschien mir zu kompliziert. Ich habe es jetzt so gemacht : eine Stored Procedure beim Programmstart, die den Trigger INACTIVE setzt. Zum Schluß eine, die ihn wieder auf ACTIVE setzt.

Was mich aber jetzt daran stört : für die Ermittlung des Generatorwertes mit MAX brauche ich noch eine SP. Und zu guter letzt auch noch ein Dataset, um folgendes auszuführen :

Delphi-Quellcode:

MaxGenSP.ExecProc;
RecDatenSatz.SelectSQL.Text := 'SET GENERATOR GEN_REC8_ID TO '+
                                IntToStr (MaxGenSP.FieldByName ['MAXIDOUT'].AsInteger);

Am meisten stört mich, daß ich nur für den einmaligen Zweck, um einen MAX-Wert an den Generator zu übergeben in der Datenbank eine SP anlegen muß. Das kommt mir sehr kompliziert vor. Geht das nicht einfacher ?