SQL Statement herausfinden, das einen Trigger auslöst

Question

Hallo zusammen,

ich habe folgendes Problem und komme nicht weiter. In einem Update Trigger (Wir arbeiten auf SQL-Server 2005 oder 2008, TSQL) muss ich das Statement analysieren, welches den Update ausgelöst hat, um entweder eine Aktion auszuführen oder nicht.

Um an das aktuelle Statement heranzukommen habe ich folgendes ausprobiert:

select @SQL_Text = [text]

  from sys.sysprocesses p

CROSS APPLY

  sys.dm_exec_sql_text(p.sql_handle) AS st

where spid=@@spid       

Aber mit diesem Statemtn bekomme ich nur den Quelltext des Trigger zurück (also Create Trigger ... usw.) aber nicht das Update Statement  (Update ..  Set .. Where usw.). Das hilft mir also gar nicht weiter.

Eine andere Möglichkeit habe ich aber nicht gefunden.

Es wäre prima, wenn da noch jemand eine andere Lösung hätte

vielen Dank.

Answer 1

Hallo JSpatz,

das wird IMO so nicht gehen. Da Du das Statement im Trigger ausführst, ist die Anweisung ja bereits durch. Ich würde die einzelnen Statements in Stored Procedures kapseln. Innerhalb der Prozeduren kannst Du dann entsprechend reagieren.

Hier mal ein Beispiel:

USE tempdb
GO

CREATE TABLE dbo.tblStammdaten
(
	SId	int				IDENTITY (1, 1),
	Vorname	nvarchar(255),
	Nachname	nvarchar(255),
	
	CONSTRAINT pk_tblStammdaten PRIMARY KEY (SId)
)

-- Dein SQL Statement
INSERT INTO dbo.tblStammdaten
(Vorname, Nachname)
VALUES
('Uwe', 'Ricken')
GO

-- Und so wäre es dann mit SP
CREATE PROC dbo.proc_app_InsertStammsatz
	@Vorname	nvarchar(255),
	@Nachname	nvarchar(255)
AS
	SET NOCOUNT ON
	DECLARE	@ReturnValue	int
	
	PRINT 'Hier könnte Dein Code stehen...'
		
	INSERT INTO dbo.tblStammdaten
	(Vorname, Nachname)
	VALUES
	(@Vorname, @Nachname)
	
	SET	@ReturnValue = SCOPE_IDENTITY()

	RETURN	ISNULL(@ReturnValue, 0)
	SET NOCOUNT OFF
GO

-- Ausführen...
DECLARE	@ReturnValue	int
EXEC	@ReturnValue	=	dbo.proc_app_InsertStammsatz 'Klaus', 'Meier'
SELECT	@ReturnValue

SELECT * FROM dbo.tblStammdaten

DROP TABLE dbo.tblStammdaten

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 2

Hallo Uwe,

leider kann ich diese Lösung bei mir nicht einsetzen. Als Begündung muss ich wohl etwas weiter ausholen:
Es geht bei mir um eine Datenbank, die von der Gupta SQL-Base auf SQL-Server 2008 portiert wurde. Die bestehende (sehr große) Applikation kann ich nicht ändern, sondern ich muss versuchen die Änderungen in der Datenbank unterzubringen, damit die Applikation läuft.
Nun hat die Gupta-Base ein Besonderheit: Es gibt dort in jeder Tabelle eine System-Spalte ROWID, die so etwas wie eine Rowversion oder einen einen Timestamp darstellt, die sich bei jedem Update der Zeile ändert. Diese Spalte wird im Where-Teil von Update-Statements verwendet um "Optimistic Locking" zu realisieren, d.h. festzustellen, ob inzwischen ein anderer Anwender die Zeile geändert hat.

Ein Update-Statment für eine bestimmte Zeile sieht dann so aus:

Update Tabelle
Set
  SpalteX = XYZ
Where
  RowID = OldRowID

Wenn dieser Update schiefgeht gibt die Gupta-Base einen Fehler zurück, um der Applikation mitzuteilen, dass diese RowID inzwischen nicht mehr existiert, d.h. ein anderer Anwender den Datensatz geändert hat. Dieser Fehler wird in der Applikation behandelt und dem Anwender mitgeteilt, dieser lädt dann den neuen Datenatz und kann dann die Änderung noch mal probieren.

Diese Spalte RowID kann ich im SQL-Server über eine Timestamp wunderbar nachbilden, aber wenn ich das oben gezeigt Update-Statement ausführe und eine anderer Anwender hat die Zeile inzwischen geändert gibt der SQL-Server keinen Fehler zurück, da das Statement an sich ja völlig korrekt ist.

Die Idee war jetzt einen Trigger in jeder Tabelle zu setzen, der nach dem Update prüft, ob mindestens eine Zeile durch den Update verändert wurde und wenn nein dann einen Benutzerfehler zu werfen.

