LOL John it's feedback like that, knowing I helped someone, that really makes my day!

inside a DML trigger, it's a differnet technique, but you bet I have examples of that too.

let me say first, you might run into permissions issues here...you can use sys.objects to get the name of the table the trigger is sitting on, as well as the schema name, but if the end user does not have permissions to the objects, they may return an error or reutrn nothing at all;

you might need to Grant View Definition ON SCHEMA::[dbo] To [YourRole]

if you have tight permissions.

for the command that is triggering the trigger, you need to use DBCC inputbuffer inside the trigger body;

EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

the buffer is limited to 4000 chars, so bigger code blocks/ commands might get cut off.

here's a full example:

tCREATE 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)

--################################################################################################

--note these two methods do not get the last command when inside a trigger;

--included for complete solution

--get the last command by the current spid:

--DECLARE @handle varbinary(64)

--SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID

--SELECT @LASTCOMMAND = [Text] FROM ::fn_get_sql(@Handle)

--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

--################################################################################################

--because dbcc inputbuffer is limited to 4000 chars, you may need to combine this witha DML trace

--################################################################################################

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 @LASTCOMMAND = EventInfo

FROM @buffer

--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]

here's the code to get the schema/table name inside the trigger:

SELECT

schema_name(schema_id),

name As TableName

from sys.objects

where object_id in(select

parent_object_id

from sys.objects

where object_id = @@PROCID)