verwendetes Datenbanksystem: MS SQL Server 2008
Hallo Zusammen
Ich habe eine history Tabelle, welche ich bei Insert und Update mit einem Trigger befülle. Soweit die Theorie. Hier gleich mal der Trigger-Code:
CODE Deleted
Mache ich nun ein Update auf die mit Triggern versehene Tabelle, wird nur ein einziges der IF UPDATE(column) Statements ausgefürht. Alle weiteren schweigen.
Es schaut so aus, als würde nur das ERSTE ausgeführt und der Trigger dann verlassen.
Mit erste meine ich die Reihenfolge im Update Query.
update tbl_Equipment
set ID_Status = same,
ID_Agreement = same,
NetbiosName = NEW,
Note = NEW
where ID_Equipment = ID
Obiger Code sollte mir also 2 einträge in der History tabelle bescheren, ich erhalte aber nur einen. Nämlich den NetbiosName wechsel. Note wird Ignoriert!
Woran liegt das? Und wie kann ich dieses Problem Lösen?
Freundlichst
Gurrnder
ps. Kann man den CODE tag irgendwie in der Höhe beschränken?
Hallo Gurrnder
Ich kann so keinen Feher feststellen. Bis du sicher dass du neue Werte in die Spalten NetbiosName und Note einfügst?
Kommentiere testweise mal alles außer dem UPDATE für die Note Spalte in deinem Trigger aus und versuch's noch mal.
Hier ein Beispiel das ich gerade mal getestet habe welches funktioniert:
---========================================================
-- Test Tabelle
IF (OBJECT_ID('TestTrigger') IS NULL)
CREATE TABLE TestTrigger
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
SomeInt INT,
SomeVarChar VARCHAR(30)
);
GO
---========================================================
-- Hist Tabelle
IF (OBJECT_ID('TestTrigger_Hist') IS NULL)
CREATE TABLE TestTrigger_Hist
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
ObjectId INT NOT NULL,
ValueOld VARCHAR(100),
ValueNew VARCHAR(100)
);
GO
---========================================================
-- Trigger to handle auditing
IF (OBJECT_ID('TR_TestTrigger_Hist') IS NULL)
EXECUTE ('CREATE TRIGGER TR_TestTrigger_Hist ON TestTrigger FOR UPDATE AS DECLARE @i INT')
GO
ALTER TRIGGER TR_TestTrigger_Hist ON TestTrigger
FOR UPDATE
AS
-- Update for SomeInt
IF (UPDATE(SomeInt))
BEGIN
INSERT INTO TestTrigger_Hist (
ValueOld
,ValueNew
,ObjectId
)
SELECT
CONVERT(VARCHAR(100), d.SomeInt)
,CONVERT(VARCHAR(100), i.SomeInt)
,i.Id
FROM inserted i
JOIN deleted d ON i.Id = d.Id;
END
-- Update for SomeVarChar
IF (UPDATE(SomeVarChar))
BEGIN
INSERT INTO TestTrigger_Hist (
ValueOld
,ValueNew
,ObjectId
)
SELECT
d.SomeVarChar
,i.SomeVarChar
,i.Id
FROM inserted i
JOIN deleted d ON i.Id = d.Id;
END
GO
---========================================================
-- Insert one sample row
INSERT INTO TestTrigger
SELECT 1, 'Blah'
-- Get new Id
DECLARE @Id INT = SCOPE_IDENTITY();
-- Update two columns
UPDATE TestTrigger SET
SomeInt = 2,
SomeVarChar = 'Bluff'
WHERE Id = @Id
-- Show history result
SELECT *
FROM TestTrigger_Hist
WHERE ObjectId = @Id
Grüße
Flo
Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+
Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.
Bahnbrechende neuigkeiten.
Wenn ich das Update Command direkt am Management Studio abfeuere, funktioniert der Trigger einwand frei.
Wird das Command aber aus C# heraus ausgeführt, wird nur die erste veränderte spalte historysiert ... gespeichert werden aber alle Änderungen!
Problem gefunden, Beschreibung Folgt:
Also. Wenn ich ein Feld von x auf y update, funktioniert die sache.
Ist ein feld aber NULL und wird auf x gesetzt, taucht dies nicht in der History auf.
NULL --> 'etwas' wird also von IF UPDATE() nicht als update erkannt und desswegen nicht bearbeitet.
Nein. UPDATE() funktioniert (natürlich!) auch bei NULL Werten. Hättest du deinen Trigger jetzt nicht gelöscht (warum eigentlich??) hätte ich dir den Fehler zeigen können. Du kannst NULL Werte nicht mit "=" vergleichen. Du musst "ISNULL" oder "IS NULL" verwenden.
Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+
Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.
USE [Harem]
GO
/****** Object: Trigger [dbo].[tr_History_Update] Script Date: 08/26/2009 09:57:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_History_Update]
on [dbo].[tbl_Equipment]
for update
AS
BEGIN
SET NOCOUNT ON;
if (update([ID_Equipment]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[ID_Equipment] as varchar(8000)) as ValueOld,
cast(i.[ID_Equipment] as varchar(8000)) as ValueNew,
'ID_Equipment' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[ID_Equipment] != d.[ID_Equipment])
end
if (update([ID_Status]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[ID_Status] as varchar(8000)) as ValueOld,
cast(i.[ID_Status] as varchar(8000)) as ValueNew,
'ID_Status' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[ID_Status] != d.[ID_Status])
end
if (update([ID_Agreement]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[ID_Agreement] as varchar(8000)) as ValueOld,
cast(i.[ID_Agreement] as varchar(8000)) as ValueNew,
'ID_Agreement' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[ID_Agreement] != d.[ID_Agreement])
end
if (update([ID_EquipmentModel]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[ID_EquipmentModel] as varchar(8000)) as ValueOld,
cast(i.[ID_EquipmentModel] as varchar(8000)) as ValueNew,
'ID_EquipmentModel' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[ID_EquipmentModel] != d.[ID_EquipmentModel])
end
if (update([DeviceNo]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[DeviceNo] as varchar(8000)) as ValueOld,
cast(i.[DeviceNo] as varchar(8000)) as ValueNew,
'DeviceNo' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[DeviceNo] != d.[DeviceNo])
end
if (update([DateOfPurchase]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[DateOfPurchase] as varchar(8000)) as ValueOld,
cast(i.[DateOfPurchase] as varchar(8000)) as ValueNew,
'DateOfPurchase' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[DateOfPurchase] != d.[DateOfPurchase])
end
if (update([ExpiryOfWarranty]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[ExpiryOfWarranty] as varchar(8000)) as ValueOld,
cast(i.[ExpiryOfWarranty] as varchar(8000)) as ValueNew,
'ExpiryOfWarranty' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[ExpiryOfWarranty] != d.[ExpiryOfWarranty])
end
if (update([NetbiosName]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[NetbiosName] as varchar(8000)) as ValueOld,
cast(i.[NetbiosName] as varchar(8000)) as ValueNew,
'NetbiosName' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[NetbiosName] != d.[NetbiosName])
end
if (update([Note]) )
begin
insert into dbo.tbl_History
(ID_ConcernedEntry, ValueOld, ValueNew, ConcernedField, ConcernedTable, ID_HistoryType, LoginName, [TimeStamp])
(select i.[ID_Equipment] as ID_ConcernedEntry,
cast(d.[Note] as varchar(8000)) as ValueOld,
cast(i.[Note] as varchar(8000)) as ValueNew,
'Note' as ConcernedField,
'tbl_Equipment' as ConcernedTable,
2 as ID_HistoryType,
SYSTEM_USER as LoginName,
getdate() as TimeStamp
from Inserted i
join Deleted d on i.[ID_Equipment] = d.[ID_Equipment]
where i.[Note] != d.[Note])
end
END
GO
Du musst NULL Werte gesondert behandeln. NULL ist undefiniert und kann mit "=" nicht verglichen werden.
Im Falle deiner "Note" Spalte musst du die WHERE Klausel wie folgt anpassen:
-- ...
where
i.[Note] != d.[Note]
OR (i.Note IS NULL AND d.Note IS NOT NULL)
OR (i.Note IS NOT NULL AND d.Note IS NULL)
Folgendes Beispiel kannst du 1:1 kopieren und ausführen. Es zeigt, dass weder "=" noch mit "!=" mit NULL verwendet werden kann.
DECLARE @t TABLE (Col1 INT, Col2 INT)
INSERT INTO @t
SELECT 1, 1
UNION ALL SELECT 1, 2
UNION ALL SELECT 1, NULL
UNION ALL SELECT NULL, NULL
SELECT * FROM @t WHERE Col1 = Col2
SELECT * FROM @t WHERE Col1 != Col2
Grüße
Flo
Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+
Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.
Das ich da auf die NULL werte achten muss habe ich total verschwitzt.
Danke für deine Hilfe!
Immer gerne 😉
Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+
Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.