出错信息是不是:“数据库中已存在#temp对象”?
全部这样改一下,不要用临时表:
if @j=0 -- inserted
begin SET @type='insert'
insert tem_column select @table_name as table_name, @column_name as column_name, * from inserted
。
全部这样改一下,不要用临时表:
if @j=0 -- inserted
begin SET @type='insert'
insert tem_column select @table_name as table_name, @column_name as column_name, * from inserted
。
好象还是有问题,说是不让插入什么的
insert tem_column
select @table_name as table_name, @column_name as column_name, *
from inserted
这句有问题
如果是可以的,那么,如果你的触发器是通过DELETE、UPDATE触发的,这个时候还可以使用Inserted嘛(我没有做过,所以不知道)?
和
select @table_name as table_name, @column_name as column_name, * from inserted
列数是否一样?还有tem_column内列是否有约束等限制?
如果有一个变量@column_name,先前给它赋值为一个列名,后面想用这个@column_name进行查询,我该怎么做呢?
字段名 属性 长度 是否为空(1为空,0不为空)
-------------------------------------------------------------
RECORDINGID float 8 1
STYLEID float 8 1
TRACKNUMBER float 8 1
TRACKTITLE nvarchar 50 1
ARTISTID float 8 1
SAMPLEFILESPEC nvarchar 50 1
---------------------------------------------------------------
tmp表的结构如下:
字段名 属性 长度 是否为空
-----------------------------------------------------------------
tmp_ID int 4 0
tmp_Table_Name varchar 50 0
tmp_Column_Name varchar 50 0
tmp_Column_Type varchar 50 0
tmp_Column_Length varchar 50 0
tmp_Column_IsNull varchar 50 0
tmp_Type varchar 50 1
tmp_Date varchar 50 0
--------------------------------------------------------------------
tme_column表结构如下:
字段名 属性 长度 是否为空
------------------------------------------------------------------
table_name varchar 50 0
column_name varchar 50 0
column_value varchar 50 1
-------------------------------------------------------------------现有触发器如下:
CREATE TRIGGER [TRI_TRACKS] ON [dbo].[TRACKS]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @i int, @j int --判断是更新类型插入,修改,删除--声名游标,为了获取相应表的列名,列属性,列属性长度,列属性是否为空
DECLARE cursor_column CURSOR FOR select a.name ,b.name ,a.length, a.isnullable
from syscolumns a, systypes b,sysobjects d
where a.xtype=b.xusertype and a.id=d.id and d.name='TRACKS'DECLARE @column_name as varchar(20) -- 存储列名
DECLARE @column_type as varchar(20) -- 存储列属性
DECLARE @column_length as smallint -- 存储列属性长度
DECLARE @column_isnull as int -- 存储列属性是否为空DECLARE @table_name as varchar(20) -- 存储表名
DECLARE @sql as varchar(800) -- 存储查询语句(tem_column)
DECLARE @sql_tem as varchar(800) -- 存储查询语句(tmp)
DECLARE @type as varchar(20) -- 存储更新类型
DECLARE @time as datetime -- 存储更新时间SET @table_name='TRACKS' --设置表名为当前表
SET @time=getDate()
SELECT @i = COUNT(*) from inserted
SELECT @j = COUNT(*) from deletedopen cursor_column
fetch cursor_column into @column_name, @column_type, @column_length, @column_isnullwhile @@fetch_status=0 --判断游标是否取到最后一列
begin
if @i=0 -- delete
begin
SET @type='delete'
SET @sql_tem='insert opensource..tmp(tmp_Table_name, tmp_Column_name, tmp_Column_Type,
tmp_Column_Length, tmp_Column_IsNull, tmp_Type, tmp_Date)
VALUES(' + @table_name + ', ' + @column_name + ', ' +
@column_type + ', ' + convert(varchar(20),@column_length) + ', ' +
convert(varchar(20),@column_isnull) + ', ' +
@type + ', ' + convert(varchar(20),@time) + ')'
SET @sql='insert opensource..tem_column
select ' + @table_name + ' as table_name, ' +
@column_name + ' as column_name, '+
' deleted.' + @column_name + ' from deleted'
exec(@sql)
exec(@sql_tem)
end
if @j=0 -- inserted
begin
SET @type='insert'
SET @sql_tem='insert opensource..tmp(tmp_Table_name, tmp_Column_name, tmp_Column_Type,
tmp_Column_Length, tmp_Column_IsNull, tmp_Type, tmp_Date)
VALUES(' + @table_name + ', ' + @column_name + ', ' +
@column_type + ', ' + convert(varchar(20),@column_length) + ', ' +
convert(varchar(20),@column_isnull) + ', ' +
@type + ', ' + convert(varchar(20),@time) + ')'
SET @sql='insert opensource..tem_column
select ' + @table_name + ' as table_name, ' +
@column_name + ' as column_name, ' +
'deleted.' + @column_name + ' from inserted'
exec(@sql)
exec(@sql_tem)
end
if (@i<>0 and @j<>0) -- update
begin
SET @type='update'
SET @sql_tem='insert opensource..tmp(tmp_Table_name, tmp_Column_name, tmp_Column_Type,
tmp_Column_Length, tmp_Column_IsNull, tmp_Type, tmp_Date)
VALUES(' + @table_name + ', ' + @column_name + ', ' +
@column_type + ', ' + convert(varchar(20),@column_length) + ', ' +
convert(varchar(20),@column_isnull) + ', ' +
@type + ', ' + convert(varchar(20),@time) + ')'
SET @sql='insert opensource..tem_column
select ' + @table_name + ' as table_name, ' +
@column_name + ' as column_name, ' +
'inserted.' + @column_name + ' from inserted'
exec(@sql)
exec(@sql_tem)
end
end在执行删除等操作的时候总是说对象名deleted或者inserted不可用,这是为什么呢?我的触发器还是有问题,请大家帮帮忙吧
假如
declare @column_name varchar(20) -- 为了存储列名
set @column_name='a'select @column_name from table
那么上面的这个查询语句会出现下面的结果
a
a
a
a
a
....如果用动态的话就真正能查找出a列所对应的值了。
不会吧,其实我的目的是很明白的,就是想提取改动字段的属性和改动的值,把这些信息放到另外的一张表里。就这么简单,我上面说了我的目的啊。
保证没问题