October 4, 2012 at 12:41 pm
I have few tables that I want to capture who did the last update on them. For that I created a couple of columns createuser and createdate with default values suser_name() and getdate(). Now I want to add an update trigger to those tables. This trigger should capture the username and time when an update happens on that table. Is this possible?
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 4, 2012 at 12:47 pm
I have this saved as a handy auditing snippet;
all these variables are available in 2008 and above, so if you have an audit table, just include some columns for the items here you might want to capture;
run this in SSMS and familiarize yourself with the expected values, and just add it to your trigger or procedures.
for 2008 and above:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
October 4, 2012 at 12:50 pm
and a basic example of the trigger:
CREATE TRIGGER TR_UPDATE_EXAMPLE ON SOMETABLE
FOR UPDATE
AS
BEGIN
UPDATE SOMETABLE
SET LastModifedUserName = SUSER_NAME(),
LastModifedDate = GETDATE()
FROM INSERTED
WHERE SOMETABLE.PKID = INSERTED.PKID
END
Lowell
October 4, 2012 at 1:57 pm
Thanks for your help Lowell. I have too many tables and I want to put it in a cursor. But I am stuck at the inner join condition. Here is my code:
--DECLARE @TriggerName VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @ConstraintName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
select name from sys.tables where name <>'sysdiagrams'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql ='create trigger Tr_'+@TableName+' ON dbo.['+@Tablename+'] AFTER UPDATE
AS
UPDATE L SET createUser = suser_name(), CreateDate = CURRENT_TIMESTAMP from ['+@TableName+'] L
INNER JOIN Inserted I on L.'+@tablename.primarykey' = I.'+@tablename.primarykey'
PRINT(@sql)
--EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
L.'+@tablename.primarykey' = I.'+@tablename.primarykey' is where I am stuck.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 4, 2012 at 2:18 pm
ok i'm HOPING this lazy fix is correct;
instead of going to find the columns that consist of the primary keys of a table constraint,
is it true that your tables are using identity columns for the PK's?
if not, i'll dig up something that actually gets the columns from the indexes and do it the right way.
syntactically, this is all correct:
this script assumes the first column in a table is an idnetity column...if it is, it will generate the script to add the columns if they don't exist yet, as well as a trigger to add your two columns as well.
--DECLARE @TriggerName VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @ColumnName VARCHAR(255)
DECLARE @ConstraintName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
--SELECT * FROM sys.columns
--assuming the first column in the table is the identity and PK of the table...otherwise we need to query the indexes
select object_name(object_id) As TbLName,name As ColName from sys.columns WHERE is_identity = 1 and column_id = 1
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName,@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql ='
--add our column if not exists
IF NOT EXISTS (SELECT name from sys.columns where name=''createUser'' and object_name(object_id) = ''+@TableName+')'
ALTER TABLE '+@TableName+' ADD createUser datetime DEFAULT suser_name();
IF NOT EXISTS (SELECT name from sys.columns where name=''CreateDate'' and object_name(object_id) = ''+@TableName+')'
ALTER TABLE '+@TableName+' ADD CreateDate datetime DEFAULT getdate();
GO
--create our trigger
CREATE TRIGGER Tr_'+@TableName+'
ON dbo.['+@Tablename+']
AFTER UPDATE
AS
UPDATE L
SET createUser = suser_name(),
CreateDate = CURRENT_TIMESTAMP
FROM ['+@TableName+'] L
INNER JOIN Inserted I on L.'+@ColumnName+' = I.'+@ColumnName+'
GO'
PRINT(@sql)
--EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName,@ColumnName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Lowell
October 4, 2012 at 3:17 pm
Cool...Thanks so much lowell...I was working on it parallely and came up with almost a same script..please find mine below:..Thanks a bunch again
DECLARE @TableName VARCHAR(255)
DECLARE @PKColumnName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT
KCU.TABLE_NAME,
KCU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
WHERE
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName,@PkCOlumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql ='create trigger Tr_'+@TableName+' ON dbo.['+@Tablename+'] AFTER UPDATE
AS
UPDATE L SET createUser = suser_name(), CreateDate = CURRENT_TIMESTAMP from ['+@TableName+'] L
INNER JOIN Inserted I on L.'+@PKColumnName+' = I.'+@PKColumnName+'
GO
'
PRINT(@sql)
--EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName,@PKColumnName
END
CLOSE TableCursor
DEALLOCATE TableCursor
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 4, 2012 at 10:49 pm
Lowell (10/4/2012)
I have this saved as a handy auditing snippet;all these variables are available in 2008 and above, so if you have an audit table, just include some columns for the items here you might want to capture;
run this in SSMS and familiarize yourself with the expected values, and just add it to your trigger or procedures.
for 2008 and above:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell,
This is way t0o cool to not save in my auditing script library!
Now I just need to research what each of those properties means. :w00t:
Thanks mate!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply