UpdateTrigger mit INSERTED und DELETED

[Patrick Krause]

Hallo,

habe schon wieder eine Frage zum MS SQL-Server:

ich möchte einen ORACLE-Update-Trigger in MS SQL übersetzen. Unter Oracle gibt es glücklicherweise die FOR EACH ROW Angabe, bei MS SQL leider nicht:

ORACLE:

CREATE TRIGGER UP_EMPB_KUNDEN 
AFTER update
on EMPB_KUNDEN 
for each row
BEGIN

  update EMPB_ELEMENTE set EMPB = :NEW.EMPB 
  where EMPB = :OLD.EMPB;

END;

Wie muss ich das mit dem MS SQL umsetzen?

Ich habe hier so etwas hinbekommen, ist das richtig? Müsste man nicht alle drei involvierten Tabellen joinen (EMPB_ELEMENTE, INSERTED, DELETED)?

MS SQL:

CREATE TRIGGER UP_EMPB_KUNDEN 
on EMPB_KUNDEN
AFTER update
AS
BEGIN
 
  update EMPB_ELEMENTE 
  set EMPB = NEW.EMPB 
  from INSERTED NEW
  join DELETED OLD on
    E.EMPB = OLD.EMPB

END

Viele Grüße,


Patrick Krause

[Patrick Krause]

So, habe etwas herausgefunden, mit dem Hinweis, dass die INSERTED und DELETED-Tabellen IMMER Synchron wären und nicht gejoint werden bräuchten. Kann diese These hier von jemandem bestätigt werden? Hier ein Beispiel:

IF UPDATE(mykey)
BEGIN
  UPDATE Table2 
  set keyforTable1 = inserted.myKey 
  from inserted, deleted, Table2 
  where table2.keyforTable1=deleted.mykey 
END

Was sagt Ihr dazu?

Grüße,

Patrick

[Andreas Kosch]

Hallo,

Unter Oracle gibt es glücklicherweise die FOR EACH ROW Angabe, bei MS SQL leider nicht.

Ein Trigger wird unabhängig von der Anzahl der von der auslösenden Aktion betroffenen Datensätze am Ende nur ein einziges Mal ausgelöst. Somit muss ein Trigger mit 3 Fällen rechnen:

1. Es sind keine Datensätze betroffen
2. Es ist genau 1 Datensatz betroffen
3. Es sind mehrere Datensätze betroffen

Wenn in allen Fällen eine datensatzbezogene Reaktion innerhalb des MS SQL Server-Triggers notwendig ist, kann im Multi-Row-Fall die Kombination von einem INSTEAD OF-Trigger mit einem Cursor einen FOR EACH ROW-Trigger simulieren. Das folgende Beispiel demonstriert dies:

SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('dbo.TestTbl') IS NOT NULL
  DROP TABLE dbo.TestTbl;
GO

 
CREATE TABLE dbo.TestTbl
(
  keycol  INT         NOT NULL PRIMARY KEY,
  datacol VARCHAR(10) NOT NULL
)
GO

CREATE TRIGGER trTestTblPerRow 
  ON dbo.TestTbl 
  INSTEAD OF INSERT
AS
  DECLARE @rc AS INT;
  SET @rc = @@ROWCOUNT;
  IF @rc = 0 
    RETURN;
  IF @rc = 1
    BEGIN
      -- Trigger ist nur für 1 Datensatz zuständig
      INSERT INTO dbo.TestTbl SELECT * FROM inserted;
    END
  ELSE
    BEGIN
      -- Trigger ist für mehrere Datensätze zuständig
      DECLARE @keycol AS INT, @datacol AS VARCHAR(10);
      DECLARE c CURSOR FAST_FORWARD FOR
        SELECT keycol, datacol FROM inserted;
      OPEN c;
      FETCH NEXT FROM c INTO @keycol, @datacol;
      WHILE @@fetch_status = 0
      BEGIN
        INSERT INTO dbo.TestTbl(keycol, datacol)
          VALUES(@keycol, @datacol);
        FETCH NEXT FROM c INTO @keycol, @datacol;
      END
      CLOSE c;
      DEALLOCATE c;
    END
GO

-- ############################
-- Triggerverhalten testen
-- ############################

 
-- Fall A: 0 Rows
INSERT INTO dbo.TestTbl SELECT 1, 'A' WHERE 1 = 0;
GO

-- Fall B: 1 Row
INSERT INTO dbo.TestTbl SELECT 1, 'A';
GO

-- Fall C: Multi Rows
INSERT INTO dbo.TestTbl
  SELECT 2, 'B'
  UNION ALL
  SELECT 3, 'C'
  UNION ALL
  SELECT 4, 'D';
GO

-- Ergebnis prüfen
SELECT * FROM dbo.TestTbl

...und nicht gejoint werden bräuchten.

Dies ist nicht korrekt. In der Hilfedatei des MS SQL Server (BOL) wird das Verhalten auf der Seite Using the inserted and deleted Tables beschrieben, das dort verwendete Beispiel greift über einen JOIN zwischen der Basistabelle und der virtuellen Tabellen inserted auf die Daten zu.

[Patrick Krause]

Danke für den Hinweis! Damit kann man sich dann behelfen, wenn man zeilenweise updates machen will.

...und nicht gejoint werden bräuchten.

Allerdings meinte ich damit, dass man die INSERTED und DELETED nicht mehr miteinander joinen braucht, wenn man noch die Basistabelle angibt. INSERTED und DELETED scheinen dann synchron zu laufen. Das von mir vorher eingefügte Beispiel funktioniert wunderbar, ganz ohne Cursor und ohne join zwischen INSERTED und DELETED: es werden alle Zeilen mit dem neuen Wert geupdatet, die noch den alten Wert besitzen.

IF UPDATE(mykey)
BEGIN
  UPDATE Table2 
  set keyforTable1 = inserted.myKey 
  from inserted, deleted, Table2 
  where table2.keyforTable1=deleted.mykey 
END

Viele Grüße,

Patrick