In dieser Art:

CREATE TRIGGER DBO.Tabelle_Test_U
ON DBO.Tabelle_Test
AFTER UPDATE
AS
DECLARE @CountUpdate int

BEGIN
 DECLARE myCursor CURSOR LOCAL FOR SELECT Count(*) FROM INSERTED
 OPEN myCursor

 FETCH NEXT FROM myCursor INTO @CountUpdate
 IF @CountUpdate  = 0
 BEGIN
     RAISERROR ('RowID-Fehler', 16, 1 );
 END

 CLOSE myCursor
 DEALLOCATE myCursor
END
GO

(Hier hat man auch keine Probleme mit Rollback oder so, da ja eh keine Zeile verändert wurde)

Das funktioniert auch wunderbar nur jetzt hat man das Problem, dass es ja auch Updates ohne RowID in der Where-Klause gibt und die dürfen den Fehler aber nicht werfen (irgendwelche Admin-Scripte oder so etwas, die ja ab und zu auch keine Zeilen erwischen.)

Deswegen wa die Idee im Trigger das ausführende SQL-Statement zu analysieren, ob im Where -Teil der Text "ROWID" vorkommt, und den Fehler dann nur zu werfen, wenn das der Fall ist.

Ich hoffe, dass mein Problem damit klarer ist

Grüße und Danke

Jürgen Spatz

 

Answer 3

Hallo Jürgen,

dann geht es so natürlich nicht. Stattdessen würde ich dann einen INSTEAD OF Trigger verwenden. Hier mal ein Codebeispiel (erstes Beispiel leicht abgewandelt).

USE tempdb
GO

CREATE TABLE dbo.tblStammdaten
(
	SId	int				IDENTITY (1, 1),
	Vorname	nvarchar(255),
	Nachname	nvarchar(255),
	rowguid	uniqueidentifier NOT NULL DEFAULT (newid()),	
	
	CONSTRAINT pk_tblStammdaten PRIMARY KEY (SId)
)
GO

CREATE TRIGGER dbo.Stammdaten_Update
ON dbo.tblStammdaten
INSTEAD OF UPDATE
AS
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT * FROM inserted)	
	BEGIN
		RAISERROR ('Der Datensatz existiert nicht!', 11, 1)
		GOTO ExitCode
	END
	
	UPDATE	s
	SET	s.Vorname = i.Vorname,
		s.Nachname = i.Nachname
	FROM	dbo.tblStammdaten s INNER JOIN inserted i
		ON (s.rowguid = i.rowguid)
	
ExitCode:
	SET NOCOUNT OFF
GO

-- Dein SQL Statement
INSERT INTO dbo.tblStammdaten
(Vorname, Nachname)
VALUES
('Uwe', 'Ricken')
GO

-- Aktualisierung des Datensatzes
UPDATE	dbo.tblStammdaten
SET	Nachname = 'Meier'
WHERE	rowguid = 'DeinEindeutigerSchluessel'

SELECT * FROM dbo.tblStammdaten

DROP TABLE dbo.tblStammdaten

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 4

Hallo Jürgen,

mir ist derzeit auch nicht bekannt, wie man in einem Trigger das auslösende SQL Statement ermitteln könnte. Zudem wäre das Parsen des SQL Statements auch nicht so ganz ohne, wenn es den zuverlässig arbeiten soll.

Eine Lösungsmöglichkeit wäre mit Context_Info zu arbeiten und das im Trigger auszuwerten. Die Applikation arbeitet ohne Context_Info (sie kennt es nicht und Du kannst sie nicht ändern). Wenn ein Admin Update-Statements ausführen möchte, muss er Context_Info setzen; siehe Using Session Context Information.

Im Trigger wertest Du dann aus: Ausserhalb Context & Anzahl Änderungen = 0 => Fehler; im Context: Nie Fehler. Beispiel:

USE [TempDB]

GO

 

CREATE TABLE dbo.dummy (ID int, Wert varchar(10));

INSERT INTO dummy VALUES (1, 'Wert 1');

INSERT INTO dummy VALUES (2, 'Wert 2');

GO

 

CREATE TRIGGER dbo.TrgUpdDummy ON dbo.dummy

AFTER UPDATE

AS

BEGIN

    DECLARE @del int, @ins int;

   

    SET @del = (SELECT COUNT(*) FROM deleted);

    SET @ins = (SELECT COUNT(*) FROM inserted);

   

    IF EXISTS (SELECT 1 WHERE CONTEXT_INFO() = 0x01010101)

        SELECT 'In context', @del, @ins;

    ELSE

        SELECT 'Out of Context', @del, @ins;   

END

 

GO

 

-- Update von App ohne Context

