触发器:
CREATE TRIGGER [tg_GetSql_Bank] ON [dbo].[bank]
FOR INSERT, UPDATE, update
AS
/*
* 截取更新的SQL语句,并重新组织,存入SQL语句表
*/
declare @datequot char(1), -- 日期型的数据引号
@sql nvarchar(1000), -- 得到的SQL语句
@type int, -- 触发器类型 1 删除 / 2 插入 / 3 更新
@prikey1 varchar(20), -- 主键1
@prikey2 varchar(20), -- 主键2
@prikey3 varchar(20), -- 主键3
@colkey nvarchar(500), -- 列名组合(主键)
@colkeyx nvarchar(500), -- 列名组合(主键)(带上@在前面)
@colkeyy nvarchar(500), -- 列名组合(主键)(带上@在前面,同时带上所属类型的引号)
@colkeyz nvarchar(500), -- 列名组合(主键)(AND colname = @colname 同时带上所属类型的引号)
@colall nvarchar(500), -- 列名组合(全部)
@colallx nvarchar(500), -- 列名组合(全部)(带上@在前面)
@colally nvarchar(500), -- 列名组合(全部)(带上@在前面,同时带上所属类型的引号)
@colallz nvarchar(500), -- 列名组合(全部)(AND colname = @colname 同时带上所属类型的引号)
@tablename nvarchar(20), -- 表名
@coldeclare nvarchar(1000), -- 列的定义
@sql_out nvarchar(4000) -- 输出的SQL语句
/**************** 初始化变量 *********************/
SELECT @datequot = '#'
SELECT @sql = ''
SELECT @type = 0
SELECT @colkey = ''
SELECT @colkeyx = ''
SELECT @colkeyy = ''
SELECT @colkeyz = ''
SELECT @colall = ''
SELECT @colallx = ''
SELECT @colally = ''
SELECT @colallz = ''
SELECT @tablename = object_name(parent_obj)
FROM sysobjects
WHERE id = @@procid
SELECT @coldeclare = ''
SELECT @sql_out = '' /**************** 判断触发器类型 *******************/
IF exists(SELECT 1 FROM updated)
SELECT @type = @type + 1
if exists(SELECT 1 FROM inserted)
SELECT @type = @type + 2
/*************** 得到所有列名和类型并存入临时表 **********/
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN DATA_TYPE
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN DATA_TYPE
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION_RADIX AS VARCHAR(5))
+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(5)) + ')'
ELSE DATA_TYPE
END AS DATA_DEFINE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN ''''''
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN @datequot
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN ''
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN ''
ELSE ''
END AS DATA_QUOT
INTO #tmp_getsql_column
FROM information_schema.columns
WHERE TABLE_NAME= @tablename /* 将主键存入临时表 */
SELECT c.column_name
INTO #tmp_getsql_prikey
FROM sysconstraints a,sysobjects b,
INFORMATION_SCHEMA.COLUMNS c
WHERE a.constid = b.id
AND (b.xtype='PK')
AND object_name(parent_obj) = @tablename
AND c.table_name = @tablename
AND c.ordinal_position = CASE WHEN b.xtype = 'pk' THEN a.colid+1 ELSE a.colid END /* 将自增字段存入临时表 */
SELECT [NAME]
INTO #tmp_getsql_identity
FROM syscolumns
WHERE id=object_id(@tablename)
AND status=128 /* 组合和列相关的字符串 */
SELECT @colkey = @colkey + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyx = @colkeyx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME
SELECT @colkeyy = @colkeyy + ' + ''' + DATA_QUOT + '''+@' + COLUMN_NAME + '+''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyz = @colkeyz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colall = @colall + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colallx = @colallx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colally = @colally + '+ ''' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME
SELECT @colallz = @colallz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME -- 截断最后一位的","
SELECT @colkey = LEFT(@colkey, LEN(@colkey) - 1)
SELECT @colkeyx = LEFT(@colkeyx, LEN(@colkeyx) - 1)
SELECT @colkeyy = LEFT(@colkeyy, LEN(@colkeyy) - 2) + ''''
SELECT @colkeyz = LEFT(@colkeyz, LEN(@colkeyz) - 2) + ''''
SELECT @colall = LEFT(@colall, LEN(@colall) - 1)
SELECT @colallx = LEFT(@colallx, LEN(@colallx) - 1)
SELECT @colally = LEFT(@colally, LEN(@colally) - 2) + ''''
SELECT @colallz = LEFT(@colallz, LEN(@colallz) - 2) + '''' SELECT @coldeclare = @coldeclare +
'declare @' + COLUMN_NAME + ' ' + DATA_DEFINE + ' '
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME /* 将inserted记录集写入临时表 */
SELECT * INTO #tmp_inserted
FROM inserted
/* 将updated记录集写入临时表 */
SELECT * INTO #tmp_updated
FROM updated /*************** 得到SQL语句(新增) ********************/
if (@type = 2)
BEGIN
declare @sql_insert nvarchar(4000) -- 动态SQL语句
SELECT @sql_insert = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_insert = @sql_insert + @coldeclare
SELECT @sql_insert = @sql_insert + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = @sql_out '
+ @colally + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
--insert into eventtable ( eventdate, eventsql) values (getdate(), @sql_insert)
--return
SELECT @sql_out = ''
EXEC sp_executesql @sql_insert,N'@sql_out nvarchar(4000) output',@sql_out output /* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
'INSERT INTO ' + @tablename
+ '(' + @colall + ') VALUES ('
+ @sql_out + ')' )
END if (@type = 1)
BEGIN
declare @sql_update nvarchar(4000) -- 动态SQL语句 SELECT @sql_update = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_updated /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_update = @sql_update + @coldeclare
SELECT @sql_update = @sql_update + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = @sql_out '
+ @colkeyz + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
--insert into eventtable ( eventdate, eventsql) values (getdate(), @sql_update)
--return EXEC sp_executesql @sql_update,N'@sql_out nvarchar(4000) output',@sql_out output /* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable ( EventDate, EventSql)
VALUES (GETDATE(),
'update FRO
CREATE TRIGGER [tg_GetSql_Bank] ON [dbo].[bank]
FOR INSERT, UPDATE, update
AS
/*
* 截取更新的SQL语句,并重新组织,存入SQL语句表
*/
declare @datequot char(1), -- 日期型的数据引号
@sql nvarchar(1000), -- 得到的SQL语句
@type int, -- 触发器类型 1 删除 / 2 插入 / 3 更新
@prikey1 varchar(20), -- 主键1
@prikey2 varchar(20), -- 主键2
@prikey3 varchar(20), -- 主键3
@colkey nvarchar(500), -- 列名组合(主键)
@colkeyx nvarchar(500), -- 列名组合(主键)(带上@在前面)
@colkeyy nvarchar(500), -- 列名组合(主键)(带上@在前面,同时带上所属类型的引号)
@colkeyz nvarchar(500), -- 列名组合(主键)(AND colname = @colname 同时带上所属类型的引号)
@colall nvarchar(500), -- 列名组合(全部)
@colallx nvarchar(500), -- 列名组合(全部)(带上@在前面)
@colally nvarchar(500), -- 列名组合(全部)(带上@在前面,同时带上所属类型的引号)
@colallz nvarchar(500), -- 列名组合(全部)(AND colname = @colname 同时带上所属类型的引号)
@tablename nvarchar(20), -- 表名
@coldeclare nvarchar(1000), -- 列的定义
@sql_out nvarchar(4000) -- 输出的SQL语句
/**************** 初始化变量 *********************/
SELECT @datequot = '#'
SELECT @sql = ''
SELECT @type = 0
SELECT @colkey = ''
SELECT @colkeyx = ''
SELECT @colkeyy = ''
SELECT @colkeyz = ''
SELECT @colall = ''
SELECT @colallx = ''
SELECT @colally = ''
SELECT @colallz = ''
SELECT @tablename = object_name(parent_obj)
FROM sysobjects
WHERE id = @@procid
SELECT @coldeclare = ''
SELECT @sql_out = '' /**************** 判断触发器类型 *******************/
IF exists(SELECT 1 FROM updated)
SELECT @type = @type + 1
if exists(SELECT 1 FROM inserted)
SELECT @type = @type + 2
/*************** 得到所有列名和类型并存入临时表 **********/
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN DATA_TYPE
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN DATA_TYPE
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION_RADIX AS VARCHAR(5))
+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(5)) + ')'
ELSE DATA_TYPE
END AS DATA_DEFINE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN ''''''
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN @datequot
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN ''
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN ''
ELSE ''
END AS DATA_QUOT
INTO #tmp_getsql_column
FROM information_schema.columns
WHERE TABLE_NAME= @tablename /* 将主键存入临时表 */
SELECT c.column_name
INTO #tmp_getsql_prikey
FROM sysconstraints a,sysobjects b,
INFORMATION_SCHEMA.COLUMNS c
WHERE a.constid = b.id
AND (b.xtype='PK')
AND object_name(parent_obj) = @tablename
AND c.table_name = @tablename
AND c.ordinal_position = CASE WHEN b.xtype = 'pk' THEN a.colid+1 ELSE a.colid END /* 将自增字段存入临时表 */
SELECT [NAME]
INTO #tmp_getsql_identity
FROM syscolumns
WHERE id=object_id(@tablename)
AND status=128 /* 组合和列相关的字符串 */
SELECT @colkey = @colkey + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyx = @colkeyx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME
SELECT @colkeyy = @colkeyy + ' + ''' + DATA_QUOT + '''+@' + COLUMN_NAME + '+''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyz = @colkeyz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colall = @colall + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colallx = @colallx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colally = @colally + '+ ''' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME
SELECT @colallz = @colallz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME -- 截断最后一位的","
SELECT @colkey = LEFT(@colkey, LEN(@colkey) - 1)
SELECT @colkeyx = LEFT(@colkeyx, LEN(@colkeyx) - 1)
SELECT @colkeyy = LEFT(@colkeyy, LEN(@colkeyy) - 2) + ''''
SELECT @colkeyz = LEFT(@colkeyz, LEN(@colkeyz) - 2) + ''''
SELECT @colall = LEFT(@colall, LEN(@colall) - 1)
SELECT @colallx = LEFT(@colallx, LEN(@colallx) - 1)
SELECT @colally = LEFT(@colally, LEN(@colally) - 2) + ''''
SELECT @colallz = LEFT(@colallz, LEN(@colallz) - 2) + '''' SELECT @coldeclare = @coldeclare +
'declare @' + COLUMN_NAME + ' ' + DATA_DEFINE + ' '
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME /* 将inserted记录集写入临时表 */
SELECT * INTO #tmp_inserted
FROM inserted
/* 将updated记录集写入临时表 */
SELECT * INTO #tmp_updated
FROM updated /*************** 得到SQL语句(新增) ********************/
if (@type = 2)
BEGIN
declare @sql_insert nvarchar(4000) -- 动态SQL语句
SELECT @sql_insert = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_insert = @sql_insert + @coldeclare
SELECT @sql_insert = @sql_insert + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = @sql_out '
+ @colally + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
--insert into eventtable ( eventdate, eventsql) values (getdate(), @sql_insert)
--return
SELECT @sql_out = ''
EXEC sp_executesql @sql_insert,N'@sql_out nvarchar(4000) output',@sql_out output /* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
'INSERT INTO ' + @tablename
+ '(' + @colall + ') VALUES ('
+ @sql_out + ')' )
END if (@type = 1)
BEGIN
declare @sql_update nvarchar(4000) -- 动态SQL语句 SELECT @sql_update = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_updated /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_update = @sql_update + @coldeclare
SELECT @sql_update = @sql_update + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = @sql_out '
+ @colkeyz + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
--insert into eventtable ( eventdate, eventsql) values (getdate(), @sql_update)
--return EXEC sp_executesql @sql_update,N'@sql_out nvarchar(4000) output',@sql_out output /* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable ( EventDate, EventSql)
VALUES (GETDATE(),
'update FRO
CREATE TRIGGER [tg_GetSql_Bank] ON [dbo].[bank]
FOR INSERT, UPDATE, update
AS
/*
* 截取更新的SQL语句,并重新组织,存入SQL语句表
*/
declare @datequot char(1), -- 日期型的数据引号
@sql nvarchar(1000), -- 得到的SQL语句
@type int, -- 触发器类型 1 删除 / 2 插入 / 3 更新
@colkey nvarchar(500), -- 列名组合(主键)
@colkeyx nvarchar(500), -- 列名组合(主键)(带上@在前面)
@colkeyy nvarchar(500), -- 列名组合(主键)(带上@在前面,同时带上所属类型的引号)
@colkeyz nvarchar(500), -- 列名组合(主键)(AND colname = @colname 同时带上所属类型的引号)
@colall nvarchar(500), -- 列名组合(全部)
@colallx nvarchar(500), -- 列名组合(全部)(带上@在前面)
@colally nvarchar(500), -- 列名组合(全部)(带上@在前面,同时带上所属类型的引号)
@colallz nvarchar(500), -- 列名组合(全部)(AND colname = @colname 同时带上所属类型的引号)
@tablename nvarchar(20), -- 表名
@coldeclare nvarchar(1000), -- 列的定义
@sql_out nvarchar(4000) -- 输出的SQL语句
/**************** 初始化变量 *********************/
SELECT @datequot = '#'
SELECT @sql = ''
SELECT @type = 0
SELECT @colkey = ''
SELECT @colkeyx = ''
SELECT @colkeyy = ''
SELECT @colkeyz = ''
SELECT @colall = ''
SELECT @colallx = ''
SELECT @colally = ''
SELECT @colallz = ''
SELECT @tablename = object_name(parent_obj)
FROM sysobjects
WHERE id = @@procid
SELECT @coldeclare = ''
SELECT @sql_out = '' /**************** 判断触发器类型 *******************/
IF exists(SELECT 1 FROM updated)
SELECT @type = @type + 1
if exists(SELECT 1 FROM inserted)
SELECT @type = @type + 2
/*************** 得到所有列名和类型并存入临时表 **********/
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN DATA_TYPE
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN DATA_TYPE
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION_RADIX AS VARCHAR(5))
+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(5)) + ')'
ELSE DATA_TYPE
END AS DATA_DEFINE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN ''''''
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN @datequot
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN ''
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN ''
ELSE ''
END AS DATA_QUOT
INTO #tmp_getsql_column
FROM information_schema.columns
WHERE TABLE_NAME= @tablename /* 将主键存入临时表 */
SELECT c.column_name
INTO #tmp_getsql_prikey
FROM sysconstraints a,sysobjects b,
INFORMATION_SCHEMA.COLUMNS c
WHERE a.constid = b.id
AND (b.xtype='PK')
AND object_name(parent_obj) = @tablename
AND c.table_name = @tablename
AND c.ordinal_position = CASE WHEN b.xtype = 'pk' THEN a.colid+1 ELSE a.colid END /* 将自增字段存入临时表 */
SELECT [NAME]
INTO #tmp_getsql_identity
FROM syscolumns
WHERE id=object_id(@tablename)
AND status=128 /* 组合和列相关的字符串 */
SELECT @colkey = @colkey + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyx = @colkeyx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME
SELECT @colkeyy = @colkeyy + ' + ''' + DATA_QUOT + '''+@' + COLUMN_NAME + '+''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyz = @colkeyz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colall = @colall + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colallx = @colallx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colally = @colally + '+ ''' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME
SELECT @colallz = @colallz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME -- 截断最后一位的","
SELECT @colkey = LEFT(@colkey, LEN(@colkey) - 1)
SELECT @colkeyx = LEFT(@colkeyx, LEN(@colkeyx) - 1)
SELECT @colkeyy = LEFT(@colkeyy, LEN(@colkeyy) - 2) + ''''
SELECT @colkeyz = LEFT(@colkeyz, LEN(@colkeyz) - 2) + ''''
SELECT @colall = LEFT(@colall, LEN(@colall) - 1)
SELECT @colallx = LEFT(@colallx, LEN(@colallx) - 1)
SELECT @colally = LEFT(@colally, LEN(@colally) - 2) + ''''
SELECT @colallz = LEFT(@colallz, LEN(@colallz) - 2) + '''' SELECT @coldeclare = @coldeclare +
'declare @' + COLUMN_NAME + ' ' + DATA_DEFINE + ' '
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME /* 将inserted记录集写入临时表 */
SELECT * INTO #tmp_inserted
FROM inserted
/* 将updated记录集写入临时表 */
SELECT * INTO #tmp_updated
FROM updated /*************** 得到SQL语句(新增) ********************/
if (@type = 2)
BEGIN
declare @sql_insert nvarchar(4000) -- 动态SQL语句
SELECT @sql_insert = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_insert = @sql_insert + @coldeclare
SELECT @sql_insert = @sql_insert + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = @sql_out '
+ @colally + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
--insert into eventtable ( eventdate, eventsql) values (getdate(), @sql_insert)
--return
SELECT @sql_out = ''
EXEC sp_executesql @sql_insert,N'@sql_out nvarchar(4000) output',@sql_out output /* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
'INSERT INTO ' + @tablename
+ '(' + @colall + ') VALUES ('
+ @sql_out + ')' )
END
/*************** 得到SQL语句(删除) ********************/
if (@type = 1)
BEGIN
declare @sql_delete nvarchar(4000) -- 动态SQL语句 SELECT @sql_delete = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_deleted /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_delete = @sql_delete + @coldeclare
SELECT @sql_delete = @sql_delete + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = @sql_out '
+ @colkeyz + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
--insert into eventtable ( eventdate, eventsql) values (getdate(), @sql_update)
--return EXEC sp_executesql @sql_delete,N'@sql_out nvarchar(4000) output',@sql_out output /* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable ( EventDate, EventSql)
VALUES (GETDATE(),
'update FROM ' + @tablename
FOR INSERT, UPDATE, DELETE
不过多用户操作,如果用全局变量的临时表,会出问题.加上@@spid又没法操作,
想不出什么办法。
FOR INSERT, UPDATE, DELETE
AS
/*
* 截取更新的SQL语句,并重新组织,存入SQL语句表
*/
declare @datequot char(1), -- 日期型的数据引号
@sql nvarchar(1000), -- 得到的SQL语句
@type int, -- 触发器类型 1 删除 / 2 插入 / 3 更新
@colkey nvarchar(500), -- 列名组合(主键)
@colkeyx nvarchar(500), -- 列名组合(主键)(带上@在前面)
@colkeyy nvarchar(500), -- 列名组合(主键)(带上@在前面,同时带上所属类型的引号)
@colkeyz nvarchar(500), -- 列名组合(主键)(AND colname = @colname 同时带上所属类型的引号)
@colall nvarchar(500), -- 列名组合(全部)
@colallx nvarchar(500), -- 列名组合(全部)(带上@在前面)
@colally nvarchar(500), -- 列名组合(全部)(带上@在前面,同时带上所属类型的引号)
@colallz nvarchar(500), -- 列名组合(全部)( colname = @colname 同时带上所属类型的引号)
@tablename nvarchar(20), -- 表名
@coldeclare nvarchar(1000), -- 列的定义
@sql_out nvarchar(4000) -- 输出的SQL语句 /**************** 初始化变量 *********************/
SELECT @datequot = '#'
SELECT @sql = ''
SELECT @type = 0
SELECT @colkey = ''
SELECT @colkeyx = ''
SELECT @colkeyy = ''
SELECT @colkeyz = ''
SELECT @colall = ''
SELECT @colallx = ''
SELECT @colally = ''
SELECT @colallz = ''
SELECT @tablename = object_name(parent_obj)
FROM sysobjects
WHERE id = @@procid
SELECT @coldeclare = ''
SELECT @sql_out = '' /**************** 判断触发器类型 *******************/
IF exists(SELECT 1 FROM deleted)
SELECT @type = @type + 1
if exists(SELECT 1 FROM inserted)
SELECT @type = @type + 2
/*************** 得到所有列名和类型并存入临时表 **********/
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN DATA_TYPE
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN DATA_TYPE
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION_RADIX AS VARCHAR(5))
+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(5)) + ')'
ELSE DATA_TYPE
END AS DATA_DEFINE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN ''''''
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN @datequot
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN ''
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN ''
ELSE ''
END AS DATA_QUOT
INTO #tmp_getsql_column
FROM information_schema.columns
WHERE TABLE_NAME= @tablename /* 将主键存入临时表 */
SELECT c.column_name
INTO #tmp_getsql_prikey
FROM sysconstraints a,sysobjects b,
INFORMATION_SCHEMA.COLUMNS c
WHERE a.constid = b.id
AND (b.xtype='PK')
AND object_name(parent_obj) = @tablename
AND c.table_name = @tablename
AND c.ordinal_position = CASE WHEN b.xtype = 'pk' THEN a.colid+1 ELSE a.colid END /* 将自增字段存入临时表 */
SELECT [NAME]
INTO #tmp_getsql_identity
FROM syscolumns
WHERE id=object_id(@tablename)
AND status=128 /* 组合和列相关的字符串 */
SELECT @colkey = @colkey + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyx = @colkeyx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME
SELECT @colkeyy = @colkeyy + ' + ''' + DATA_QUOT + '''+@' + COLUMN_NAME + '+''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyz = @colkeyz + '+'' AND ' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colall = @colall + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colallx = @colallx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME SELECT @colally = @colally + '+ ''' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME
SELECT @colallz = @colallz + '+''' + COLUMN_NAME + '=' + DATA_QUOT + '''+ISNULL(CAST(@' + COLUMN_NAME + ' AS varchar(100)),'''') +''' + DATA_QUOT + ','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME -- 截断最后一位的","
SELECT @colkey = LEFT(@colkey, LEN(@colkey) - 1)
SELECT @colkeyx = LEFT(@colkeyx, LEN(@colkeyx) - 1)
SELECT @colkeyy = LEFT(@colkeyy, LEN(@colkeyy) - 2) + ''''
SELECT @colkeyz = LEFT(@colkeyz, LEN(@colkeyz) - 2) + ''''
SELECT @colall = LEFT(@colall, LEN(@colall) - 1)
SELECT @colallx = LEFT(@colallx, LEN(@colallx) - 1)
SELECT @colally = LEFT(@colally, LEN(@colally) - 2) + ''''
SELECT @colallz = LEFT(@colallz, LEN(@colallz) - 2) + '''' SELECT @coldeclare = @coldeclare +
'declare @' + COLUMN_NAME + ' ' + DATA_DEFINE + ' '
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME /* 将inserted记录集写入临时表 */
SELECT * INTO #tmp_inserted
FROM inserted
/* 将updated记录集写入临时表 */
SELECT * INTO #tmp_deleted
FROM deleted
if (@type = 2)
BEGIN
declare @sql_insert nvarchar(4000) -- 动态SQL语句
SELECT @sql_insert = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_insert = @sql_insert + @coldeclare
SELECT @sql_insert = @sql_insert + N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = '
+ @colally + N'
/* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
''INSERT INTO ' + @tablename + N'
( ' + @colall + ') VALUES (''
+ @sql_out + '')'' )
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
EXEC sp_executesql @sql_insert,N'@sql_out nvarchar(4000)',@sql_out
END
/*************** 得到SQL语句(删除) ********************/
if (@type = 1)
BEGIN
declare @sql_delete nvarchar(4000) -- 动态SQL语句 SELECT @sql_delete = N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_deleted /* 打开游标 */
OPEN cur_Set
SELECT @sql_out = ''''
'
SELECT @sql_delete = @sql_delete + @coldeclare
SELECT @sql_delete = @sql_delete + N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out = '
+ @colkeyz + N'
/* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
''DELETE FROM ' + @tablename + N'
WHERE 1=1'' + @sql_out )
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
'
EXEC sp_executesql @sql_delete,N'@sql_out nvarchar(4000)',@sql_out END /*************** 得到SQL语句(更新) ********************/
if (@type = 3) -- 更新
BEGIN
declare @sql_update nvarchar(4000) -- 动态SQL语句
declare @sql_out_delete nvarchar(4000) -- 构造的SQL语句(删除)
declare @sql_out_insert nvarchar(4000) -- 构造的SQL语句(插入) SELECT @sql_update = N'
/* 声明游标 */
DECLARE cur_delete CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_deleted
DECLARE cur_insert CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted /* 打开游标 */
OPEN cur_delete
OPEN cur_insert
SELECT @sql_out_delete = ''''
SELECT @sql_out_insert = ''''
'
SELECT @sql_update = @sql_update + @coldeclare + REPLACE(@coldeclare,' @',' @del_')
SELECT @sql_update = @sql_update + N'
FETCH NEXT FROM cur_delete INTO '
+ REPLACE(@colkeyx,' @',' @del_') + N'
FETCH NEXT FROM cur_insert INTO '
+ @colallx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_out_delete = '
+ REPLACE(@colkeyz,' @',' @del_') + N'
SELECT @sql_out_insert = '
+ @colallz + N'
/* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
''UPDATE ' + @tablename + N'
SET '' + @sql_out_insert + '' WHERE 1=1'' + @sql_out_delete )
FETCH NEXT FROM cur_delete INTO '
+ REPLACE(@colkeyx,' @',' @del_') + N'
FETCH NEXT FROM cur_insert INTO '
+ @colallx + N'
END /* 关闭并删除游标 */
CLOSE cur_delete
DEALLOCATE cur_delete
CLOSE cur_insert
DEALLOCATE cur_insert
'
EXEC sp_executesql @sql_update,
N'@sql_out_delete nvarchar(4000),@sql_out_insert nvarchar(4000)',
@sql_out_delete,@sql_out_insert
END
/* 删除临时表 */
DROP TABLE #tmp_getsql_column
DROP TABLE #tmp_getsql_prikey
DROP TABLE #tmp_inserted
DROP TABLE #tmp_deleted
把INSERTED,DELETED生成的临时表,
临时表名用表名+用户ID构成,但它也得用动态SQL.不行。
好多人也在想,如何得到对表操作的SQL语句,你这种方法实现了,但如果一口气插入1000行:insert table1 select top 1000 from table2
你的结果是一千行的insert.严重不爽ING....
FOR INSERT, UPDATE, DELETE
AS
/*
* 截取更新的SQL语句,并重新组织,存入SQL语句表
*/
declare @datequot char(1), -- 日期型的数据引号
@type int, -- 触发器类型 1 删除 / 2 插入 / 3 更新
@colkey nvarchar(4000), -- 列名组合(主键)
@colkeyx nvarchar(4000), -- 列名组合(主键)(带上@在前面)
@colkeyy nvarchar(4000), -- 列名组合(主键)(带上@在前面,同时带上所属类型的引号)
@colkeyz nvarchar(4000), -- 列名组合(主键)(AND colname = @colname 同时带上所属类型的引号)
@colall nvarchar(4000), -- 列名组合(全部,除去自增字段)
@colallx nvarchar(4000), -- 列名组合(全部,除去自增字段)(带上@在前面)
@colally nvarchar(4000), -- 列名组合(全部,除去自增字段)(带上@在前面,同时带上所属类型的引号)
@colallz nvarchar(4000), -- 列名组合(全部,除去自增字段)( colname = @colname 同时带上所属类型的引号)
@tablename nvarchar(100), -- 表名
@coldeclare nvarchar(4000), -- 列的定义(全部,除去自增字段)
@coldeclarekey nvarchar(4000) -- 列的定义(主键) /**************** 初始化变量 *********************/
SELECT @datequot = '#'
SELECT @type = 0
SELECT @colkey = ''
SELECT @colkeyx = ''
SELECT @colkeyy = ''
SELECT @colkeyz = ''
SELECT @colall = ''
SELECT @colallx = ''
SELECT @colally = ''
SELECT @colallz = ''
SELECT @tablename = object_name(parent_obj)
FROM sysobjects
WHERE id = @@procid
SELECT @coldeclare = ''
SELECT @coldeclarekey = ''
/**************** 判断触发器类型 *******************/
IF exists(SELECT 1 FROM deleted)
SELECT @type = @type + 1
if exists(SELECT 1 FROM inserted)
SELECT @type = @type + 2
/*************** 得到所有列名和类型并存入临时表 **********/
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN DATA_TYPE
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN DATA_TYPE
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION_RADIX AS VARCHAR(5))
+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(5)) + ')'
ELSE DATA_TYPE
END AS DATA_DEFINE,
CASE
WHEN (DATA_TYPE='varchar' OR DATA_TYPE='char' OR DATA_TYPE='nvarchar' OR DATA_TYPE='nchar')
THEN ''''''
WHEN (DATA_TYPE='datetime' OR DATA_TYPE='smalldatetime')
THEN @datequot
WHEN (DATA_TYPE='int' OR DATA_TYPE='tinyint' OR DATA_TYPE='bigint'
OR DATA_TYPE='smallint' OR DATA_TYPE='bit')
THEN ''
WHEN (DATA_TYPE='float' OR DATA_TYPE='decimal' OR DATA_TYPE='real' OR DATA_TYPE='number')
THEN ''
ELSE ''
END AS DATA_QUOT
INTO #tmp_getsql_column
FROM information_schema.columns
WHERE TABLE_NAME= @tablename /* 将主键存入临时表 */
SELECT c.column_name
INTO #tmp_getsql_prikey
FROM sysconstraints a,sysobjects b,
INFORMATION_SCHEMA.COLUMNS c
WHERE a.constid = b.id
AND (b.xtype='PK')
AND object_name(parent_obj) = @tablename
AND c.table_name = @tablename
AND c.ordinal_position = CASE WHEN b.xtype = 'pk' THEN a.colid+1 ELSE a.colid END /* 将自增字段存入临时表 */
SELECT [NAME] AS COLUMN_NAME
INTO #tmp_getsql_identity
FROM syscolumns
WHERE id=object_id(@tablename)
AND status=128 /* 组合和列相关的字符串 */
SELECT @colkey = @colkey + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyx = @colkeyx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME
SELECT @colkeyy = @colkeyy + '+ CASE WHEN @' + COLUMN_NAME + ' IS NOT NULL THEN '''
+ DATA_QUOT + ''' + REPLACE(CAST(@' + COLUMN_NAME
+ ' AS varchar(1000)),'''''''','''''''''''') +''' + DATA_QUOT + ''' ELSE ''NULL'' END + '','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colkeyz = @colkeyz + '+'' AND ' + COLUMN_NAME + '= '' + CASE WHEN @' + COLUMN_NAME + ' IS NOT NULL THEN ''' + DATA_QUOT + ''' + REPLACE(CAST(@' + COLUMN_NAME + ' AS varchar(1000)),'''''''','''''''''''') +''' + DATA_QUOT + ''' ELSE ''NULL'' END + '','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME SELECT @colall = @colall + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME NOT IN (
SELECT COLUMN_NAME FROM #tmp_getsql_identity )
ORDER BY COLUMN_NAME SELECT @colallx = @colallx + ' @' + COLUMN_NAME + ','
FROM #tmp_getsql_column
WHERE COLUMN_NAME NOT IN (
SELECT COLUMN_NAME FROM #tmp_getsql_identity )
ORDER BY COLUMN_NAME SELECT @colally = @colally + '+ CASE WHEN @' + COLUMN_NAME + ' IS NOT NULL THEN '''
+ DATA_QUOT + ''' + REPLACE(CAST(@' + COLUMN_NAME
+ ' AS varchar(1000)),'''''''','''''''''''') +'''
+ DATA_QUOT + ''' ELSE ''NULL'' END + '','''
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME
SELECT @colallz = @colallz + '+''' + COLUMN_NAME + '='' + CASE WHEN @'
+ COLUMN_NAME + ' IS NOT NULL THEN ''' + DATA_QUOT + ''' + REPLACE(CAST(@'
+ COLUMN_NAME + ' AS varchar(1000)),'''''''','''''''''''') +'''
+ DATA_QUOT + ''' ELSE ''NULL'' END + '','''
FROM #tmp_getsql_column
WHERE COLUMN_NAME NOT IN (
SELECT COLUMN_NAME FROM #tmp_getsql_identity )
ORDER BY COLUMN_NAME -- 截断最后一位的","
SELECT @colkey = LEFT(@colkey, LEN(@colkey) - 1)
SELECT @colkeyx = LEFT(@colkeyx, LEN(@colkeyx) - 1)
SELECT @colkeyy = LEFT(@colkeyy, LEN(@colkeyy) - 2) + ''''
SELECT @colkeyz = LEFT(@colkeyz, LEN(@colkeyz) - 2) + ''''
SELECT @colall = LEFT(@colall, LEN(@colall) - 1)
SELECT @colallx = LEFT(@colallx, LEN(@colallx) - 1)
SELECT @colally = LEFT(@colally, LEN(@colally) - 5) --+ ''''
SELECT @colallz = LEFT(@colallz, LEN(@colallz) - 5) --+ '''' SELECT @coldeclare = @coldeclare +
'declare @' + COLUMN_NAME + ' ' + DATA_DEFINE + ' '
FROM #tmp_getsql_column
ORDER BY COLUMN_NAME
SELECT @coldeclarekey = @coldeclarekey +
'declare @' + COLUMN_NAME + ' ' + DATA_DEFINE + ' '
FROM #tmp_getsql_column
WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM #tmp_getsql_prikey )
ORDER BY COLUMN_NAME /* 将inserted记录集写入临时表 */
SELECT * INTO #tmp_inserted
FROM inserted
/* 将updated记录集写入临时表 */
SELECT * INTO #tmp_deleted
FROM deleted
if (@type = 2)
BEGIN
EXEC ( N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted /* 打开游标 */
OPEN cur_Set
'
+ @coldeclare
+ N'
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
''INSERT INTO ' + @tablename + N'
( ' + @colall + ') VALUES (''
+ ''''' + @colally + '+'')'' )
FETCH NEXT FROM cur_Set INTO '
+ @colallx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set') END
/*************** 得到SQL语句(删除) ********************/
if (@type = 1)
BEGIN EXEC ( N'
/* 声明游标 */
DECLARE cur_Set CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_deleted /* 打开游标 */
OPEN cur_Set
'
+ @coldeclare
+ N'
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
''DELETE FROM ' + @tablename + N'
WHERE 1=1'' + '
+ @colkeyz + N' )
FETCH NEXT FROM cur_Set INTO '
+ @colkeyx + N'
END /* 关闭并删除游标 */
CLOSE cur_Set
DEALLOCATE cur_Set
') END /*************** 得到SQL语句(更新) ********************/
if (@type = 3) -- 更新
BEGIN
declare @coldeclarekey_del nvarchar(4000)
declare @colkeyx_del nvarchar(4000)
declare @colkeyz_del nvarchar(4000)
SELECT @coldeclarekey_del = REPLACE(@coldeclarekey,' @',' @del_')
SELECT @colkeyx_del = REPLACE(@colkeyx,' @',' @del_')
SELECT @colkeyz_del = REPLACE(@colkeyz,' @',' @del_') --insert into eventtable values (getdate(),
EXEC (
N'/* 声明游标 */
DECLARE cur_delete CURSOR FOR
SELECT '
+ @colkey + N'
FROM #tmp_deleted
DECLARE cur_insert CURSOR FOR
SELECT '
+ @colall + N'
FROM #tmp_inserted
/* 打开游标 */
OPEN cur_delete
OPEN cur_insert
'
+ @coldeclare + @coldeclarekey_del
+ N'FETCH NEXT FROM cur_delete INTO '
+ @colkeyx_del + N'
FETCH NEXT FROM cur_insert INTO '
+ @colallx
+ N'WHILE (@@FETCH_STATUS = 0)
BEGIN
/* 构造要插入事件表的SQL语句 */
INSERT INTO EventTable (EventDate, EventSql)
VALUES (GETDATE(),
''UPDATE ' + @tablename + N'
SET '' + ' + @colallz + ' + '' WHERE 1=1'' + ' + @colkeyz_del + ' )'
+ N'FETCH NEXT FROM cur_delete INTO '
+ @colkeyx_del + N'
FETCH NEXT FROM cur_insert INTO '
+ @colallx + N'
END
/* 关闭并删除游标 */
CLOSE cur_delete
DEALLOCATE cur_delete
CLOSE cur_insert
DEALLOCATE cur_insert
') END
/* 删除临时表 */
DROP TABLE #tmp_getsql_column
DROP TABLE #tmp_getsql_prikey
DROP TABLE #tmp_getsql_identity
DROP TABLE #tmp_inserted
DROP TABLE #tmp_deleted
现在完成的版本,可是在EXEC语句中碰到了4K最大字符数的限制请问谁有解决办法?另: chiff 那些特殊的字段类型我们不会用的 default在触发器时已经生成了
declare @sql2 nvarchar(4000)
declare @sql3 nvarchar(4000)set @sql1 = N'create proc justatest as /*'+replicate(N'-',3000)
set @sql2 = replicate(N'-',4000)
set @sql3 = '*/
select 123'exec(@sql1+@sql2+@sql3)
go
N'/* 声明游标 */
DECLARE cur_delete CURSOR FOR
SELECT '
+ @colkey + N'
FROM ....
再select top count(*)/2 from .. order by .. desc