Unique bei 4 verschiedenen Feldern

[ATS3788]

Hallo
Danke noch mal für die Hilfen,
Nachdem ich mich ein wenig mit SQL beschäftigt habe, möchte ich
meine Lösung präsentieren.
Ich habe einfach eine neue Tabelle Dummy erstellt und schreibe
da einfach alle Werte in DUMMYCOUNTER hinein.
Das Feld ist auch Unique und meckert wenn was 2 mal
versucht wird einzugeben.
Falls mal ein Datensatz gelöscht werden soll,
wird dieser auch in DUMMYCOUNTER gelöscht.

Vielleicht weiss ja jemand eine Verinfachung des ganzen ?

SQL-Code:

******************************************************************************/
/*                             Unique Constraints                             */
/******************************************************************************/

ALTER TABLE "FolderDsg" ADD CONSTRAINT UNQ1_FOLDERDSG UNIQUE (ORDER1)
USING INDEX "_IDXORDER1";
ALTER TABLE "FolderDsg" ADD CONSTRAINT UNQ2_FOLDERDSG UNIQUE (ORDER2)
USING INDEX "_IDXORDER2";
ALTER TABLE "FolderDsg" ADD CONSTRAINT UNQ3_FOLDERDSG UNIQUE (ORDER3)
USING INDEX "_IDXORDER3";
ALTER TABLE "FolderDsg" ADD CONSTRAINT UNQ4_FOLDERDSG UNIQUE (ORDER4)
USING INDEX "_IDXORDER4";

CREATE UNIQUE INDEX "FolderDsg_IDX1" ON "FolderDsg" (ORDER1, ORDER2, ORDER3, ORDER4);


/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/

/* Trigger: FOLDERDSG_DELETE */
CREATE OR ALTER TRIGGER FOLDERDSG_DELETE FOR "FolderDsg"
ACTIVE BEFORE DELETE POSITION 1
AS
declare variable theorder1 bigint;
begin

if (OLD.order1 is not null) then begin
theorder1 = OLD.order1;
 DELETE FROM DUMMY
    WHERE DUMMYCOUNTER = :theorder1;
      end

if (OLD.order2 is not null) then begin
theorder1 = OLD.order2;
 DELETE FROM DUMMY
    WHERE DUMMYCOUNTER = :theorder1;
      end

if (OLD.order3 is not null) then begin
theorder1 = OLD.order3;
 DELETE FROM DUMMY
    WHERE DUMMYCOUNTER = :theorder1;
      end

if (OLD.order4 is not null) then begin
theorder1 = OLD.order4;
 DELETE FROM DUMMY
    WHERE DUMMYCOUNTER = :theorder1;
      end


end
^


/* Trigger: FOLDERDSG_UPDATE */
CREATE OR ALTER TRIGGER FOLDERDSG_UPDATE FOR "FolderDsg"
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable theorder1 bigint;
declare variable theorder2 bigint;

begin

if (OLD.order1 is not null) then begin
theorder1 = NEW.order1;
theorder2 = OLD.order1;

 update DUMMY set DUMMYCOUNTER = :theorder1
     WHERE DUMMYCOUNTER = :theorder2;

end
-- that works well ----
if ((OLD.order1 is null) and NEW.order1 is not null ) then begin
INSERT INTO DUMMY (DUMMYCOUNTER)
     values (NEW.order1);
    end
-----------------------------------------

if (OLD.order2 is not null) then begin
theorder1 = NEW.order2;
theorder2 = OLD.order2;

 update DUMMY set DUMMYCOUNTER = :theorder1
     WHERE DUMMYCOUNTER = :theorder2;

end
-- that works well ----
if ((OLD.order2 is null) and NEW.order2 is not null ) then begin
INSERT INTO DUMMY (DUMMYCOUNTER)
     values (NEW.order2);
    end
-----------------------------------------
if (OLD.order3 is not null) then begin
theorder1 = NEW.order3;
theorder2 = OLD.order3;

 update DUMMY set DUMMYCOUNTER = :theorder1
     WHERE DUMMYCOUNTER = :theorder2;

end
-- that works well ----
if ((OLD.order3 is null) and NEW.order3 is not null ) then begin
INSERT INTO DUMMY (DUMMYCOUNTER)
     values (NEW.order3);
    end
-----------------------------------------
if (OLD.order4 is not null) then begin
theorder1 = NEW.order4;
theorder2 = OLD.order4;

 update DUMMY set DUMMYCOUNTER = :theorder1
     WHERE DUMMYCOUNTER = :theorder2;

end
-- that works well ----
if ((OLD.order4 is null) and NEW.order4 is not null ) then begin
INSERT INTO DUMMY (DUMMYCOUNTER)
     values (NEW.order4);
    end
-----------------------------------------
 end
^