UPDATE dbo.dummy

SET Wert = Wert + ' neu'

WHERE ID = 1;

 

-- Update von Admin mit Context

SET CONTEXT_INFO 0x01010101

UPDATE dbo.dummy

SET Wert = Wert + ' neu'

WHERE ID = 3;

GO

 

-- Context zurück setzten

SET CONTEXT_INFO 0x0

GO

 

DROP TABLE dbo.dummy;

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 5

HI,

nochmals danke für die Mühe, aber auch das wird leider nicht funktionieren, weil ja nicht alle Updates über die RowID laufen und dann würde der Trigger ins leere laufen.

Grüße

Jürgen Spatz

Answer 6

Hallo,

ich befürchte auch das wird nicht funktionieren, weil es auch innerhalb der Applikation Updates ohne RowID gibt und dort kann ich keine Context-Info setzen

Danke für die Mürhe

Jürgen Spatz 

Answer 7

use tempdb
go

create table t1 (id int identity, col1 nvarchar(55), version timestamp)
go

create trigger TR_T1_IOU on t1
instead of update 
as
begin
	declare @ins as table(id int)

	update t1
	set col1 = i.col1
	output inserted.id into @ins
	from t1 t inner join inserted i	on t.id = i.id
	and (t.version = i.version or i.version is null)
	
	if exists(select id
	from inserted i
	where i.id not in(select id from @ins))
	begin
		declare @ids nvarchar(100)
		
		select @ids = isnull(@ids,'') + cast(id as nvarchar(32)) + ';'
		from inserted i
		where i.id not in(select id from @ins)

		raiserror('Die Zeilen mit der ID %s wurden inzwischen geändert oder gelöscht!',16,1,@ids)
	end

end
go

insert into t1(col1) values('testtext 1111'),('testtext 2222')
go

declare @t1 as table(id int, col1 nvarchar(55), version binary(8))

insert into @t1
select id,col1,version from t1

--update 1: Daten werden aktualisiere
update t1 set col1 = t.col1, version = t.version
from t1 inner join @t1 t
on t1.id = t.id

--update 2: liefert die Fehlermeldung:
--Msg 50000, Level 16, State 1, Procedure TR_T1_IOU, Line 24
--Die Zeilen mit der ID 1;2; wurden inzwischen geändert oder gelöscht!
update t1 set col1 = t.col1, version = t.version
from t1 inner join @t1 t
on t1.id = t.id

drop trigger TR_T1_IOU
go
drop table t1
go

Answer 8

hi,

wie wird denn in deiner SQLBase-Umgebung eine administratives UPDATE ausgeführt? Woher weiß SQLBase das?

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 9

Hi

Diese Unterscheidung gibt es dort nicht. Es gibt nur Updates der Form

Update ..

Where

   RowID = :OldRowID and ...

Hier wird der Fehler gesetzt, weil die Datenbank sieht: aha, hier findet ein Update über die RowID statt

oder es gibt Updates in der Form

Update ...

Where

    A = B

Und hier wird der Fehler nicht gesetzt, wenn A = b auf keine Zeile zutrifft.  Wie schon gesagt ist die Spalte ROWID eine Systemspalte, die jede Tabelle automatisch hat, die muss nicht vom User angelegt werden. Deswegen kennt die Datenbank auch diese Unterscheidung

Grüße

Jürgen Spatz

 

 

Answer 10

Hi

Ich befürchte auch diese Lösung geht davon aus, dass er nur Updates mit "Where RowID = :OldRowID" gibt, aber wenn das so wäre, dann würde der ursprungliche Trigger auch funktionieren. Er darf aber bei Updates der Form

Update ...

Where

A = B

Eben kein Fehlermeldung ausgeben, wenn keine Zeile betroffen ist und das ist Problem

Grüße

Jürgen Spatz

Answer 11

ich befürchte auch das wird nicht funktionieren, weil es auch innerhalb der Applikation Updates ohne RowID gibt und dort kann ich keine Context-Info setzen

Ist das nicht etwas widersprüchlich zu den bisherigen Aussagen? Und SQLBase müsste hier doch auch einen Fehler liefern, wenn 0 Datensätze betroffen sind?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 12

hi,

wenn das auf Datenbank-Level passiert, schauts schlecht aus.

Dann hilft imho nur ein SQL Proxy der entsprechend die Statements anpasst. Das kann auch eine Proxy-Layer um den Datenbankzugriff herum sein. Das steht und fällt imho aber mit den verwendeten Zugriffsmechanismen.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 13

Hi,

nein, ich denke das ist kein Wiederspruch. Die oben angesprochenen Admin-Scripte waren nur als Beispiel gedacht, weil mir nichts besseres eingefallen ist. Ich denke in jeder größeren Applikation gibt es einerseits Updates über den PK oder ein eindeutiges Merkmal, die dann genau einen Datensatz ändern, und natürlich will der Anwender dann wissen, ob der Update funktioniert hat, bzw. ob vorher ein anderer Nutzer die Daten verändert hat.

