July 9, 2015 at 5:42 am
Hi,
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
at present I have
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
AND EXISTS (SELECT * FROM dbo.t_account WHERE document_status = 0)
begin
INSERT dbo.t_account_TriggerLog
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
cast(context_info() as NVARCHAR(128)),
COALESCE(SUSER_SNAME(),USER_NAME()),
GETDATE()
end;
GO
however this doesn't appear to bring back the procedure that triggered the update...
the value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDA\administrator2015-06-25 07:42:01.677
NULLNULLLOMBDA\tim64602015-06-25 07:51:34.503
NULLNULLLOMBDA\administrator2015-06-25 07:52:01.610
NULLNULLLOMBDA\administrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account] AFTER UPDATE, INSERT AS IF UPDATE (document_status) BEGIN IF EXISTS (SELECT * FROM DBO.T_ACCOUNT WHERE document_status IN ('0',NULL)) INSERT doesmore.dbo.t_account_TriggerLog SELECT convert(varchar(3trTableupdateaccountLOMBDA\administrator2015-06-25 09:12:01.157
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account] AFTER UPDATE, INSERT AS IF UPDATE (document_status) BEGIN IF EXISTS (SELECT * FROM DBO.T_ACCOUNT WHERE document_status IN ('0',NULL)) INSERT doesmore.dbo.t_account_TriggerLog SELECT convert(varchar(3trTableupdateaccountLOMBDA\administrator2015-06-25 09:32:03.233
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account] AFTER UPDATE, INSERT AS IF UPDATE (document_status) BEGIN IF EXISTS (SELECT * FROM DBO.T_ACCOUNT WHERE document_status IN ('0',NULL)) INSERT doesmore.dbo.t_account_TriggerLog SELECT convert(varchar(3trTableupdateaccountLOMBDA\administrator2015-06-25 09:42:01.040
NULLNULLsql_doesmore_prod2015-06-25 16:58:41.690
NULLNULLsql_doesmore_prod2015-06-26 13:39:08.260
NULLNULLsql_doesmore_prod2015-06-26 13:39:43.177
July 9, 2015 at 7:19 am
Hi and welcome to the forums! As posted your question is extremely unclear. I am unable to determine what you are trying to do and what the question is. Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
July 9, 2015 at 7:49 am
hi, thanks for getting back to me.
I am trying to create a trigger that runs whenever a value in a specific column is updated to 0
how do i go about this?
July 9, 2015 at 7:51 am
Create an update trigger, it'll fire on every update. In the SQL that you have inside the trigger, doing whatever you want the trigger to do, you would filter WHERE inserted.ASpecificColumn = 0
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2015 at 7:56 am
many thanks for you reply,
how do i get the trigger to show me what code what run to update it (i.e. stored proce)?
I was using
INSERT dbo.t_account_TriggerLog
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
but that didnn;t seem to work.
July 9, 2015 at 7:58 am
No, it won't. EventData is only for DDL triggers.
I don't think there's an easy way to do what you're asking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2015 at 9:08 am
You could use INPUTBUFFER to grab the outermost level of code executing for that SPID.
Something like this (you'd obviously have to change the specific table information, and you might want to pull other information, or check that a row was actually updated, etc.):
CREATE TABLE SomeTableUpdateLog (OccurredAt datetime, ModifyingSQL nvarchar(4000))
CREATE TRIGGER SomeTableUpdateTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #InputBuffer (EventType nvarchar(30), Parameters smallint, EventInfo nvarchar(4000))
DECLARE @sql varchar(100)
SET @sql='dbcc inputbuffer ('+CAST(@@spid as varchar)+')'
INSERT INTO #InputBuffer
EXEC (@sql)
INSERT INTO SomeTableUpdateLog
SELECT
OccurredAT=GETDATE(),
ModifyingSQL=EventInfo
FROM #InputBuffer
END
As always, be careful of the overhead and potential issues (if you forget the trigger's there, troubleshooting errors in an UPDATE a couple years from now could be infuriating) with using triggers. My brain knows triggers have their uses, but I still tend to cringe when I see them 🙂
Cheers!
July 9, 2015 at 9:25 am
The trigger route seems like it can be pretty painful.
How many procs can update this table?
If there are only a few, then would creating a separate table that contains the primary key of the table you want to audit, the name of the proc, and the time it was updated?
Then, add a few lines of code to each proc that does an update or insert to fill this table.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 9, 2015 at 9:26 am
many thanks!!!
is there any way you can narrow the update to being a single column to a single value. I only want the trigger to run when column A is updated to value B..
July 9, 2015 at 9:42 am
No. An update trigger fires for any update. You can do checks in the trigger using either the UPDATE() function or checking the inserted and deleted tables and then decide what to do.
And bear in mind that a trigger fires once for an update, not once per row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2015 at 9:50 am
As Gail pointed out, the trigger will fire for every update.
You can control when it logs to your audit table, though, using the inserted and deleted tables like she said.
You're just wanting to check for a row where the value for document_status was non-zero, and after the update was zero? In that case, you'd just join the INSERTED and DELETED tables on your table's primary key, and check for the existence of a row where DELETED.document_status (the old value) is !=0 and INSERTED.document_status (the new value) is =0.
So, you'd just wrap the code from the body of the trigger in something like this:
IF EXISTS (SELECT 1 FROM deleted
inner join inserted
ON inserted.id=deleted.id
WHERE inserted.document_status=0 and deleted.document_status!=0)
BEGIN
--*************************
--Rest of trigger code goes here
--*************************
END
As Michael pointed out, though, if you have relatively few procedures that would make such an update, it might be worth just having those procedures log such updates directly, instead of using a trigger.
Triggers have a bad habit of becoming unruly creatures 🙂
Cheers!
July 9, 2015 at 10:13 am
thank-you kindly. this worked perfectly.
July 9, 2015 at 10:39 am
thank-you, this has helped a lot.
Lastly, is there any way of seeing which row (primary key) is being updated and also the user_id updating?
July 9, 2015 at 10:52 am
harnett.tim (7/9/2015)
thank-you, this has helped a lot.Lastly, is there any way of seeing which row (primary key) is being updated and also the user_id updating?
All the columns in the table are in the inserted and deleted virtual tables so the primary key is already there.
For the user_id that depends. Is this the user_id that made the connection to the database or the user_id from the application? If it is the database user you can use ORIGINAL_LOGIN(). If it is from the application you would need to put that value in your table to make it visible in a trigger.
_______________________________________________________________
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/
July 10, 2015 at 11:01 am
You can use CONTEXT_INFO to pass the name of the proc to the trigger. The calling proc sets specific bytes in CONTEXT_INFO, and the trigger substrings out those bytes to get the name. Btw, the calling proc can use @@PROCID to get its own object_id, and thus its own name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply