Gleich vorwegschicken möchte ich, daß dieser Beitrag nicht zur Nachahmung oder zum Einsatz in Produktivsystemen gedacht ist. Vielmehr eher als reine Spielerei mit dem, was möglich ist.
Also, kann die Ausführung eines Triggers umgangen werden? Einfache Antwort: Ja! Und zwar auf mehreren Wegen, von denen ich hier nur einen anführen will.
Die Ausgangssituation:
CREATE TABLE dbo.test_trigger
(
c1 INT
, c2 CHAR(10)
)
GO
CREATE TRIGGER fool_me ON dbo.test_trigger FOR INSERT
AS
BEGIN
UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
END
GO
Dieser Trigger soll also bei jedem INSERT den Wert 'Hallo Welt' in die Spalte c2 schreiben. Das das funktioniert, kann man einfach feststellen:
INSERT INTO dbo.test_trigger (c1) VALUES(1)
INSERT INTO dbo.test_trigger (c1, c2) VALUES(1, 'wtf')
SELECT * FROM dbo.test_trigger
c1 c2
----------- ----------
1 Hallo Welt
1 Hallo Welt
So, egal,ob man nun explizit einen Wert in die Spalte c2 eintragen will oder nicht, der Wert wird durch den Trigger überschrieben.
Die Manipulation:
Und zwar nutzen wir die Spalte context_info in der master.dbo.sysprocesses Tabelle. Diese kann durch die Einstellung SET CONTEXT_INFO zusätzliche 128 Bytes pro Session oder Connection speichern. Zunächst ändern wir mal unseren Trigger ab.
ALTER TRIGGER fool_me ON dbo.test_trigger FOR INSERT AS
IF NOT (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@SPID) = 0xA
BEGIN
UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
END
GO
So, nun können wir SET CONTEXT_INFO verwenden:
SET CONTEXT_INFO 0xA
INSERT INTO dbo.test_trigger (c1, c2) VALUES(2, 'q.e.d')
SET CONTEXT_INFO 0
Das Ergebnis:
SELECT * FROM dbo.test_trigger
c1 c2
----------- ----------
1 Hallo Welt
1 Hallo Welt
2 q.e.d
Nochmals der Hinweis: Dies ist kein Skript, was in Produktionssystemen verwendet werden sollte. Die Manipulation von SET CONTEXT_INFO kann zu Nebeneffekten führen bei anderen DB Objekten, die ebenfalls auf diese Einstellung zugreifen.
Danke an Jonathan van Houtte!
Vielleicht hat sich der Eine oder Andere schon mal gefragt, wieso und warum SQL Server bei einem SELECT * Statement die Spalte in der Art und Weise zurückgibt, in der es geschieht. Zum Beispiel:
CREATE TABLE #test
(
col1 CHAR
, col2 INT
, col3 FLOAT
, col4 VARCHAR
)
GO
INSERT INTO #test VALUES('a',1,0.9,'A')
SELECT * FROM #test
col1 col2 col3 col4
---- ----------- ----------------------------------------------------- ----
a 1 0.90000000000000002 A
Die Reihenfolge der Spalten entspricht bei einem SELECT * immer stets der Reihenfolge, in der sie beim CREATE TABLE Statement angegeben wurden. Das heißt, im Grunde werden die Spalten in aufsteigender Reihenfolge der colid in der Tabelle syscolumns zurückgegeben. Hier ist ein Ausschnitt aus
EXEC sp_help #test
Column_name
------------
col1
col2
col3
col4
Und ist der entsprechende Ausschnitt aus syscolumns:
SELECT
CAST(name AS CHAR(30)) AS [name]
, colid
FROM
syscolumns
WHERE
id=OBJECT_ID('#test')
name colid
------------------------------ ------
col1 1
col2 2
col3 3
col4 4
Erwähnt werden sollte auch, daß ein SELECT * Statement nichts in Produktionscode zu suchen hat, IMHO. Und das die physikalische Speicherung der Storage Engine wiederum eine komplett andere ist.
Die nachfolgenden Informationen gelten nur für AFTER Trigger, nicht für INSTEAD OF Trigger. Grundsätzlich kann man (theoretisch) beliebig viele AFTER Trigger pro Tabelle definieren. Die Anzahl ist hierbei nur durch die maximalen Objekte pro Datenbank von 2.147.483.647 beschränkt. Wenn es nun auf eine gewisse Abfolge ankommt, in welcher die Trigger abgefeuert werden, so kann man auf diese einen gewissen Einfluß ausüben durch die Systemprozedur sp_settriggerorder. Diese hat folgende Syntax:
sp_settriggerorder[@triggername = ] 'triggername' , [@order = ] 'value' , [@stmttype = ] 'statement_type'
Ein beispielhafter Aufruf sieht dann folgendermaßen aus:
Den Einfluß, den man ausüben kann beschränkt sich aber darauf, festzulegen, welcher Trigger als Erster und welcher Trigger als Letzter abgefeuert wird. Dies erfolgt durch Angabe von
@order='first'
oder
@order='last'
Dazwischen ist die Reihenfolge der Ausführung undefiniert.
@order='none'
Weitere Erklärungen stehen in BOL.
Original von Narayana Vyas Kondreddi; deutsche Übersetzung von Frank Kalis
Neulich stellte jemand in den öffentlichen Microsoft Newsgroups die Frage, wie man Zeichen innerhalb eines Strings sortiert? Zum Beispiel enthielt der String 'CBA' und er wollte nun die Zeichen innerhalb des String sortieren, um daraus 'ABC' zu machen. Anstelle nun die prozedurale Lösung zu verwenden, habe ich versucht, rein aus Spaß, dies mit einem relationalen Ansatz (T-SQL spezifisch) zu lösen. In diesem Artikel zeige ich Ihnen, wie Sie eine Zahlentabelle verwenden können, um diese innovative Art von Abfragen zu schreiben.
Wie so oft mag man sich fragen, ob dies eher die Aufgabe des Clients als die des Server ist, aber da man häufig derartige Fragestellungen beobachten kann, hier an dieser Stelle vielleicht ein paar Lösungsansätze zu folgendem Problem:
Dies ist ein beliebtes Beispiel für Informatikstudenten im Anfangsstadium, um die Auswirkungen effizienter Algorithmen zu demonstrieren. Also auch hier nicht unbedingt etwas, was man zwingend in einer Datenbank machen müßte, das sich aber durchaus mengenbasiert lösen läßt. Zu diesem Algorithmus gibt es eine kleinen Anekdote:
Dem "Entdecker" Carl-Friedrich Gauß wurde in der Schule die Aufgabe gestellt, die Summe aller Zahlen von 1 bis 100 zu berechnen. Alle Kinder rechneten los, Gauß schrieb kurz etwas auf seine Tafel und riss nach kurzer Zeit seinen Lehrer aus dessen Ruhepause. Die Formel stimmte natürlich! Wer es genauer nachlesen möchte, kann sich mal hier umschauen.
DECLARE @n BIGINT SET @n = 100 SELECT (@n+@n*@n)/2 -------------------- 5050 (1 row(s) affected)
oder als UDF
CREATE FUNCTION dbo.achtsieben(@n BIGINT) RETURNS BIGINT AS BEGIN RETURN (@n+@n*@n)/2 END GO SELECT dbo.achtsieben(100) DROP FUNCTION dbo.achtsieben -------------------- 5050 (1 row(s) affected)
Zu dem Namen, den ich der Funktion gegeben habe, gibt es auch eine Anekdote:
Als ich unsere Mathematiker nach der "richtigen" Bezeichnung für diese Formel gefragt habe, kam als Antwort:
"Wir nennen das die 78-er Regel, da die Summe der Monate eines Jahres 78 ist."
Ein kurzer Test ergibt:
DECLARE @n BIGINT SET @n = 12 SELECT (@n+@n*@n)/2 -------------------- 78 (1 row(s) affected)
Stimmt!
Nachtrag 27.08.2004: Auch im SQL Server kann man damit die Notwendigkeit zum Einsatz von effizienten Algorithmen demonstrieren. Dazu bauen wir uns mal folgendes Testskript zusammen:
DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO DECLARE @start DATETIME SET @start = GETDATE() DECLARE @n BIGINT SET @n = 200000 SELECT (@n+@n*@n)/2 SELECT GETDATE()-@start AS Zeit DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO DECLARE @start DATETIME DECLARE @n BIGINT DECLARE @result BIGINT SET @start = GETDATE() SET @n = 1 SET @result = 0 WHILE @n <= 200000 BEGIN SET @result = @result + @n SET @n = @n + 1 END SELECT @result SELECT GETDATE()-@start AS Zeit
Nach Ausführung erhält man folgendes Ergebnis:
... -------------------- 20000100000 (1 row(s) affected) Zeit ------------------------------------------------------ 1900-01-01 00:00:00.010 (1 row(s) affected) ... -------------------- 20000100000 (1 row(s) affected) Zeit ------------------------------------------------------ 1900-01-01 00:00:01.513 (1 row(s) affected)
Während der Gauß Algorithmus fast augenblicklich das Ergebnis zurückliefern, braucht die iterative Methode deutlich länger!
Vielleicht mag das nicht viel erscheinen, aber jetzt stelle ich mir die Auswirkungen auf ein System vor, in dem ein solcher algorithmus in einer stark frequentierten Prozedur implementiert wurde, die mehrere Tausend Male pro Stunde aufgerufen wird. Nun sieht die Sache schon etwas anders aus. Jetzt mag man natürlich mit Caching argumentieren, aber in diesem Fall wird die Ausführung ohne
DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO
zwischen zwei Läufen der iterativen Methode auch nicht wirklich schneller
Zeit ------------------------------------------------------ 1900-01-01 00:00:01.403 (1 row(s) affected)
Dies ist natürlich kein repräsentativer Test unter sterilen Testbedingungen, aber verdeutlicht doch die Notwendigkeit effizienter Algorithmen, auch, und vielleicht gerade, in T-SQL.
SQL Server MVP Steve Kass hat dieses Beispiel in den englischen Newsgroups gepostet. Es zeigt, daß der Einsatz der Datentypen zur Speicherung monetärer Daten sorgfältig durchdacht sein sollte. Man sollte stets bedenken, welche Operationen mit diesen Daten durchgeführt werden.
Die Präsentation von Informationen aus der Datenbank ist imho eher Sache des Clients als die des Servers. Solche Aufgaben gehören zum Handswerkzeug jedes Front-End und sind dort schnell und einfach erledigt.
Falls jedoch, aus welchen Gründen auch immer, dies auf dem Server erledigt werden muss, kann vielleicht folgendes Skript gute Dienst leisten:
DECLARE @MeineZahl INT SET @MeineZahl = 99 SELECT RIGHT(REPLICATE('0',10) + CAST(@MeineZahl AS VARCHAR(10)),10) AS Rechtsbündig , LEFT(CAST(@MeineZahl AS VARCHAR(10)) + REPLICATE('0',10) ,10) AS Linksbündig Rechtsbündig Linksbündig ------------ ----------- 0000000099 9900000000 (1 row(s) affected)
Für den Lazycoder könnte das rechtbündige Auffüllen auch noch folgendermaßen aussehen:
DECLARE @MeineZahl INT SET @MeineZahl = 99 SELECT REPLACE(STR(@MeineZahl,10), ' ', '0') Rechtsbündig ---------------------- 0000000099 (1 row(s) affected)