Genauso gibt es aber auch größere Updates die mehere Datensätze umfassen (und dann eben nicht über ein eindeutiges Kriterium gehen) und dann aber auch 0 Daten erfassen können.

Wie schon weiter unten ausgeführt ist die Spalte ROWID eine spezielle System-Spalte in dem alten Datenbanksystem, und abhängig davon, ob die ROWID in der Where-Bedingung steht oder nicht wird der Fehler zurückgegeben, wenn der Update keine Zeilen ändert.

Vielen Dank für die Mühen bisher

Jürgen Spatz

Answer 14

Was spricht dann gegen die Logik:

Wenn Anzahl Datensätze = 0 und kein Context_Info gesetzt ist, wird ein Fehler ausgegeben, sonst eben nicht.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 15

hi,

sieht auf den ersten Blick vielversprechend aus, aber:

DECLARE @timestamp TIMESTAMP ;

SELECT  @timestamp = version
FROM    t1
WHERE   ID = 1 ;

SELECT  *
FROM    t1 ;

UPDATE  t1
SET     col1 = col1 + '!'
WHERE   version = @timestamp ;

SELECT  *
FROM    t1 ;

-- sollte einen Fehler schmeissen, tut's aber nicht.
UPDATE  t1
SET     col1 = col1 + '!'
WHERE   version = @timestamp ;

SELECT  *
FROM    t1 ;

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 16

Hallo

wie eben schon angeführt werden beide möglichen Versionen der Update-Statements in der Applikation verwendet und in der Applikation kann ich keinen Context_Info setzen, weil ich die nicht ändern kann (bzw. will, weil die riesengroß ist). Und dann bin ich genausoweit wie in meiner ersten Version.

 

Trotzdem Danke

Jürgen Spatz

Answer 17

Hallo Jürgen,

unter Umständen kommst Du mit

  DBCC INPUTBUFFER( @@spid )
  (Quelle: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/65252/Get-firing-DML-of-trigger)

an das Statement. Allerdings kann ich dir so aus dem Stehgreif nicht sagen, wie man das weiterverarbeiten kann.

Das obige funktioniert aber auch nur, wenn das UPDATE Statement direkt abgesetzt wird, nicht, wenn man bspw. eine SP per EXEC spname und dort dann das UPDATE Statement ausführt.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 18

Ich befürchte auch diese Lösung geht davon aus, dass er nur Updates mit "Where RowID = :OldRowID" gibt, aber wenn das so wäre, dann würde der ursprungliche Trigger auch funktionieren. Er darf aber bei Updates der Form.....

Jürgen Spatz

Hallo Jürgen,

ist es eben nicht! :)

Ich habe meine Lösung erweitert, so dass die Updates update table set col = <value> where a=b ohne Fehler ausgeführt werden können.

Um die Version (Timestamp usw.) zu berücksichtigen, muss die Spalte explizit mit dem Update aktualisiert werden update table set col = value, version = old_version where <update Bedingungen>, ansonsten sind Deine Anforderungen mit einem Trigger nicht realisierbar.

Answer 19

Hallo,

tut mir leid, dann hatte ich die Lösung anscheinend nicht verstanden. OK, ich werden das ganz mal in meinem testsystem ausprobieren und schauen, ob ich weiterkomme.

 

Vielen Dank erst mal an alle, die sich hier mühe gegeben haben

Grüße

Jürgen Spatz

Answer 20

  DBCC INPUTBUFFER( @@spid )
  (Quelle: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/65252/Get-firing-DML-of-trigger)

Hallo Stefan,

diese Idee finde ich auch recht verlockend - könnte ev. so funktionieren:

DECLARE	@spid	int
DECLARE	@stmt	nvarchar(255)
DECLARE	@parms	nvarchar(255)
SET		@stmt	=	'DBCC INPUTBUFFER(@spid)'
SET		@parms	=	'@spid int'

SET		@spid	=	@@SPID

DECLARE	@t TABLE
(
	EventType	nvarchar(255),
	Parameters	int,
	EventInfo	nvarchar(max)
)

INSERT INTO @t
EXEC sp_executeSQL @stmt, @parms, @spid = @spid

SELECT * FROM @t

Die Werte könnte man dann ja aus  @t auslesen...

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 21

Hallo zusammen,

 

vielen Dank noch mal für die konstruktiven Vorschläge und Ideen ich werde die nächsten Tage hoffentlich dazu kommen alles ausführlich zu testen und denke dass ich damit das Problem in den Griff bekommen.

Grüße

Jürgen Spatz