February 2, 2010 at 2:15 pm
How to pull UserID column from the table that you have created Trigger?
Below is my generic script and I have put comment as "--would like to acoomplish"
what I have is I have one table that contain User login info so, when ever Test_Field change I am inserting the change to my [UserAudit] table. Currently, I can not pull UserID column from [TestTriggerSMS] table.
How do I accomplish where when ever user make changes to [test_field] my [UserAudit] table get update with [UserID] value?
CREATE TABLE [dbo].[TestTriggerSMS]
([Userid][int],
[test_field] [varchar] (50)
) ON [PRIMARY]
create trigger tr_Update_TestTriggerSMS on [dbo].[TestTriggerSMS]
for update
as
set nocount on
declare @rows int
select @rows = count(*) from inserted
-- send notification if TestTriggerSMS table is being updated
if @rows=1-- notification only if update 1 row
begin
if update(test_field)-- notification only if update [test_field] field
begin
print ' [test_field] field updated'
declare @old_value varchar(50)
, @new_value varchar(50)
--, @userid int -- Would like to accomplish
declare @tab char(1)
set @tab = char(9)
declare @message varchar(555)
set @old_value = (select test_field from deleted)
set @new_value = (select test_field from inserted)
set @message = 'TestTriggerSMS updated:' + char(13) +
--'User ID:' + @tab + @tab + '[' + @userid + ']'+ char(13) + -- Would like to accomplish
'Old Value ' + @tab + @tab + '[' + @old_value + ']'+ char(13) +
'New Value ' + @tab + @tab + '[' + @new_value + ']'
insert into dbo.UserAudit (UserID, Message_TXT, Insert_dt)
-- Would like to accomplish to have USERID from [TestTriggerSMS] table
SELECT@userid,@message, GETDATE()
end
end
set nocount off
February 2, 2010 at 2:53 pm
DECLARE @User_ID int
SELECT @User_ID = UserId
FROM inserted
It should be that simple. Translating the integer user id into a name is another story that depends on where you keep that information.
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply