February 18, 2011 at 1:14 pm
so if i have a table, 'MyTable', with an AFTER INSERT trigger set against it, 'MyTrigger':
I was wondering if there is a way to obtain the entire SQL statement that causes the trigger to fire in the first place.
eg, if i execute the following:
INSERT INTO MyTable (MyColumn) VALUES ('MyValue')
I'd like to, in my trigger, see the statement
"INSERT INTO MyTable (MyColumn) VALUES ('MyValue')"
so i can se it agains a variable and manipuilate it. Possible?
Thanks!
February 18, 2011 at 1:45 pm
There is no way that I know of to capture the triggering SQL Statement within a trigger.
I'm pretty sure you'd need to have a server-side trace running or perhaps use extended events.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2011 at 2:41 pm
It sounds like you are wanting to change some of the values under certain circumstances on insert? If so, you could accomplish this with an insert sproc instead of a trigger for this. That way you can control what is happening and don't get stuck in an infinite loop of inserts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 18, 2011 at 2:49 pm
just to confirm, from inside a trigger, you cannot always get the whole SQL statement; you can use DBCC inputbuffer, but that returns only the first 255 chars; a lot of statements are longer than that, so you'd end up with truncation, so you cannot use it for what you wanted 100%;
here is a complete 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)
--################################################################################################
--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 256 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]
Lowell
February 18, 2011 at 3:45 pm
Sean Lange (2/18/2011)
It sounds like you are wanting to change some of the values under certain circumstances on insert? If so, you could accomplish this with an insert sproc instead of a trigger for this. That way you can control what is happening and don't get stuck in an infinite loop of inserts.
Yes I want to run the inputted values (from the insert statement) against a function that will replace certain special characters. I'd like to make the trigger dynamic so one source can easily be set against many tables without the need to write triggers for each table (with table names, column names, etc.)
Did't think about the infinite loop, which would definitely happen with the approach i taking... In the past I've written triggers that do inserts/updates to different tables, not against the same one like I'm trying now...
What do you mean by 'insert stored proCedur'?
February 21, 2011 at 7:55 am
What I meant was that you could create a stored proc that would do you insert for you. Instead of inserting into the table you would use the stored proc which can do whatever data manipulation you want before it actually inserts your record.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply