User Log in MS SQL Server     1.Пуска се целя скрипт в коя да е база 2.Създав се таблица, каква да е само трябва ключ да има (може и съставен) 3. exec dbo.spRegistertableForLoging име_на_таблицата,ключ (ако е съставен, примерно: cast(k1 as nvarchar) + ‘|’ + cast(k2 as nvarchar)) Параметрите са заградени в апострофи. Всички промени ще започнат да се логват в tbSecLogMain и tbSecLogData 4.  exec dbo.spUnregistertableForLoging име_на_таблицата Спира логването   Лога се чете със:   select       * from       dbo.tbSecLogMain as m join dbo.tbSecLogDatas as d on(m.SecLogID=d.SecLogID)   Има малки бугове, но общо взето работи. ;) Не трябва в таблиците да има полета с име „k” или „[k]” И др. такива ;)     Ето скрипта:

/****** Object: Table [dbo].[tbSecLogMain]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[tbSecLogMain]") AND OBJECTPROPERTY(id, N"IsUserTable") = 1) BEGIN CREATE TABLE [dbo].[tbSecLogMain](       [SecLogID] [int] IDENTITY(1,1) NOT NULL,       [ChangingTime] [datetime] NOT NULL,       [UserName] [nvarchar](50) NOT NULL,       [ChangedTableName] [nvarchar](255) NOT NULL,       [KeyOfChangedTable] [nvarchar](1000) NOT NULL,  CONSTRAINT [PK_tbSecLogMain] PRIMARY KEY CLUSTERED (       [SecLogID] ASC ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: StoredProcedure [dbo].[spGetChangedKeys]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[spGetChangedKeys]") AND OBJECTPROPERTY(id,N"IsProcedure") = 1) BEGIN EXEC dbo.sp_executesql @statement = N"   CREATE procedure [dbo].[spGetChangedKeys]         @key as nvarchar(1000)--,       --@keyValue nvarchar(128)   as BEGIN       -- --select ""start spGetChangedKeys""   declare       @sql nvarchar(2000),       @params nvarchar(500)   set @params=""@key nvarchar(1000)""   set @sql="" select       x.* into       ##tmp_k from ( select       "" + @key + "" as [k],       """"D"""" as [flag] ,       * from       #d union select       "" + @key + "" as [k],       """"I"""" as [flag] ,       * from       #i ) as x ""   -- --select @sql   exec sp_executesql @sql, @params, @key=@key   END " END GO /****** Object: StoredProcedure [dbo].[spGetValues]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[spGetValues]") AND OBJECTPROPERTY(id,N"IsProcedure") = 1) BEGIN EXEC dbo.sp_executesql @statement = N"   CREATE procedure [dbo].[spGetValues]       @FieldName nvarchar(255),       @KeyValue nvarchar(128) AS BEGIN   -- --select ""start spGetValues""   declare       @sql nvarchar(2000),       @params nvarchar(500)   set @params=""@FieldName nvarchar(255), @KeyValue nvarchar(128)"" set @sql="" select       x1.* into       ##v from ( select       "" + @FieldName + "" as [val],       """"O"""" as [f] from       ##tmp_k where       flag=""""D"""" and       k=@KeyValue union all select       "" + @FieldName + "" as [val],       """"N"""" as [f] from       ##tmp_k where       flag=""""I"""" and       k=@KeyValue ) as x1 ""   exec sp_executesql @sql, @params, @FieldName=@FieldName, @KeyValue=@KeyValue   END " END GO /****** Object: StoredProcedure [dbo].[spRegistertableForLoging]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[spRegistertableForLoging]") AND OBJECTPROPERTY(id,N"IsProcedure") = 1) BEGIN EXEC dbo.sp_executesql @statement = N" CREATE procedure [dbo].[spRegistertableForLoging]       @TableName nvarchar(255),       @KeyScript nvarchar(1000) as BEGIN   declare       @sql nvarchar(2000)   set @sql="" create trigger trSecLog_"" + @TableName + "" on "" + @TableName + "" for insert, update, delete as BEGIN select       * into       #d from       deleted   select       * into       #i from       inserted   exec spLoging """""" + @TableName + """""", """""" + @KeyScript + """"""   END ""   exec sp_executesql @sql   END " END GO /****** Object: StoredProcedure [dbo].[spUnregistertableForLoging]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[spUnregistertableForLoging]") AND OBJECTPROPERTY(id,N"IsProcedure") = 1) BEGIN EXEC dbo.sp_executesql @statement = N" CREATE procedure [dbo].[spUnregistertableForLoging]       @TableName nvarchar(255) AS BEGIN   declare       @sql nvarchar(2000)   set @sql="" drop trigger trSecLog_"" + @TableName   exec sp_executesql @sql   END " END GO /****** Object: Table [dbo].[tbSecLogDatas]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[tbSecLogDatas]") AND OBJECTPROPERTY(id, N"IsUserTable") = 1) BEGIN CREATE TABLE [dbo].[tbSecLogDatas](       [SecLogID] [int] NOT NULL,       [KeyValue] [nvarchar](128) NOT NULL,       [FieldName] [nvarchar](255) NOT NULL,       [OldValue] [nvarchar](2000) NULL,       [NewValue] [nvarchar](2000) NULL,  CONSTRAINT [PK_tbSecLogDatas] PRIMARY KEY CLUSTERED (       [SecLogID] ASC,       [KeyValue] ASC,       [FieldName] ASC ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: StoredProcedure [dbo].[spLoging]    Script Date: 12/14/2006 14:56:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[spLoging]") AND OBJECTPROPERTY(id,N"IsProcedure") = 1) BEGIN EXEC dbo.sp_executesql @statement = N"     ------------------------------------ CREATE procedure [dbo].[spLoging]       @TableName nvarchar(255),       @Key nvarchar(1000) as BEGIN   -- --select ""start spLoging""   declare       @UserName nvarchar(50),       @ChangingTime datetime,       @SecLogID int   declare       @KeyValue nvarchar(128),       @FieldName nvarchar(255),       @OldValue nvarchar(2000),       @NewValue nvarchar(2000)   set @UserName = suser_sname() set @ChangingTime = getdate()   insert into       dbo.tbSecLogMain (       ChangingTime,       UserName,       ChangedTableName,       KeyOfChangedTable ) values (       @ChangingTime,       @UserName,       @TableName,       @Key )   set @SecLogID=       (       select             MAX(SecLogID)       from             dbo.tbSecLogMain       where             ChangingTime=@ChangingTime and             UserName=@UserName and             ChangedTableName=@TableName       )   exec dbo.spGetChangedKeys @Key   drop table #d drop table #i   declare crKeys cursor for select distinct k from ##tmp_k   open crKeys   fetch next from crKeys into @keyValue       while @@fetch_status=0 begin         declare crFields cursor for       select             c.name       from             dbo.sysobjects as o join dbo.syscolumns as c on(o.[id]=c.[id])       where             o.name=@TableName         open crFields             fetch next from crFields       into @FieldName             while @@fetch_status=0       begin                         exec dbo.spGetValues @FieldName, @keyValue                               set @OldValue=(select top 1 cast(val as nvarchar(2000)) from ##v where f=""O"")             set @NewValue=(select top 1 cast(val as nvarchar(2000)) from ##v where f=""N"")                         drop table ##v                        if @OldValue@NewValue or @OldValue is NULL or @NewValue is NULL             begin                   insert into                         dbo.tbSecLogDatas                   (                         SecLogID,                         KeyValue,                         FieldName,                         OldValue,                         NewValue                   )                   values                   (                         @SecLogID,                         @keyValue,                         @FieldName,                         cast(@OldValue as nvarchar(2000)),                         cast(@NewValue as nvarchar(2000))                   )             end               fetch next from crFields             into @FieldName       end           close crFields             deallocate crFields               fetch next from crKeys       into @keyValue end   close crKeys   deallocate crKeys   drop table ##tmp_k   END " END GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N"[dbo].[FK_tbSecLogDatas_tbSecLogMain]") AND type = "F") ALTER TABLE [dbo].[tbSecLogDatas] WITH NOCHECK ADD CONSTRAINT [FK_tbSecLogDatas_tbSecLogMain] FOREIGN KEY([SecLogID]) REFERENCES [dbo].[tbSecLogMain] ([SecLogID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[tbSecLogDatas] CHECK CONSTRAINT [FK_tbSecLogDatas_tbSecLogMain]

 

/****** Object: Table [dbo].[tbDemoTable] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tbDemoTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tbDemoTable](
[KeyField] [int] IDENTITY(1,1) NOT NULL,
[TextField] [nvarchar](50) NULL,
[NumericField] [numeric](18, 2) NULL,
CONSTRAINT [PK_tbDemoTable] PRIMARY KEY CLUSTERED
(
[KeyField] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO

/****** Object: Trigger [trSecLog_tbDemoTable] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create trigger [trSecLog_tbDemoTable]
on [dbo].[tbDemoTable]
for insert, update, delete
as
BEGIN
select
*
into
#d_e38be7c27a86
from
deleted

select
*
into
#i_e38be7c27a86
from
inserted

exec spLoging 'tbDemoTable', 'KeyField'

END

GO
/****** Object: Table [dbo].[tbSecLogMain] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tbSecLogMain]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tbSecLogMain](
[SecLogID] [int] IDENTITY(1,1) NOT NULL,
[ChangingTime] [datetime] NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[ChangedTableName] [nvarchar](255) NOT NULL,
[KeyOfChangedTable] [nvarchar](1000) NOT NULL,
CONSTRAINT [PK_tbSecLogMain] PRIMARY KEY CLUSTERED
(
[SecLogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: StoredProcedure [dbo].[spGetChangedKeys] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetChangedKeys]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[spGetChangedKeys]

@key as nvarchar(1000)--,
--@keyValue nvarchar(128)

as
BEGIN

--<debug/>
--select ''start spGetChangedKeys''

declare
@sql nvarchar(2000),
@params nvarchar(500)

set @params=''@key nvarchar(1000)''

set @sql=''
select
x.*
into
##tmp_k_e38be7c27a86
from
(
select
'' + @key + '' as [k_e38be7c27a86],
''''D'''' as [flag_e38be7c27a86] ,
*
from
#d_e38be7c27a86
union
select
'' + @key + '' as [k_e38be7c27a86],
''''I'''' as [flag_e38be7c27a86] ,
*
from
#i_e38be7c27a86
) as x
''

--<debug/>
--select @sql

exec sp_executesql @sql, @params, @key=@key

END
'
END
GO
/****** Object: StoredProcedure [dbo].[spGetValues] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetValues]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[spGetValues]
@FieldName nvarchar(255),
@KeyValue nvarchar(128)
AS
BEGIN

--<debug/>
--select ''start spGetValues''

declare
@sql nvarchar(2000),
@params nvarchar(500)

set @params=''@FieldName nvarchar(255), @KeyValue nvarchar(128)''
set @sql=''
select
x1.*
into
##v_e38be7c27a86
from
(
select
'' + @FieldName + '' as [val_e38be7c27a86],
''''O'''' as [f_e38be7c27a86]
from
##tmp_k_e38be7c27a86
where
flag_e38be7c27a86=''''D'''' and
k_e38be7c27a86=@KeyValue
union all
select
'' + @FieldName + '' as [val_e38be7c27a86],
''''N'''' as [f_e38be7c27a86]
from
##tmp_k_e38be7c27a86
where
flag_e38be7c27a86=''''I'''' and
k_e38be7c27a86=@KeyValue
) as x1
''

exec sp_executesql @sql, @params, @FieldName=@FieldName, @KeyValue=@KeyValue

END
'
END
GO
/****** Object: StoredProcedure [dbo].[spRegistertableForLoging] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRegistertableForLoging]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[spRegistertableForLoging]
@TableName nvarchar(255),
@KeyScript nvarchar(1000)
as
BEGIN

declare
@sql nvarchar(2000)

set @sql=''
create trigger trSecLog_'' + @TableName + ''
on '' + @TableName + ''
for insert, update, delete
as
BEGIN
select
*
into
#d_e38be7c27a86
from
deleted

select
*
into
#i_e38be7c27a86
from
inserted

exec spLoging '''''' + @TableName + '''''', '''''' + @KeyScript + ''''''

END
''

exec sp_executesql @sql

END
'
END
GO
/****** Object: StoredProcedure [dbo].[spUnregistertableForLoging] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUnregistertableForLoging]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[spUnregistertableForLoging]
@TableName nvarchar(255)
AS
BEGIN

declare
@sql nvarchar(2000)

set @sql=''
drop trigger trSecLog_'' + @TableName

exec sp_executesql @sql

END
'
END
GO
/****** Object: Table [dbo].[tbSecLogDatas] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tbSecLogDatas]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tbSecLogDatas](
[SecLogID] [int] NOT NULL,
[KeyValue] [nvarchar](128) NOT NULL,
[FieldName] [nvarchar](255) NOT NULL,
[OldValue] [nvarchar](2000) NULL,
[NewValue] [nvarchar](2000) NULL,
CONSTRAINT [PK_tbSecLogDatas] PRIMARY KEY CLUSTERED
(
[SecLogID] ASC,
[KeyValue] ASC,
[FieldName] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: StoredProcedure [dbo].[spLoging] Script Date: 12/18/2006 10:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoging]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
CREATE procedure [dbo].[spLoging]
@TableName nvarchar(255),
@Key nvarchar(1000)
as
BEGIN

--<debug/>
--select ''start spLoging''

declare
@UserName nvarchar(50),
@ChangingTime datetime,
@SecLogID int

declare
@KeyValue nvarchar(128),
@FieldName nvarchar(255),
@OldValue nvarchar(2000),
@NewValue nvarchar(2000)

set @UserName = suser_sname()
set @ChangingTime = getdate()

insert into
dbo.tbSecLogMain
(
ChangingTime,
UserName,
ChangedTableName,
KeyOfChangedTable
)
values
(
@ChangingTime,
@UserName,
@TableName,
@Key
)

set @SecLogID=
(
select
MAX(SecLogID)
from
dbo.tbSecLogMain
where
ChangingTime=@ChangingTime and
UserName=@UserName and
ChangedTableName=@TableName
)

exec dbo.spGetChangedKeys @Key

drop table #d_e38be7c27a86
drop table #i_e38be7c27a86

declare crKeys cursor for
select distinct k_e38be7c27a86 from ##tmp_k_e38be7c27a86

open crKeys

fetch next from crKeys
into @keyValue

while @@fetch_status=0
begin

declare crFields cursor for
select
c.name
from
dbo.sysobjects as o join dbo.syscolumns as c on(o.[id]=c.[id])
where
o.name=@TableName

open crFields

fetch next from crFields
into @FieldName

while @@fetch_status=0
begin

exec dbo.spGetValues @FieldName, @keyValue

set @OldValue=(select top 1 cast(val_e38be7c27a86 as nvarchar(2000)) from ##v_e38be7c27a86 where f_e38be7c27a86=''O'')
set @NewValue=(select top 1 cast(val_e38be7c27a86 as nvarchar(2000)) from ##v_e38be7c27a86 where f_e38be7c27a86=''N'')

drop table ##v_e38be7c27a86

if @OldValue<>@NewValue or @OldValue is NULL or @NewValue is NULL
begin
insert into
dbo.tbSecLogDatas
(
SecLogID,
KeyValue,
FieldName,
OldValue,
NewValue
)
values
(
@SecLogID,
@keyValue,
@FieldName,
cast(@OldValue as nvarchar(2000)),
cast(@NewValue as nvarchar(2000))
)
end

fetch next from crFields
into @FieldName
end

close crFields

deallocate crFields


fetch next from crKeys
into @keyValue
end

close crKeys

deallocate crKeys

drop table ##tmp_k_e38be7c27a86

END
'
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_tbSecLogDatas_tbSecLogMain]') AND type = 'F')
ALTER TABLE [dbo].[tbSecLogDatas] WITH NOCHECK ADD CONSTRAINT [FK_tbSecLogDatas_tbSecLogMain] FOREIGN KEY([SecLogID])
REFERENCES [dbo].[tbSecLogMain] ([SecLogID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbSecLogDatas] CHECK CONSTRAINT [FK_tbSecLogDatas_tbSecLogMain]

--Register table in LogSystem
dbo.spRegistertableForLoging 'tbDemoTable', 'KeyField'

--Unregister table from LogSystem
dbo.spUnregistertableForLoging 'tbDemoTable'


--changes monitoring script
select
*
from
dbo.tbSecLogMain as m join
dbo.tbSecLogDatas as d on(m.SecLogID=d.SecLogID)
where
ChangedTableName='tbDemoTable'

--Register table in LogSystem
dbo.spRegistertableForLoging 'tbDemoTable', 'KeyField'

--Unregister table from LogSystem
dbo.spUnregistertableForLoging 'tbDemoTable'


--changes monitoring script
select
*
from
dbo.tbSecLogMain as m join
dbo.tbSecLogDatas as d on(m.SecLogID=d.SecLogID)
where
ChangedTableName='tbDemoTable'

--Register table in LogSystem
dbo.spRegistertableForLoging 'tbDemoTable', 'KeyField'

--Unregister table from LogSystem
dbo.spUnregistertableForLoging 'tbDemoTable'


--changes monitoring script
select
*
from
dbo.tbSecLogMain as m join
dbo.tbSecLogDatas as d on(m.SecLogID=d.SecLogID)
where
ChangedTableName='tbDemoTable'

--Register table in LogSystem
dbo.spRegistertableForLoging 'tbDemoTable', 'KeyField'

--Unregister table from LogSystem
dbo.spUnregistertableForLoging 'tbDemoTable'


--changes monitoring script
select
*
from
dbo.tbSecLogMain as m join
dbo.tbSecLogDatas as d on(m.SecLogID=d.SecLogID)
where
ChangedTableName='tbDemoTable'

--Register table in LogSystem
dbo.spRegistertableForLoging 'tbDemoTable', 'KeyField'

--Unregister table from LogSystem
dbo.spUnregistertableForLoging 'tbDemoTable'


--changes monitoring script
select
*
from
dbo.tbSecLogMain as m join
dbo.tbSecLogDatas as d on(m.SecLogID=d.SecLogID)
where
ChangedTableName='tbDemoTable'

how to increase page rank seo articles <a href=http://xrumerservice.org>backlink service</a> backlink watch

nice to be here millenn.blog.bg blogger discovered your site via yahoo but it was hard to find and I see you could have more visitors because there are not so many comments yet. I have discovered website which offer to dramatically increase traffic to your website http://xrumerservice.org they claim they managed to get close to 1000 visitors/day using their services you could also get lot more targeted traffic from search engines as you have now. I used their services and got significantly more visitors to my blog. Hope this helps :) They offer best <a href=http://xrumerservice.org>backlinks</a> Take care. Jason