---
CREATE PROCEDURE [dbo].[procUtils_GenerateHistoryTable]
@TableName [varchar](200)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) , --The string value
DataType varchar(50) , --the datatype
IsNullable varchar(3), -- whether or not this table col is nullable
MaxLength int --the CHARACTER_MAXIMUM_LENGTH
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
DECLARE @sqlTxtToPrint varchar(max)
DECLARE @nowNiceTime varchar(400)
set @nowNiceTime = dbo.funcGetNiceTime()
--select * from sys.objects where object_name(object_id) like '%func%'
SET @sqlTxtToPrint = ' USE Gaf
GO'
--PRINT THE SQL TO
SET @sqlTxtToPrint = @sqlTxtToPrint +
'
USE [Gaf]
GO
/****** Object: Table ' + @TableName + 'History Script Date: ' + @nowNiceTime + ' ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + @TableName + 'History'') AND type in (N''U''))
DROP TABLE ' + @TableName + 'History
GO
USE [Gaf]
GO
/****** Object: Table ' + @TableName + 'History Script Date: ' + @nowNiceTime + ' ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
'
--Populate the TABLE variable using some logic
-- SELECT * from INFORMATION_SCHEMA.COLUMNS
INSERT INTO @ColNames SELECT column_name , Data_type , IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColName varchar(300);
DECLARE @DataType varchar(50)
DECLARE @PkColName varchar(200)
DECLARE @IsNullable varchar(3)
DECLARE @MaxLength int
set @sqlTxtToPrint = @sqlTxtToPrint + '
SET NOCOUNT ON;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[' + @TableName + 'History](' + CHAR(13) --Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 1
BEGIN
--Load current value from the Table
SELECT @ColName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @IsNullable = IsNullable FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @MaxLength = MaxLength FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--PRINT EACH COLUMN WITH , AT THE END
if @CurrentDelimiterPositionVar = 1
BEGIN --IF THIS IS THE FIRST COL = PK
set @PkColName = @TableName + 'HistoryId'
set @sqlTxtToPrint = @sqlTxtToPrint +
'[' + @PkColName + '][int] IDENTITY(1,1) NOT NULL,' + CHAR(13)
END --IF THIS IS THE FIRST COL = PK
SET @sqlTxtToPrint = @sqlTxtToPrint + '[' + @ColName + ']' + ' ['+ @DataType + '] '
--ADD THE (200)
IF @MaxLength IS NOT NULL
BEGIN --IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL
--xml
if @DataType != 'xml' and @DataType != 'sql_variant' and @DataType != 'text' and @DataType != 'ntext' and @DataType != 'image' and @DataType != 'hierarchyid' and @DataType != 'binary' and @DataType != 'varbinary'
begin --those with()
if @MaxLength != -1
SET @sqlTxtToPrint =
@sqlTxtToPrint + '(' + CONVERT(VARCHAR , @MaxLength ) + ')' else
SET @sqlTxtToPrint = @sqlTxtToPrint + '(max)'
end --those with(200)
else
begin
SET @sqlTxtToPrint = @sqlTxtToPrint
end
END --IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL
--ADD NULL , NOT NULL DEPENDING WHETHER OR NOT THE COLUMN IS NULLABLE
IF @IsNullable = 'YES'
SET @sqlTxtToPrint = + @sqlTxtToPrint + ' NULL '
else
SET @sqlTxtToPrint = + @sqlTxtToPrint + ' NOT NULL '
--if @CurrentDelimiterPositionVar != @Count
SET @sqlTxtToPrint = @sqlTxtToPrint + ','
IF @DataType != 'timestamp'
--DEBUGGING
--PRINT '@ColName - ' + @ColName
--PRINT '@DataType - ' + @DataType
--PRINT '@IsNullable - ' + @IsNullable
--PRINT '@CHARACTER_MAXIMUM_LENGTH - ' + CONVERT ( VARCHAR , @CHARACTER_MAXIMUM_LENGTH )
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1; set @sqlTxtToPrint = @sqlTxtToPrint + CHAR(13)
END --eof while 1
SET @CurrentDelimiterPositionVar = 1
set @sqlTxtToPrint = @sqlTxtToPrint +
'[SqlLogin] [varchar](50) NOT NULL,
[Action] [varchar](50) NOT NULL,
[UpdatedWhen] [datetime] NOT NULL,
CONSTRAINT [PK_' + @TableName + 'History] PRIMARY KEY CLUSTERED
(
[' + @PkColName + '] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING ON
'
--NOW ADD SOME USEFULL STATEMENTS
set @sqlTxtToPrint = @sqlTxtToPrint +
+ ' --SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE ''%PROC%''' + CHAR(13)
+ ' --SELECT NAME FROM SYS.TABLES WHERE NAME LIKE ''%TABLE%''' + CHAR(13)
+ ' --exec sp_HelpText procUtils_GenerateMetaInsert' + CHAR(13)
+ ' --exec procUtils_GenerateMetaInsert ''TABLE''' + CHAR(13)
--AT THE END PRINT THE RESULT
PRINT @sqlTxtToPrint
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/*
USE [Gaf]
GO
SELECT NAME FROM SYS.tables where name like '%Msg%'
EXEC [dbo].[procUtils_GenerateHistoryTable] @TableName = N'PlEditItem'
*/
---
CREATE PROCEDURE [dbo].[procUtils_GenerateOnDeleteTrigger]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) , --The string value
DataType varchar(50) --the datatype
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name , Data_type from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColumnName varchar(300);
DECLARE @DataType varchar(50)
print '
USE [Gaf]
GO
/****** Object: Trigger [trigGen_AfterDeleteOn' + @TableName + '] Script Date: 11/25/2009 10:35:45 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[trigGen_AfterDeleteOn' + @TableName + ']''))
DROP TRIGGER [dbo].[trigGen_AfterDeleteOn' + @TableName + ']
GO
USE [Gaf]
GO
/****** Object: Trigger [dbo].[trigGen_AfterDeleteOn' + @TableName + '] Script Date: 11/25/2009 10:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Yordan Georgiev>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trigGen_AfterDeleteOn' + @TableName + ']
ON [dbo].[' + @TableName + ']
for Delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into ' + @TableName + 'History
(
'
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 1
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--DO SOMETHING TO THE FIRST COLUMN
--if ( @CurrentDelimiterPositionVar = 1 )
--begin
--set @CurrentDelimiterPositionVar =@CurrentDelimiterPositionVar + 1
-- continue
--end
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] , --type of ' + @DataType -- this is the last row this time with comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 1
print '
[SqlLogin],
[Action],
[UpdatedWhen]
)
SELECT
'
-- end the insert into STATEMENT
--SELECT TOP 5 * FROM ' + @TableName + ' order by 1 desc
--AND START ALL OVER AGAIN
SET @CurrentDelimiterPositionVar = 1
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 2 start
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--DO SOMETHING TO THE FIRST COLUMN
--if ( @CurrentDelimiterPositionVar = 1 )
--begin
--set @CurrentDelimiterPositionVar =@CurrentDelimiterPositionVar + 1
-- continue
--end
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] , --type of ' + @DataType -- this is the last row this with comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 2
print 'user_name(),
''DELETE'',
getdate()'
print '
from Deleted
END
GO
'
PRINT '--SELECT * FROM [dbo].[' + @TableName + 'History] ORDER BY 1 DESC '
PRINT '--SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE ''%PROC%'''
PRINT '--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE ''%TABLE%'''
PRINT '--exec sp_HelpText procUtils_GenerateMetaInsert'
PRINT '--exec procUtils_GenerateMetaInsert ''TABLE'''
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/*
USE [Gaf]
GO
SELECT NAME FROM SYS.tables where name like '%Msg%'
EXEC [dbo].[procUtils_GenerateOnDeleteTrigger] @TableName = N'Requirement'
GO
*/
---
CREATE PROCEDURE [dbo].[procUtils_GenerateOnUpdateTrigger]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) , --The string value
DataType varchar(50) --the datatype
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
DECLARE @PkColName varchar(200)
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name , Data_type from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColumnName varchar(300);
DECLARE @DataType varchar(50)
print '
USE [Gaf]
GO
/****** Object: Trigger [trigGen_AfterUpdateOn' + @TableName + '] Script Date: 11/25/2009 10:35:45 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[trigGen_AfterUpdateOn' + @TableName + ']''))
DROP TRIGGER [dbo].[trigGen_AfterUpdateOn' + @TableName + ']
GO
USE [Gaf]
GO
/****** Object: Trigger [dbo].[trigGen_AfterUpdateOn' + @TableName + '] Script Date: 11/25/2009 10:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Yordan Georgiev>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trigGen_AfterUpdateOn' + @TableName + ']
ON [dbo].[' + @TableName + ']
for Update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into ' + @TableName + 'History
(
'
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 1
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--DO SOMETHING TO THE FIRST COLUMN
if ( @CurrentDelimiterPositionVar = 1 )
begin
SET @PkColName = @ColumnName
end
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] , --type of ' + @DataType -- this is the last row this time with comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 1
print '
[SqlLogin],
[Action],
[UpdatedWhen]
)
SELECT
'
-- end the insert into STATEMENT
--SELECT TOP 5 * FROM ' + @TableName + ' order by 1 desc
--AND START ALL OVER AGAIN
SET @CurrentDelimiterPositionVar = 1
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 2 start
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--DO SOMETHING TO THE FIRST COLUMN
--if ( @CurrentDelimiterPositionVar = 1 )
--begin
--set @CurrentDelimiterPositionVar =@CurrentDelimiterPositionVar + 1
-- continue
--end
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] , --type of ' + @DataType -- this is the last row this with comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 2
print 'user_name(),
''UPDATE'',
getdate()'
print '
from '+ @TableName + ' where '+ @PkColName + ' in (select ' + @PkColName + ' from Inserted)
END
GO
'
PRINT '--SELECT * FROM [dbo].[' + @TableName + 'History] ORDER BY 1 DESC '
PRINT '--SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE ''%PROC%'''
PRINT '--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE ''%TABLE%'''
PRINT '--exec sp_HelpText procUtils_GenerateMetaInsert'
PRINT '--exec procUtils_GenerateMetaInsert ''TABLE'''
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/*
USE [Gaf]
GO
SELECT NAME FROM SYS.tables where name like '%Msg%'
EXEC [dbo].[procUtils_GenerateOnInsertTrigger] @TableName = N'Requirement'
GO
*/
---
CREATE PROCEDURE [dbo].[procUtils_GenerateOnInsertTrigger]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) , --The string value
DataType varchar(50) --the datatype
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
DECLARE @PkColName varchar(200)
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name , Data_type from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColumnName varchar(300);
DECLARE @DataType varchar(50)
print '
USE [Gaf]
GO
/****** Object: Trigger [trigGen_AfterInsertOn' + @TableName + '] Script Date: 11/25/2009 10:35:45 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[trigGen_AfterInsertOn' + @TableName + ']''))
DROP TRIGGER [dbo].[trigGen_AfterInsertOn' + @TableName + ']
GO
USE [Gaf]
GO
/****** Object: Trigger [dbo].[trigGen_AfterInsertOn' + @TableName + '] Script Date: 11/25/2009 10:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Yordan Georgiev>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trigGen_AfterInsertOn' + @TableName + ']
ON [dbo].[' + @TableName + ']
for Insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into ' + @TableName + 'History
(
'
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 1
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--DO SOMETHING TO THE FIRST COLUMN
if ( @CurrentDelimiterPositionVar = 1 )
begin
SET @PkColName = @ColumnName
end
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] , --type of ' + @DataType -- this is the last row this time with comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 1
print '
[SqlLogin],
[Action],
[UpdatedWhen]
)
SELECT
'
-- end the insert into STATEMENT
--SELECT TOP 5 * FROM ' + @TableName + ' order by 1 desc
--AND START ALL OVER AGAIN
SET @CurrentDelimiterPositionVar = 1
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --while 2 start
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--DO SOMETHING TO THE FIRST COLUMN
--if ( @CurrentDelimiterPositionVar = 1 )
--begin
--set @CurrentDelimiterPositionVar =@CurrentDelimiterPositionVar + 1
-- continue
--end
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] , --type of ' + @DataType -- this is the last row this with comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 2
print 'user_name(),
''INSERT'',
getdate()'
print '
from '+ @TableName + ' where '+ @PkColName + ' in (select ' + @PkColName + ' from Inserted)
END
GO
'
PRINT '--SELECT * FROM [dbo].[' + @TableName + 'History] ORDER BY 1 DESC '
PRINT '--SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE ''%PROC%'''
PRINT '--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE ''%TABLE%'''
PRINT '--exec sp_HelpText procUtils_GenerateMetaInsert'
PRINT '--exec procUtils_GenerateMetaInsert ''TABLE'''
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/*
USE [Gaf]
GO
SELECT NAME FROM SYS.tables where name like '%Msg%'
EXEC [dbo].[procUtils_GenerateOnInsertTrigger] @TableName = N'Msg'
GO
*/
---
CREATE PROCEDURE [dbo].[procUtils_GenerateTableHistorySql]
@TableName varchar(50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
declare @procedureName varchar(200)
declare @procStep varchar(4000)
declare @thisProcName varchar(300)
set @thisProcName= ( SELECT OBJECT_NAME(@@PROCID))
set @procStep = ' '
BEGIN TRY --begin try
-- Action !!!
exec procUtils_GenerateHistoryTable @TableName
exec procUtils_GenerateOnInsertTrigger @TableName
exec procUtils_GenerateOnUpdateTrigger @TableName
exec procUtils_GenerateOnDeleteTrigger @TableName
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/*
USE [Gaf]
GO
DECLARE @return_value int,
@ret int
EXEC @return_value = [dbo].[procUtils_GenerateTableHistorySql1]
@UserSessionId = 2,
@PageTypeId = 12,
@msgOut = NULL,
@debugMsgOut = NULL,
@ret = @ret OUTPUT
SELECT @ret as N'@ret'
SELECT 'Return Value' = @return_value
SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE '%grep%'
exec SP_HelpText 'proc'
GO
*/
Posts
