March 30, 2010 at 7:20 pm
Hello, first time poster, long time lurker.
We have a very large, distributed application which occasionally sets a certain status to null. After hours of searching, we can't replicate the behavior. I was thinking that I could write a trigger to look for the status changing to null and will be able to discern who (which will most likely just say ".Net SqlClient Data Provider") and when caused the change. What I'd like to log is what the SQL statement was that caused the change.
I doubt this is possible; does anyone know?
March 31, 2010 at 6:37 am
it is certainly possible; all i can do is give you a generic working example for you to use as a model.
in this case, the important piece is the inserting the audit information into an audit table, from the virtual table INSERTED, only when the NULL test condition is true;
==edit== having a little trouble getting the last command; i'm still testing that...i get the last CREATE command, instead of the DML command.
example results:
WHATEVERID INSERTUPDATE USER_NAME SUSER_NAME CURRENT_USER SYSTEM_USER SESSION_USER USER APPLICATION_NAME HOST_NAME OCCURANCE_DATE
----------- ------------- ---------- ------------------ ------------- ------------------ ------------- ----- ----------------------------------------------- ---------- -----------------------
12 INSERT dbo DISNEY\lizaguirre dbo DISNEY\lizaguirre dbo dbo Microsoft SQL Server Management Studio - Query D223 2010-03-31 08:31:49.260
5 UPDATE dbo DISNEY\lizaguirre dbo DISNEY\lizaguirre dbo dbo Microsoft SQL Server Management Studio - Query D223 2010-03-31 08:31:52.933
4 UPDATE dbo DISNEY\lizaguirre dbo DISNEY\lizaguirre dbo dbo Microsoft SQL Server Management Studio - Query D223 2010-03-31 08:31:52.933
the code example:
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
--used to capture the row id plus a bunch of audit information
CREATE TABLE [dbo].[WHATEVER_AUDIT] (
[WHATEVERID] INT NOT NULL,
[INSERTUPDATE] NVARCHAR(30) NULL,
[LASTCOMMAND] NVARCHAR(max) NULL,
[USER_NAME] NVARCHAR(256) NULL,
[SUSER_NAME] NVARCHAR(256) NULL,
[CURRENT_USER] NVARCHAR(256) NULL,
[SYSTEM_USER] NVARCHAR(256) NULL,
[SESSION_USER] NVARCHAR(256) NULL,
NVARCHAR(256) NULL,
[APPLICATION_NAME] NVARCHAR(256) NULL,
[HOST_NAME] NVARCHAR(256) NULL,
[OCCURANCE_DATE] DATETIME DEFAULT GETDATE() NOT NULL)
GO
CREATE TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @INSERTUPDATE NVARCHAR(30),
@LASTCOMMAND NVARCHAR(max)
--get the last command by the current spid:
SELECT @LASTCOMMAND = DEST.TEXT
FROM sys.[dm_exec_connections] SDEC
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
WHERE SDEC.[most_recent_session_id] = @@SPID
--assume it is an insert
SET @INSERTUPDATE='INSERT'
--if there's data ind eleted, it's an update
IF EXISTS(SELECT * FROM DELETED)
SET @INSERTUPDATE='UPDATE'
--insert data that meets the criteria: the column 'description' is null
INSERT INTO [WHATEVER_AUDIT]
SELECT
INSERTED.WHATEVERID,
@INSERTUPDATE,
@LASTCOMMAND,
user_name() AS [user_name],
suser_name() AS [suser_name],
current_user AS [current_user],
system_user AS [system_user],
session_user AS [session_user],
user AS ,
APP_NAME() AS [application_name],
HOST_NAME() AS [host_name],
getdate() AS [occurance_date]
FROM INSERTED
WHERE DESCRIP IS NULL
END --TRIGGER
GO
--does not trigger audit:
INSERT INTO WHATEVER(DESCRIP)
SELECT 'CANTALOUPE' UNION
SELECT 'TANGARINES' UNION
SELECT 'PLUMS' UNION
SELECT 'PEACHES' UNION
SELECT 'BLUEBERRIES'
--triggers one row out of multi row insert
INSERT INTO WHATEVER(DESCRIP)
SELECT NULL UNION
SELECT 'TANGARINES'
--triggers one row out of multi row insert
UPDATE WHATEVER SET DESCRIP = NULL WHERE WHATEVERID IN (4,5)
SELECT * FROM WHATEVER
SELECT * FROM [WHATEVER_AUDIT]
Lowell
March 31, 2010 at 7:07 am
i've tried two methoids so far to get the current/last issued? command from inside the trigger: in the above example. both get the same info, just from two different ways....but it's not the current command.
--get the last command by the current spid:
DECLARE @handle varbinary(64)
SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID
SELECT [Text] FROM ::fn_get_sql(@Handle)
--get the last command by the current spid:
SELECT DEST.TEXT
FROM sys.[dm_exec_connections] SDEC
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
WHERE SDEC.[most_recent_session_id] = @@SPID
it doesn't seem to be returning what i want within the trigger; anyone have any other suggestions?
Lowell
March 31, 2010 at 8:19 am
DBCC INPUTBUFFER(@@SPID) is the only way I have found so far to do this.
All queries involving DMVs did return the trigger code or the calling stored procedure code. I don't know the reason behind and I did not have the time to investigate it further, but this is a valid workaround, as far as I know.
-- Gianluca Sartori
April 1, 2010 at 3:38 am
This is the piece of code I use in my triggers:
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @SQLBuffer = EventInfo
FROM @buffer
-- Gianluca Sartori
April 5, 2010 at 2:16 pm
Sorry, I was away for most of last week and just tried this. I've never noticed the DBCC InputBuffer statement before. Wonderful.
Thanks to all who responded.
April 7, 2010 at 2:49 am
You're welcome.
Glad I could help.
-- Gianluca Sartori
April 7, 2010 at 4:28 am
There are rumours of fuller support for this sort of requirement in the next major release of SQL Server.
(R2 is not a major release).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 10:38 pm
Paul White NZ (4/7/2010)
There are rumours of fuller support for this sort of requirement in the next major release of SQL Server.(R2 is not a major release).
Paul: random tangent question, do you know what the codename for that next major release is? Or even better, a futures release roadmap for SQL Server?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2010 at 12:06 am
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
There are rumours of fuller support for this sort of requirement in the next major release of SQL Server.(R2 is not a major release).
Paul: random tangent question, do you know what the codename for that next major release is? Or even better, a futures release roadmap for SQL Server?
No idea.
http://news.softpedia.com/news/Introducing-Microsoft-Codename-Denali-the-Great-One-135006.shtml
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply