none
Trigger erstellen RRS feed

  • Frage

  • Wie kann man folgende Trigger(Oracle) für SQL Server ( Syntax) anpassen?

    CREATE OR REPLACE TRIGGER "DWH_TRG_DVTW_SK"

       BEFORE INSERT

       ON DWH_D_VERTRIEBSWEG

       REFERENCING NEW AS NEW OLD AS OLD

       FOR EACH ROW

    DECLARE

    BEGIN

       SELECT DWH_sqc_DVTW_SK.NEXTVAL

         INTO :NEW.DVTW_SK

         FROM DUAL;

    END trg_DVTW_SK;

     

    LG

    Freitag, 27. Oktober 2017 12:45

Antworten

  • Hallo,

    meiner Meinung nach bewirkt DWH_sqc_DVTW_SK.NEXTVAL (eine Sequence) nur, dass die Spalte DVTW_SK mit einem eindeutigen Wert gefüllt wird. Ich habe unter ORACLE auch so etwas benutzt.

    Wenn man im SQL-Server diese Spalte aber als "int IDENTITY(1,1)" deklariert, so geschieht dies automatisch und man benötigt dafür überhaupt keinen Trigger.

    Gruß

    Willi


    Donnerstag, 2. November 2017 15:52
  • Hallo Baldur,

    erst einmal "sorry" für meine Unterstellung - ich hatte Deinen Post tatsächlich so verstanden, dass Werte in inserted geändert werden sollen.

    "Bei der Verwendung mit Instead-Trigger ist dies dann wieder egal, da ja die Identity grundsätzich mit einem Nextvalue-Wert gesetzt wird,"

    Ich hoffe, Dich hier nicht auch wieder falsch verstanden zu haben :)! Wenn Du einen INSTEAD OF-Trigger verwendest, wird IDENTITY noch nicht ermittelt. Das findet erst dann statt, wenn der Trigger die Daten endgültig in die Zieltabelle einträgt!

    CREATE TABLE dbo.foo (Id INT NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, C1 CHAR(400));
    GO
    
    CREATE TRIGGER dbo.trg_foo_Insert
    ON dbo.foo
    INSTEAD OF INSERT
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	SELECT * FROM inserted;
    
    	SET NOCOUNT OFF;
    END
    GO

    Wie man gut erkennen kann, gibt es zum Zeitpunkt, wenn der Trigger ausgeführt wird, noch keine ID. Das wäre auch fatal, da für den Fall eines FALLBACK die ID-Werte dennoch vergeben wurden.

    Ich habe dazu mal vor 4 Jahren einen Artikel dazu geschrieben. Die Technologie (Caching) hat sich geändert; aber generell funktioniert das Prinzip auch mit den neuen Versionen von Microsoft SQL Server noch.

    In dem Fall des TE würde ich - wie Willi - ebenfalls eine Lösung mit IDENTITY vorschlagen. Damit könnte er auch, sofern keine weitere Business-Logik mit dem Trigger erreicht werden soll, ohne Trigger arbeiten.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Freitag, 3. November 2017 13:26

