August 13, 2012 at 2:53 pm
Lowell,
Alright...I tinkered with the code you sent us & came up with this "alpha" code I will pick at tomorrow. Do you see anything initially wrong with going this route:
ALTER TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @INSERTUPDATE NVARCHAR(30),
@LASTCOMMAND NVARCHAR(max)
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer -- @buffer is a table with 2 fields: EventType and EventInfo
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo -- This line is assigning EVENTINFO to the variable @LastCommand
FROM @buffer
IF LEFT(@lastcommand, 3) = UPPER('UPD')
BEGIN
SET @INSERTUPDATE='UPDATE'
END
ELSE IF LEFT(@LASTCOMMAND, 3) = UPPER('DEL')
BEGIN
SET @INSERTUPDATE='DELETE'
END
ELSE
BEGIN
SET @INSERTUPDATE = 'INSERT'
END
INSERT INTO [PL].[dbo].[WHATEVER_AUDIT]
([INSERTUPDATE]
,[LASTCOMMAND]
,[USER_NAME]
,[SUSER_NAME]
,[CURRENT_USER]
,[SYSTEM_USER]
,[SESSION_USER]
,
,[APPLICATION_NAME]
,[HOST_NAME]
,[OCCURANCE_DATE])
VALUES
(@INSERTUPDATE
,@LASTCOMMAND
,USER_NAME()
,SUSER_NAME()
,CURRENT_USER
,SYSTEM_USER
,SESSION_USER
,USER
,APP_NAME()
,HOST_NAME()
,GETDATE())
END --TRIGGER
GO
All in all the table is populating with the exact data I'm looking for and then some. I will tweak it more tomorrow to get rid of all the extra fields that aren't needed but as a start I think this is spot on.
Thoughts? Or do you think this "monster" will turn on me? 😀
August 13, 2012 at 3:02 pm
you shouldnt' assume the commands start with your expected keyword..comments, whitespace, lots of other things can prevent you from determining it was insert/update.delete.
i would do it like this, using the INSERTED/DELETED tables.
IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
IF EXISTS(SELECT 1 FROM DELETED)
SET @INSERTUPDATE='UPDATE'
ELSE
SET @INSERTUPDATE='INSERT'
END
ELSE
BEGIN
SET @INSERTUPDATE='DELETE'
END --Else no data in INSERTED
and the full example trigger you posted, slightly edited:
ALTER TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @INSERTUPDATE NVARCHAR(30),
@LASTCOMMAND NVARCHAR(max)
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer -- @buffer is a table with 2 fields: EventType and EventInfo
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo -- This line is assigning EVENTINFO to the variable @LastCommand
FROM @buffer
IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
IF EXISTS(SELECT 1 FROM DELETED)
SET @INSERTUPDATE='UPDATE'
ELSE
SET @INSERTUPDATE='INSERT'
END
ELSE
SET @INSERTUPDATE='DELETE'
INSERT INTO [PL].[dbo].[WHATEVER_AUDIT]
([INSERTUPDATE]
,[LASTCOMMAND]
,[USER_NAME]
,[SUSER_NAME]
,[CURRENT_USER]
,[SYSTEM_USER]
,[SESSION_USER]
,
,[APPLICATION_NAME]
,[HOST_NAME]
,[OCCURANCE_DATE])
VALUES
(@INSERTUPDATE
,@LASTCOMMAND
,USER_NAME()
,SUSER_NAME()
,CURRENT_USER
,SYSTEM_USER
,SESSION_USER
,USER
,APP_NAME()
,HOST_NAME()
,GETDATE())
END --TRIGGER
GO
Lowell
August 13, 2012 at 3:18 pm
Good grief. Yeah, I didn't even think about the capability of something coming before the INSERT/UPDATE/DELETE in the string. <facepalm>
And your modification works great! Many, many thanks!
My only question is more of a syntax question. Please forgive me if it's a very rookie question but my t-sql up till now was very, very limited.
IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
IF EXISTS(SELECT 1 FROM DELETED)
I don't understand where the "(SELECT 1 FROM whatever)" is coming from. The rest of the code made perfect sense once I broke it down. Could you please explain how those 2 lines of code work?
Thank you again!! This is great, great stuff! I hope others gain from it as well!!
August 13, 2012 at 3:26 pm
ok, inside a DML trigger, here's what is happening in SQL server:
the values someone changed, are already in the table WHATEVER. if you query the table inside the trigger, the values have already been changed, including identity() columns and calculated columns.
there are two special tables, that exist inside the trigger, named INSERTED and DELETED, only for the duration of the trigger event.
they contain:
1. both the old and new values (if there was an UPDATE)
2. only new values if it was an INSERT,
3. only the deleted values if the event was a DELETE.
those tables are an exact mirror of the table the trigger is on: same column names, everything; they are a virtual extention for the trigger.(so teh columns in INSERTED/DELETED change for each table)
you can check if any data exists at all in a table by SELECT * FROM or select a constant, like 1 from (IF EXISTS(SELECT * FROM blah blah)
you can use those tables for logging (if you neede dto know "Bob changed the table PAYROLL", and log INSERTED.PayRate and DELETED.PayRate to some table for auditing if you wanted.
all i'm doing, is a fast way to assign a constant, by using the EXISTS...it doesn't matter if one row or a million rows were changed, the tables will simply have rows or not, based on the event.
Lowell
August 21, 2012 at 2:40 pm
John did you ever get all this workign the way you expected? between the DDL and the DML, you were testing for a lot of stuff, i was hoping to hear about any followup.(foul up?)
Lowell
August 21, 2012 at 2:54 pm
I'm sorry, Lowell. Yes. This has been a outstanding success!!!
I got a little trigger happy on several issues I'd like to *cough* "monitor" and my manager had to reign me in. All thanks to the code you got me started with.
Right now we use it to babysit a certain table that gets updated by developers on random occasions throughout the day. We had a number of hands in on this one development environment and there were so many changes going on with it no one would owe up to who was doing what. So we implemented this trigger code to keep tabs on them.
I'm still tweaking it a little. On a number of UPDATE queries, where we will have an OLD value & a NEW value....both are showing as NULL. It's no big deal at the time because we are able to capture the t-sql code that is run, who's running it, from where & when. So that is 95% of the battle right there. The UPDATE statement will sometimes have multiple updates within it. So that is understandable. The OLD values & NEW values are easy to look at but in the case of multiple updates, if we can get the t-sql code running the update...we are VERY, VERY HAPPY!! We can get what we need from that.
Thanks again, Lowell. As I said before this t-sql code is gold & I see this being used by us a lot!!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply