set nocount on
--Auto archive script
--blindman, 3/2/2006
--blindman, 11/7/2007:	Enhanced to handle inserts/updates/deletes

--creates a script to create archive tables and triggers for saving deleted records.

declare	@TableNameMask varchar(50)
declare	@ArchiveSuffix varchar(50)
declare	@DeletedColumnName varchar(50)
set	@TableNameMask = 'TableNameMask'
set	@ArchiveSuffix = '_arc'
set	@DeletedColumnName = 'DeletedFlag'  --Will indicate whether archive record was the result of a delete operation.

declare	@TableName varchar(500)
declare	@FieldDefs varchar(max)
declare	@FieldList varchar(max)
declare	@DeletedList varchar(max)
declare	@SQLStringTABLECREATE varchar(max)
declare	@SQLStringTRIGGERHEADER varchar(max)
declare	@SQLStringTRIGGERINSERT varchar(max)
declare	@SQLStringTRIGGERDELETE varchar(max)
declare	@KeyList varchar(max)
declare	@FirstKey varchar(100)


/*
select '/*'
select	name as 'The following tables will be scripted:'
from	sysobjects
where	type = 'U'
	and name not like 'dt%'
	and name not like '%' + @ArchiveSuffix
	and name not like '%staging%'
order by name
select '*/'
*/

declare	TableList cursor for
	select	name
	from	sysobjects
	where	type = 'U'
		and name like @TableNameMask
		and name not like 'dt%'
		and name not like '%' + @ArchiveSuffix
		and name not like '%staging%'
		and name not in ('sysdiagrams')
	order by name

open TableList
fetch next from TableList into @TableName

while @@fetch_status = 0
begin
	set @FieldDefs = null
	set @FieldList = null
	set @DeletedList = null
	set @SQLStringTABLECREATE = null
	set @SQLStringTRIGGERHEADER = null
	set @SQLStringTRIGGERINSERT = null
	set @SQLStringTRIGGERDELETE = null
	set @KeyList = null
	set @FirstKey = null

	select	@FieldDefs = isnull(@FieldDefs + ',' +char(10) + '	', '')
		+ syscolumns.name
		+ 	' ' + systypes.name
				+ isnull(' (' + case systypes.name
						when 'timestamp' then Null
						when 'smalldatetime' then Null
						when 'datetime' then Null
						when 'int' then Null
						when 'bigint' then Null
						when 'smallint' then Null
						when 'tinyint' then Null
						when 'real' then Null
						when 'bit' then Null
						when 'money' then Null
						when 'uniqueidentifier' then Null
						when 'xml' then Null
						when 'decimal' then cast(syscolumns.prec as varchar(10)) + ', ' + cast(syscolumns.scale as varchar(10))
						when 'numeric' then cast(syscolumns.prec as varchar(10)) + ', ' + cast(syscolumns.scale as varchar(10))
						else case when syscolumns.length = -1 then 'max' else cast(syscolumns.length as varchar(10)) end
						end + ')', '')
			 	+ ' ' + case syscolumns.isnullable when 0 then 'NOT NULL' else 'NULL' end
	from	syscolumns
		inner join sysobjects on syscolumns.id = sysobjects.id
		inner join systypes on syscolumns.xusertype = systypes.xusertype
	where	sysobjects.name = @TableName
		and systypes.name not in ('text', 'ntext', 'image') --These cannot be referenced in the deleted table
		and syscolumns.name not in ('Modified', 'Modifier')
	order by syscolumns.colid

	select	@FieldList = isnull(@FieldList + ',' +char(10) + '	', '')
		+ syscolumns.name
	from	syscolumns
		inner join sysobjects on syscolumns.id = sysobjects.id
		inner join systypes on syscolumns.xusertype = systypes.xusertype
	where	sysobjects.name = @TableName
		and systypes.name not in ('text', 'ntext', 'image') --These cannot be referenced in the deleted table
		and syscolumns.name not in ('Modified', 'Modifier')
	order by syscolumns.colid

	select	@DeletedList = isnull(@DeletedList + ',' +char(10) + '	', '')
		+ 'deleted.' + syscolumns.name
	from	syscolumns
		inner join sysobjects on syscolumns.id = sysobjects.id
		inner join systypes on syscolumns.xusertype = systypes.xusertype
	where	sysobjects.name = @TableName
		and systypes.name not in ('text', 'ntext', 'image') --These cannot be referenced in the deleted table
		and syscolumns.name not in ('Modified', 'Modifier')
	order by syscolumns.colid

	select	@KeyList = isnull(@KeyList + char(10) + '	and ', '') + 'deleted.' + columns.name + ' = inserted.' + columns.name
	from	sys.objects objects
		inner join sys.indexes indexes on objects.object_id = indexes.object_id
		inner join sys.index_columns index_columns
		    on indexes.object_id = index_columns.object_id
		    and indexes.index_id = index_columns.index_id
		inner join sys.columns columns
		    on index_columns.object_id = columns.object_id
		    and index_columns.column_id = columns.column_id
	where	is_primary_key = 1
		and objects.name = @TableName

	set @FirstKey = 
	    (select	top 1 columns.name
	    from	sys.objects objects
		    inner join sys.indexes indexes on objects.object_id = indexes.object_id
		    inner join sys.index_columns index_columns
			on indexes.object_id = index_columns.object_id
			and indexes.index_id = index_columns.index_id
		    inner join sys.columns columns
			on index_columns.object_id = columns.object_id
			and index_columns.column_id = columns.column_id
	    where	is_primary_key = 1
		    and objects.name = @TableName
	    order by columns.column_id)

	set	@SQLStringTABLECREATE = '--<<[' + @TableName + ']>>--' + char(10)
		+ 'Create table [' + @TableName + @ArchiveSuffix + ']' + char(10)
		+ '	(' + @FieldDefs + ',' + char(10)
		+ '	' + @DeletedColumnName + ' bit NOT NULL,' + char(10)
		+ '	Modified datetime NULL,' + char(10)
		+ '	Modifier varchar (50) NULL)' + char(10)
		+ 'GO' + char(10)
		+ char(10)
		+ 'ALTER TABLE [' + @TableName + @ArchiveSuffix + '] ADD ' + char(10)
		+ '	CONSTRAINT [DF_' + @TableName + @ArchiveSuffix + '_Deleted] DEFAULT (0) FOR [' + @DeletedColumnName + '],' + char(10)
		+ '	CONSTRAINT [DF_' + @TableName + @ArchiveSuffix + '_Modified] DEFAULT (getdate()) FOR [Modified],' + char(10)
		+ '	CONSTRAINT [DF_' + @TableName + @ArchiveSuffix + '_Modifier] DEFAULT (suser_sname()) FOR [Modifier]' + char(10)
		+ 'GO' + char(10)

	set	@SQLStringTRIGGERHEADER ='CREATE TRIGGER [TR_' + @TableName + '_Archiver] ON [' + @TableName + ']' + char(10)
		+ 'FOR INSERT, UPDATE, DELETE' + char(10)
		+ 'AS' + char(10)
		+ 'set nocount on' + char(10)
		+ 'if exists (select * from dbo.sysobjects where id = object_id(N''[' + @TableName + @ArchiveSuffix + ']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)' + char(10)
		+ 'begin'

	set	@SQLStringTRIGGERINSERT = 'insert into [' + @TableName + @ArchiveSuffix + ']' + char(10)
		+ '	(' + @FieldList + ',' + char(10)
		+ '	' + @DeletedColumnName + ')' + char(10)
		+ 'select	' + @FieldList + ',' + char(10)
		+ '	0' + char(10)
		+ 'from	inserted' + char(10)
		+ 'where	TRIGGER_NESTLEVEL() = 1' + char(10)

	set	@SQLStringTRIGGERDELETE = 'insert into [' + @TableName + @ArchiveSuffix + ']' + char(10)
		+ '	(' + @FieldList + ',' + char(10)
		+ '	' + @DeletedColumnName + ')' + char(10)
		+ 'select	' + @DeletedList + ',' + char(10)
		+ '	1' + char(10)
		+ 'from	deleted' + char(10)
		+ 'left outer join inserted on ' + @KeyList + char(10)
		+ 'where inserted.' + @FirstKey + ' is null' + char(10)
		+ 'end' + char(10)
		+ 'GO'

	if @FirstKey is not null
	    begin
	    print	@SQLStringTABLECREATE
	    print	@SQLStringTRIGGERHEADER
	    print	@SQLStringTRIGGERINSERT
	    print	@SQLStringTRIGGERDELETE
	    end
	else select '--NO PRIMARY KEY ON TABLE ' + @TableName + '!'

	fetch next from TableList into @TableName
end

close tablelist
deallocate tablelist

quote:

---

Originally posted by blindman
Create your primary table and then run the script below, which will generate a script for creating an archive table and a trigger on your main table to process it.

---

quote:

---

But what do you mean with the trigger? Does it automatically fill the archive table with old data? And if so, how does it determine which row could be archived? OK, I got something to read for the weekend

---

PROCEDURE [dbo].[GetCurrentUsers]
	@BusinessUnitID INT,
	@LatestUpdate DATETIME = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @LatestUpdate IS NULL 
		SET @LatestUpdate = GetDate()
		
  SELECT PS.*, latest.*
	FROM UserStatics PS
	INNER JOIN
	(
	SELECT PF.*
	FROM UserFlexibles PF
	INNER JOIN
	(
		SELECT UserID,BusinessUnitID,MAX(FetchedDate) AS 'FetchedDate' FROM UserFlexibles
		WHERE BusinessUnitID = @BusinessUnitID AND FetchedDate < @LatestUpdate
		GROUP BY UserID,BusinessUnitID
	)t
	ON t.UserID=PF.UserID
	AND t.BusinessUnitID=PF.BusinessUnitID
	AND t.FetchedDate =PF.FetchedDate
	)latest
	ON latest.UserID = PS.UserID
END

SELECT UserID,BusinessUnitID,MAX(FetchedDate) AS 'FetchedDate' FROM UserFlexibles
		WHERE BusinessUnitID = @BusinessUnitID AND FetchedDate < @LatestUpdate
		GROUP BY UserID,BusinessUnitID

PROCEDURE [dbo].[GetCurrentUsers]
	@BusinessUnitID INT,
	@LatestUpdate DATETIME = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @LatestUpdate IS NULL 
		SET @LatestUpdate = GetDate()
		
  SELECT PS.*, latest.*
	FROM UserStatics PS
	INNER JOIN
	(
	SELECT PF.*
	FROM UserFlexibles PF
	INNER JOIN
	(
		SELECT UserID,BusinessUnitID,MAX(FetchedDate) AS 'FetchedDate' FROM UserFlexibles
		WHERE BusinessUnitID = @BusinessUnitID AND FetchedDate < @LatestUpdate
		GROUP BY UserID,BusinessUnitID
	)t
	ON t.UserID=PF.UserID
	AND t.BusinessUnitID=PF.BusinessUnitID
	AND t.FetchedDate =PF.FetchedDate
        OUTER APPLY (SELECT BusinessUnitID
                     FROM UserFlexibles
                     WHERE UserID=PF.UserID
                     AND FetchedDate>t.FetchedDate
                     AND BusinessUnitID<>PF.BusinessUnitID)b
        WHERE b.BusinessUnitID IS NULL
	)latest
	ON latest.UserID = PS.UserID
END