Alle Antworten

  • Der SQL-Server unterstützt leider keine Before-Trigger sondern nur After-Trigger mit der Besonderheit, dass der Trigger nur nach dem Update aller Zeilen, die ein Update erwischt, aufgerufen wird.
    Anschließend kann man durch die Inserted-Tabelle wandern um dann die fehlenden Daten per eigenem 2. Update zu ergänzen.
    Empfehlenswert ist das leider nicht da nicht performant.

    Alternativ geht nur ein Instead-of-Trigger. In diesem musst du den Insert dann selber machen, kannst aber die fehlenden Daten dann auch dazu packen:

    insert into OrigTable (F1, F2, ...)
    Values(: V1, : V2, (Select ....), ...)

    https://technet.microsoft.com/de-de/library/ms175089%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396


    Freitag, 27. Oktober 2017 14:18
  • Guten Morgen,

    ein Statement und zwei falsche Aussagen zu Triggern in SQL Server!

    Der SQL-Server unterstützt leider keine Before-Trigger sondern nur After-Trigger

    Selbstverständlich unterstützt SQL Server auch BEFORE-Trigger. Sie heißen dort INSTEAD OF-Trigger. Die Besonderheiten von diesen Triggern wurden doch in einem anderen Thread bereits ausführlich - und leider auch teilweise recht hitzig - diskutiert.

    Die Besonderheit des INSTEAD OF-Triggers liegt auch darin, dass die Ressourcen zum Zeitpunkt des Absetzen des eigentlichen Befehls noch nicht blockiert werden.

    Inserted-Tabelle wandern um dann die fehlenden Daten per eigenem 2. Update zu ergänzen.

    Nein, das stimmt nicht! Weder inserted noch deleted kann man nachträglich bearbeiten. Die Tabellen sind schreibgeschützt und Du erhältst den Fehler 286, wenn Du versuchst die Daten zu ändern!

    CREATE TABLE dbo.test
    (
    	Id	int	identity(1, 1) PRIMARY KEY,
    	c1	varchar(100)
    )
    GO
    
    CREATE TRIGGER dbo.trg_test_insert
    ON dbo.test
    INSTEAD OF INSERT
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	UPDATE inserted
    	SET		c1 = 'Das stimmt nicht!'
    
    	SET NOCOUNT OFF;
    END

    https://docs.microsoft.com/de-de/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables

    @olha20: Was genau möchtest Du überhaupt mit dem Trigger bewirken? Aus dem Code wird mir das nicht richtig klar.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Donnerstag, 2. November 2017 05:53
  • Korrektur zu den Falschaussagen, die ich ja nicht gemacht habe:

    Ich habe nicht geschrieben, dass die Inserted-Tabelle zu ergänzen wäre, sondern dass diese Tabelle die Basis ist, mit der die geänderten Sätze zu identifizieren sind.
    Ist doch logisch, dass eine Ergänzung dann nur auf dem Original funktioniert, was auf Grund des besagten Threads als nicht empfehlenswert gilt (Transaktionsproblematik, Journal).

    Ein Before-Trigger wird je Satz aufgerufen und enthält je eine Struktur mit Before-Immage (Old.) und After-Immage (New.), je nach Implementierung.
    Hier kann ich mich nun gezielt um die wenigen Felder kümmern um dies es geht, zumal diese ja auch als Variablen verfügbar sind. Alle anderen interessieren mich ja nicht.
    Der anschließende Update/Insert/Delete wird dann von der Datenbank selber durchgeführt.
    Diese Form des Triggers wird eben von SQL-Server nicht unterstützt.

    Ein Instead-Of-Trigger ist eben in diesem Sinne kein echter Before-Trigger sondern ist eher wie ein Prozedur-Aufruf zu sehen, der alle Spalten der Tabelle oder auch View als Parameter enthält.
    In diesem kann ich dann mit den Daten machen was ich will, da das Ziel der Tabelle überhaupt nicht festgelegt ist.
    Ich muss also nun die benötigten Befehle alle selber komplett kodieren und kann natürlich Daten ergänzen und/oder verfälschen.
    Zusätzlich muss ich bei Update/Delete ggf. auch noch einen Select machen um auf die Before-Daten zuzugreifen.
    Und Performant zum Vergleich eines Before-Triggers ist das auch nicht, da ja grundsätzlich zusätzliche SQL's erforderlich werden.
    Der Nachteil (meine persönliche Meinung) ist, dass bei jeder Änderung/Erweiterung der Tabelle ebenso der Instead-Of-Trigger bearbeitet werden muss, da dieser ja auch mit den neuen Feldern umgehen muss.
    Was i.d.R. eine Bearbeitung des Datenmodells komplizierter macht und halt deswegen öfters unerlassen wird.
    Beim Before-Trigger dagegen ändert sich da überhaupt nichts, wenn eine neue Spalte vom Trigger nicht berücksichtigt werden muss.

    Und in meiner obigen Antwort habe ich den Instead-Of-Trigger ja erwähnt.

    Und was soll gemacht werden?
    Ganz Einfach: Aus einer Sequenz soll die nächste Nummer in ein Feld geschrieben werden bevor der Insert tatsächlich passiert.
    Für diesen Konkreten Fall gibt es allerdings (auch schon bei Oracle) Alternativen: Stichwort Identity-Column.
    • Bearbeitet Der Suchende Donnerstag, 2. November 2017 09:13
    Donnerstag, 2. November 2017 09:09
  • Hallo,

    meiner Meinung nach bewirkt DWH_sqc_DVTW_SK.NEXTVAL (eine Sequence) nur, dass die Spalte DVTW_SK mit einem eindeutigen Wert gefüllt wird. Ich habe unter ORACLE auch so etwas benutzt.

    Wenn man im SQL-Server diese Spalte aber als "int IDENTITY(1,1)" deklariert, so geschieht dies automatisch und man benötigt dafür überhaupt keinen Trigger.

    Gruß

    Willi


    Donnerstag, 2. November 2017 15:52
  • Ja, das ist soweit korrekt, aber:
    Das Problem mit Identity ist das selbe wie mit Default-Werten.
    Diese werden nur gesetzt, wenn man die Spalten nicht im Insert verwendet, also die Spalte nicht erwähnt.
    Erst dann wird der nächste Identity-Wert gezogen oder der Default gesetzt.
    Verwendet man aber die Identity-Spalte beim Insert, gibt es einen Fehler und der Insert schlägt fehl, während man bei den Default-Spalten nun individuelle Werte angeben kann.
    Dies erfordert halt eine etwas andere Programmierung.
    Bei der Verwendung mit Instead-Trigger ist dies dann wieder egal, da ja die Identity grundsätzich mit einem Nextvalue-Wert gesetzt wird, wobei der Instead-Trigger nun die Spalte eher weglassen sollte als einen neuen Wert zu laden.


    • Bearbeitet Der Suchende Donnerstag, 2. November 2017 21:58
    Donnerstag, 2. November 2017 21:57
  • Hallo Baldur,

    erst einmal "sorry" für meine Unterstellung - ich hatte Deinen Post tatsächlich so verstanden, dass Werte in inserted geändert werden sollen.

    "Bei der Verwendung mit Instead-Trigger ist dies dann wieder egal, da ja die Identity grundsätzich mit einem Nextvalue-Wert gesetzt wird,"

    Ich hoffe, Dich hier nicht auch wieder falsch verstanden zu haben :)! Wenn Du einen INSTEAD OF-Trigger verwendest, wird IDENTITY noch nicht ermittelt. Das findet erst dann statt, wenn der Trigger die Daten endgültig in die Zieltabelle einträgt!

    CREATE TABLE dbo.foo (Id INT NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, C1 CHAR(400));
    GO
    
    CREATE TRIGGER dbo.trg_foo_Insert
    ON dbo.foo
    INSTEAD OF INSERT
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	SELECT * FROM inserted;
    
    	SET NOCOUNT OFF;
    END
    GO

    Wie man gut erkennen kann, gibt es zum Zeitpunkt, wenn der Trigger ausgeführt wird, noch keine ID. Das wäre auch fatal, da für den Fall eines FALLBACK die ID-Werte dennoch vergeben wurden.

    Ich habe dazu mal vor 4 Jahren einen Artikel dazu geschrieben. Die Technologie (Caching) hat sich geändert; aber generell funktioniert das Prinzip auch mit den neuen Versionen von Microsoft SQL Server noch.

    In dem Fall des TE würde ich - wie Willi - ebenfalls eine Lösung mit IDENTITY vorschlagen. Damit könnte er auch, sofern keine weitere Business-Logik mit dem Trigger erreicht werden soll, ohne Trigger arbeiten.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Freitag, 3. November 2017 13:26
  • Nun, da hast du mich tatsächlich wieder mistverstanden;-).

    Beim Instead-Of-Trigger ist es egal, ob der Client beim Insert die Identity-Spalte verwendet oder auch nicht, da der Trigger ja nun selber entscheiden muss, ob beim eigentlichen Insert die Spalte angegeben wird oder nicht.
    Eben je nach Implemantation.

    Ist die Spalte keine Identity, vergebe ich die, wie ganz oben gewünscht, eben selber.
    Ist die Spalte eine Identity, lasse ich die Spalte weg, so dass der Server die ID vergibt und gebe diese für evtl. Output-Felder wieder zurück.

    Da ja hier eine Umstellung von Oracle auf den SQL-Server anliegt und man die Client-Software wohl geringfügig bis garnicht anpassen möche (setze ich mal so voraus), bietet sich eben ein Instead-Of Before-Insert mit einer Identity-Column an um sich das eben zu vereinfachen.

    PS:
    Manchmal habe ich hier das Gefühl, dass man alles bis ins kleinste vorbeten muss;-).
    Das ist jetzt nicht bös gemeint.

    Freitag, 3. November 2017 14:24