Problem bei InsteadOfInsert-Trigger

27/03/2009 - 08:47 von Lutz Uhlmann | Report spam
Hallo NG

Ich habe auf eine Abfrage über zwei Tabellen einen InsteadOfInsert-Trigger
laufen. Leider ergibt sich hier ein Problem was ich derzeit nicht lösen
kann.

Tabelle1: DB_STOERUNG
ID (int, PK)
...

Tabelle2: DB_ANGEBOT
NR (int, PK)
STO_ID (int, PK) > FK für DB_STOERUNG
...

Abfrage: a_stoerung
SELECT schStoer.DB_STOERUNG.ID, , schAuftrag.DB_ANGEBOT.NR,
schFakos.DB_ANGEBOT.STO_ID,
FROM schStoer.DB_STOERUNG INNER JOIN schAuftrag.DB_ANGEBOT ON
schStoer.DB_STOERUNG.ID = schAuftrag.DB_ANGEBOT.STO_ID

Es kann also folgende Daten geben
ID NR, STO_ID
100 1, 100
101 1, 101
102 1, 102

Mein trigger sieht ungefàhr so aus

CREATE TRIGGER schAuftrag.TR_IOI_schAuftrag_a_stoerung ON
schAuftrag.a_stoerung
WITH ENCRYPTION
INSTEAD OF INSERT
AS
BEGIN
SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
INSERT INTO schStoer.DB_STOERUNG
( [ID]
, ...)
SELECT [ID]
, ...
FROM inserted

INSERT INTO schAuftrag.DB_ANGEBOT
( [NR]
, [STO_ID]
, ...)
SELECT [NR]
, inserted.ID AS [STO_ID]
, ...
FROM inserted
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END

Leider gibt das ein Problem, wenn ich in diese Abfrage einen Datensàtze
ID NR, STO_ID
100 2, 100
103 1, 103
einfügen will. Der erste Insert im Trigger schlàgt natürlich für den ersten
Datensatz fehl, weil es den Datensatz 100 in DB_STOERUNG ja schon gibt.

Wie kann ich diesen Insert am günstigsten auf die nicht vorhanden
DB_STOERUNG-Datensàtze beschrànken?
Probiert habe ich erst einmal
INSERT INTO schStoer.DB_STOERUNG
( [ID]
, ...)
SELECT [ID]
, ...
FROM inserted WHERE inserted.ID NOT IN (SELECT ID FROM
schStoer.DB_STOERUNG)
Allerdings bin ich mir grad nicht sicher ob das so wirklich funktioniert
(bekomme abundan noch Fehlermeldungen, die aber vielleicht auch von anderen
Einschrànkungen herrühren können) und vorallem ob das von der Performance
her so günstig ist?

Lutz
 

Lesen sie die antworten

#1 Elmar Boye
29/03/2009 - 18:56 | Warnen spam
Hallo Lutz,

Lutz Uhlmann schrieb:
Ich habe auf eine Abfrage über zwei Tabellen einen InsteadOfInsert-Trigger
laufen. Leider ergibt sich hier ein Problem was ich derzeit nicht lösen
kann.

Wie kann ich diesen Insert am günstigsten auf die nicht vorhanden
DB_STOERUNG-Datensàtze beschrànken?
Probiert habe ich erst einmal
INSERT INTO schStoer.DB_STOERUNG
( [ID]
, ...)
SELECT [ID]
, ...
FROM inserted WHERE inserted.ID NOT IN (SELECT ID FROM
schStoer.DB_STOERUNG)
Allerdings bin ich mir grad nicht sicher ob das so wirklich funktioniert



Das funktioniert schon.
Unten mal ein etwas abstrakteres Beispiel.

(bekomme abundan noch Fehlermeldungen, die aber vielleicht auch von anderen
Einschrànkungen herrühren können)



Die Daten die Du in die Referenz-Tabelle einfügen willst,
müssen natürlich allen Anforderungen genügen.
Wenn nicht, und sie kommen von der Anwendung gilt auch hier: ROLLBACK!

und vorallem ob das von der Performance her so günstig ist?



(Zusàtzliche) Trigger machen es immer etwas langsamer,
aber in vielen Fàllen sollte das verkraftbar sein.

Eher zu bedenken ist der zusàtzlich zu wartende Code.
Wann immer möglich sollte man IMO auf Trigger (INSTEAD OF
insbesondere) verzichten und auf direkte INSERTs - was hier
dann zwei wàren, zurückgreifen.

Gruß Elmar


USE tempdb;
GO

CREATE TABLE dbo.Tabelle1 (
t1_id int not null PRIMARY KEY,
t1_daten varchar(40) not null);

CREATE TABLE dbo.Tabelle2 (
t2_id int not null PRIMARY KEY,
t1_id int not null REFERENCES dbo.Tabelle1 (t1_id),
t2_daten varchar(40));

INSERT INTO dbo.Tabelle1 VALUES (1, 'Zeile 1')
INSERT INTO dbo.Tabelle2 VALUES (101, 1, 'Zeile 101 zu Zeile 1');
INSERT INTO dbo.Tabelle2 VALUES (102, 1, 'Zeile 102 zu Zeile 1');
INSERT INTO dbo.Tabelle2 VALUES (103, 1, 'Zeile 103 zu Zeile 1');

INSERT INTO dbo.Tabelle1 VALUES (2, 'Zeile 2')
INSERT INTO dbo.Tabelle2 VALUES (201, 2, 'Zeile 201 zu Zeile 1');
INSERT INTO dbo.Tabelle2 VALUES (203, 2, 'Zeile 303 zu Zeile 1');
GO

CREATE TRIGGER TI_Tabelle2
ON dbo.Tabelle2
INSTEAD OF INSERT
AS
SET NOCOUNT ON;

INSERT INTO dbo.Tabelle1 (t1_id, t1_daten)
SELECT DISTINCT i.t1_id, 'Neue Zeile'
FROM inserted AS i
WHERE NOT EXISTS (SELECT *
FROM dbo.Tabelle1
WHERE i.t1_id = Tabelle1.t1_id);
IF @@ERROR <> 0
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
RETURN ;
END

INSERT INTO dbo.Tabelle2 (t2_id, t1_id, t2_daten)
SELECT i.t2_id, i.t1_id, i.t2_daten
FROM inserted AS i;
GO

INSERT INTO dbo.Tabelle2 (t1_id, t2_id, t2_daten)
SELECT 1, 104, 'Eine neue Zeile 104'
UNION ALL SELECT 2, 202, 'Eine neue Zeile 202'
UNION ALL SELECT 3, 301, 'Eine neue Zeile 301'
UNION ALL SELECT 3, 302, 'Eine neue Zeile 302'
GO

SELECT * FROM Tabelle1
SELECT * FROM Tabelle2
GO

DROP TABLE dbo.Tabelle2, dbo.Tabelle1
GO

Ähnliche fragen