Creating audit trails ( database activity logs of users)

[kosta]

Hi All experts,
i am VFP developer and newly SQL server . i want to create stored procedure or triggers ( which is best and practice,easy) for tracking user activities on tables ( add delete update ) . i am doing it in VFP just a funciton in storec procedure ( named myaudit ) and i put myaudit("I") myaudit("U") myaudit("D") to the dbf tables table-triggers section at design time. that's enough my needs.

So i want to do the same thing on sql server . i dont need professional solution just track these activities and keep its on the table such audist.

How can i do this ?
any help will be very appreciated

TIA

Soykan OEZCELIK

 

[nigelrivett]

see

http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html

http://www.nigelrivett.net/GenerateTriggerForAudit.html

http://www.nigelrivett.net/AuditTrailTrigger.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Glowworm27]

I used the audit triggers in the links from nigelrivett, and it worked fine,

Here is my trigger

CREATE TRIGGER tr_AuditTrigger on drug FOR insert, update, DELETE
as
DECLARE @bit int ,
	@field int ,
	@maxfield int ,
	@char int ,
	@fieldname varchar(128) ,
	@TableName varchar(128) ,
	@PKCols varchar(1000) ,
	@sql varchar(2000), 
	@UpdateDate varchar(21) ,
	@UserName varchar(128)
	
	SELECT @TableName = 'drug'
	-- date AND USER
	SELECT 	@UserName = SYSTEM_USER ,
		@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
	-- get list OF columns
	SELECT * INTO #ins FROM inserted
	SELECT * INTO #del FROM deleted
	
	-- Get PRIMARY KEY columns FOR FULL OUTER JOIN
	SELECT	@PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
	FROM	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	WHERE 	pk.TABLE_NAME = @TableName
	AND	CONSTRAINT_TYPE = 'PRIMARY KEY'
	AND	c.TABLE_NAME = pk.TABLE_NAME
	AND	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	
	IF @PKCols IS NULL
	BEGIN
		raiserror('no PK ON TABLE %s', 16, -1, @TableName)
		RETURN
	END
	
	SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
	WHILE @field < @maxfield
	BEGIN
		SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
		SELECT @bit = (@field - 1 )% 8 + 1
		SELECT @bit = power(2,@bit - 1)
		SELECT @char = ((@field - 1) / 8) + 1
		IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
		BEGIN
			SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
			SELECT @sql = 		'insert Audit (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName)'
			SELECT @sql = @sql + 	' select ''' + @TableName + ''''
			SELECT @sql = @sql + 	',''' + @fieldname + ''''
			SELECT @sql = @sql + 	',convert(varchar(1000),d.' + @fieldname + ')'
			SELECT @sql = @sql + 	',convert(varchar(1000),i.' + @fieldname + ')'
			SELECT @sql = @sql + 	',''' + @UpdateDate + ''''
			SELECT @sql = @sql + 	',''' + @UserName + ''''
			SELECT @sql = @sql + 	' FROM #ins i FULL OUTER JOIN #del d'
			SELECT @sql = @sql + 	@PKCols
			SELECT @sql = @sql + 	' WHERE i.' + @fieldname + ' <> d.' + @fieldname 
			SELECT @sql = @sql + 	' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)' 
			SELECT @sql = @sql + 	' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)' 
			
			EXEC (@sql)
		END
	END

and here is my audit table

CREATE TABLE [dbo].[Audit] (
	[TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UpdateDate] [datetime] NULL ,
	[UserName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

hope this helps

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[ESquared]

J O I N ! ! !

Should I use JOIN or WHERE to join my tables?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[kosta]

Thanks Glowworm27 ,
should i create above trigger for each table or is it enough in stored procedure for all tables ? i think this trigger code for 'drug' table

TIA

Soykan OEZCELIK

 

[kosta]

Hi Again Glowworm27,
also i wantto know , some tables ( eg. northwind employees table ) gives below error ...

Server: Msg 311, Level 16, State 1, Procedure tr_AuditTrigger_employees, Line 20
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

how would be this code when some tables has text,ntext,or image columns ?

Soykan OEZCELIK

 

[NoCoolHandle]

I think it is just telling you that you can't reference the column that contains text/image data in it, not that the insetted table can't contain any image or text data.

If you are interested in what column changed you could do that by checking the updated "property?".

I.E.

create database crap
go
Use crap
go
create table a
(c1 int identity primary key,
 c2 image)
go
create table b
(c1 int identity primary key,
ColChanged varchar(100),
ModTIme smalldatetime default getdate())
go
create trigger uTa
on a for update , insert
as
if update(c2) 
	begin
		insert into b (ColChanged) values ('c2')
	end
go
insert into a (c2) select photo from northwind.dbo.employees where employeeid = 1
go
update a set c2 = (select photo from northwind.dbo.employees where employeeid = 2)
where c1 = 1
go
select * from b
go
drop database crap

Here you will see that it was quite easy to see that the col changed was the image col..

 

[kosta]

Thanks for the reply NoCoolHandle,
how would be possible just one trigger code for insert,update,delete actions can track these actions for all field types on tables .

just i need this. would you like write here sample trigger code for this and code for creating audit table ?

Thanks from now...

Soykan OEZCELIK

 

[NoCoolHandle]

I don't have too much time, but a quickie (would need to be tested as I don't have QA right now)

I am sure you have read up on and understnd the suser_sname() , host_name(),user_name() functions..

Something like

create table Audit(AuditID int identity,EventType char(6) not null,SQLLogin varhcar(300) default suser_sname() not null ,NetWorkstation varchar(300) default host_Name() not null ,EventTime datetime default getdate() not null)

create trigger tr_uid_SomeTable_ForAudit
on SomeTable for UPdate, Insert, Delete
as
If exists (select * from inserted) and exists (select * from deleted)
   Begin
    -- Test to see what cols have been updated and insert
    -- that info as well (if necessary)
    insert into Audit (EventType) values ('Update')
   End
else if exists(select * from inserted) -- Must be an insert
   begin
    insert into audit (EventType) values ('Insert')
   end
else
   begin
    insert into audit (EventType) values ('Delete')
   end

Of course you might wish to capture before and after states, that should be possible for most events and columns..

I would suggest you look at Lumigent's Log Explorer (free demo download) very good. You will also find that alot of good audit function is available via profiler without having to do a whole lot more than set up filters..

HTH


Rob

 

[kosta]

Hi Rob,
i've tested your code it works fine.
sory i dont wantto take your worty time but one more question .
i can add tablename and fieldname fields to the audit table.
how can we track also tablename and fieldname via trigger code and insert its also to the audit table ?

TIA


Soykan OEZCELIK

 

[AshleyRickards]

I used GlowWorm27 code to create the trigger and the table but I get this error. Any idea.

'Another user has mofified the contents of this table or view; the database row you are modifying no longer exists in the database. Database error 'microsoft odbc sql server driver sql server Incorrect syntax near the keyword 'and'

Ashley L Rickards
SQL DBA