• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

触发器 行触发 游标 字符长度 inserted和deleted一致 保存 1000分

lifeforu 北京赛智科技有限公司 项目经理  2004-09-02 05:53:08
描述: 获取数据更改变化并记录
方案: 通过触发器分别获取INSERT,UPDATE,DELETE的操作数据,记录到表或文件
问题: 1:SQLSERVER触发器不能像ORACLE那样行触发,如果能达到行触发就勿须用到游标,有没有方案做到行触发?BTW:应用系统不得更改;
2:查阅较多资料均告之"不推荐在触发器中使用游标",请问如果在触发器中使用游标会有哪些后果?有没有致命后果?
3:若不用游标有什么好的办法从inserted,deleted读取数据?
4:在update触发器中inserted和deleted中的数据是不是一定匹配为第一行对应第一行...?
5:存放数据记录的表如何设计?(关键是保存数据的字段是什么类型?)
6:SQLSERVER的数据类型在保存到文件再次更新时有哪些限制,如text,ntext,image字段能不能取出文本保存并以UPDATE的方法来更新?如果不可以有没有解决方案?
目前我采用的办法是:
declare @content varchar(8000)--最大值极有可能不够用
set @content = ''
select @content = @content + cast(field1 as varchar),
@content = @content + cast(field2 as varchar),
@content = @content + cast(field3 as varchar)
from inserted--deleted
这样做如果在一行且所有行的数据字符不超过8000时有效.能不能实现类似ORACLE的LONG类型可用于局部变量的做法?text,ntext,image均不能用于局部类型

在ORACLE里我用LONG类型来保存更新的数据,基本上实现没有太大的问题,在SQLSERVER中如何实现?

如果在记录表中使用text类型,那么在trigger中如何保存及操作?

不知道哪位高人有更好的解决方案还请不啬指教!!!
愿意付出1000分.
联系方式 QQ:670340 MSN:lifeforu51[at]hotmail[dot]com
...全文
341 点赞 收藏 25
写回复
25 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lifeforu 2004-09-06
-----------------------------------------???
回复
zjcxc 元老 2004-09-03
如果表中有主键/聚集索引,则只允许单条更新?这是系统规定还是人为规定?


这当然是人为规定,因为有主键/聚集索引的表,如果修改了主键/索引字段的值,则会首先inserted与deleted表的记录顺序不一致的情况,这个上面也说过一次了.
回复
lifeforu 2004-09-03
to pbsql(风云):
我所想的就是仅仅在触发器上面做文章,而不能影响原来的系统及应用
回复
zjcxc 元老 2004-09-03
我看你还是没有仔细看我写的

我并没有针对你的处理要求来写,我只是写了另一种记录方法,我的记录是每个字段一条记录,这点你如果仔细看了,应该看得出来

所以并不存在记录超长的问题.
回复
pbsql 2004-09-03
如果一个表没有自增列(或记录更改前后值不变的列),多条更新时inserted & deleted中的数据是不可能确定它们哪些是对应的
回复
pbsql 2004-09-03
不允许更改原数据库及应用系统,那还讨论什么呢?

在表中加一自增列不会对系统造成什么影响吧?
回复
lifeforu 2004-09-03
to zjcxc(邹建) :
我仔细地看了一下你的示例代码,我认为依然没有解决我所描述的数据过长的问题:
--操作记录明细表
create table Log_record(
编号 int,
字段 sysname,
修改前值 sql_variant, --记录新增或修改前的值
修改后值 sql_variant --记录删除或修改后的值
)
create trigger tr_表A_reinsert on 表A
insert Log_record(编号,字段,修改前值)
select scope_identity(),'ID',cast(id as sql_variant) from inserted
union all
select scope_identity(),'NAME',cast(NAME as sql_variant) from inserted
union all
select scope_identity(),'AGE',cast(AGE as sql_variant) from inserted
union all
select scope_identity(),'SEX',cast(SEX as sql_variant) from inserted
因为不确定数据库中的表总数,更不能确定表中的列数,sql_variant 字节数限8016
所以不能满足我的设计思路
对于我认为主要问题解决变量不够长的问题我的想法是能过这样的语句一次性地读取inserted/deleted中的数据而不使用游标!
CREATE TRIGGER TEST ON TEST FOR INSERT,DELETE,UPDATE
DECLARE @CONTENT VARCHAR(8000)--问题所在
SET @I=0
SELECT @I=@I+1
,@CONTENT = @CONTENT + '<CHANGE' + CAST(@I AS VARCHAR) +'>'
,@CONTENT = @CONTENT + '<RECID TYPE="INT">' + CAST(RECID AS VARCHAR) + '</RECID>'
,@CONTENT = @CONTENT + '<CODE TYPE="INT">' + CAST(CODE AS VARCHAR) + '</CODE>'
,@CONTENT = @CONTENT + '<DIRECTION TYPE="VARCHAR">' + CAST(DIRECTION AS VARCHAR) +'</DIRECTION>'
,@CONTENT = @CONTENT + '<TYPE TYPE="VARCHAR">' + CAST(TYPE AS VARCHAR) + '</TYPE></CHANGE' + CAST(@I AS VARCHAR) + '>'
FROM INSERTED
INSERT INTO LOGTABLE (TEXTDATA)VALUES(@CONTENT)

对于事件探察器的考虑:
我的方案1就是用trace实现的,但是这样做风险较大,因为所有的命令都必须依赖于上一条的命令成功执行
还有问题就是SQL2000不支持通过命令记录日志到表,这样就需要用户手工开启一个记录到表的trace
可能是我对trace研究不够吧,按照手册上的:
SELECT *FROM ::fn_trace_geteventinfo(trace_id)
WHERE EventID= 'x'来获取跟踪日志我没有实现....
我以前的做法是手工开启一个跟踪并记录到表中去,然后定时读表-保存-删表记录

TO pbsql(风云)
现在的情况是不允许更改原数据库及应用系统,所以你所推荐的方案不能通过的

TO pengda1i(冒牌大力 V0.2)
如果我没有理解错误的话instead of 触发器用来替代原有的UPDATE OR INSERT OR DELETE操作,
操作是必须进行,数据仍然需要获取,所以我认为没有必要涉及到INSTEAD OF触发器,因为即使使用INSTEAD OF触发器
同样也存在读取数据的问题
//这个问题我觉得楼主有误解(整个贴都说明楼主对触发器有误解),
//inserted和deleted的对应应该是根据表的主键来对应;
//何谓“读取数据”?select * from inserted是不是可以读取数据?
//既然已经放到了临时表,读取就不成为问题。
我对触发器确实研究不深,大致看了些文档和资料,我所谓的读取数据指的是在
不使用游标(如大家所说游标是低效率)的情况下把inserted以及deleted中的数据
完全读取并加以存放,且存放只能是按标准存放,也就是说所有的表的触发器所获取
的数据可能要存放在一个表中,比如说:
dtdatetime user station deletedtextdata
2004.09.03 11:03 test work1 <row1><f1>1</f1><f2>a</f2></row1><row2><f1>2</f1><f2>b</f2></row2>.....
insertedtextdata
<row1><f1>11</f1><f2>a</f2></row1><row2><f1>22</f1><f2>b</f2></row2>.....
.....
对于:在update触发器中inserted和deleted中的数据是不是一定匹配为第一行对应第一行...?
我的意思是:如果一个表有或是没有主键/聚集索引,
当UPDATE操作影响行数大于1行时对应情况如何?
对于邹版主的代码:
--if @@rowcount<>1 return --如果表中有主键/聚集索引,则只允许单条更新,即用这个条件
select mid=identity(int,1,1),* into #i from inserted
select mid=identity(int,1,1),* into #d from deleted
如果表中有主键/聚集索引,则只允许单条更新?这是系统规定还是人为规定?
如果是人为规定的话怕是不能在实际应用中使用吧,对于两条读取写入临时表
的操作我认为这样依然是黙认inserted & deleted中的数据是行行匹配

回复
yccai 2004-09-03
学习中...
回复
pengda1i 2004-09-03
1:SQLSERVER触发器不能像ORACLE那样行触发,如果能达到行触发就勿须用到游标,有没有方案做到行触发?BTW:应用系统不得更改;

了解和学习instead of 触发器的工作原理,也许对你有帮助。

2:查阅较多资料均告之"不推荐在触发器中使用游标",请问如果在触发器中使用游标会有哪些后果?有没有致命后果?

既然是不推荐,特殊情况该用还是要用,游标是低效率的,触发器是不好控制的,两个加在一起,就是极难预计效率,所以后果应该能想到。

3:若不用游标有什么好的办法从inserted,deleted读取数据?

这个问题我觉得楼主有误解(整个贴都说明楼主对触发器有误解),inserted和deleted的对应应该是根据表的主键来对应;何谓“读取数据”?select * from inserted是不是可以读取数据?既然已经放到了临时表,读取就不成为问题。

4:在update触发器中inserted和deleted中的数据是不是一定匹配为第一行对应第一行...?

这个上面回答了

5:存放数据记录的表如何设计?(关键是保存数据的字段是什么类型?)
6:SQLSERVER的数据类型在保存到文件再次更新时有哪些限制,如text,ntext,image字段能不能取出文本保存并以UPDATE的方法来更新?如果不可以有没有解决方案?

这两个我不回答了,邹建版主说的我同意。
回复
pbsql 2004-09-03
关于SQL SERVER触发器,没有行触发,但可以在表中加一自增列id来标记记录,这样inserted、deleted就可以通过该列来关联了

不推荐在触发器中使用游标,因为游标的效率较低,但没有致命后果
回复
lyzzbbgo 2004-09-03
mark,学习一下.
回复
pengda1i 2004-09-03
意义不大
以前讨论过,也有代码

回复
lifeforu 2004-09-03
谁有呢?在CSDN搜了半天都没找着什么好的消息,不知道谁能公开一点Lumigent log explorer的做法就好了
回复
yccai 2004-09-03
SQLSERVER2000有没有提供接口,直接读取在线事务日志.
就象Lumigent log explorer一样估计就OK了.

回复
quansui 2004-09-03
学习。
回复
lifeforu 2004-09-03
如此说来利用触发器来做数据记录变化是不可行的了?
回复
zjcxc 元老 2004-09-02
我觉得现在主要问题就是如何解决变量不够长的问题
其次就是解决update时inserted与deleted中数据匹配的问题


我想如果你仔细看了我的示例,应该看到我示例中的说明.
回复
zjcxc 元老 2004-09-02
在你的示例中也用到了游标,如果采用游标的方法那么我就要数据库中的所有触发器中使用游标,我非常但心由此对数据库造成灾难


我本来就不赞成自己写触发器做日志记录,觉得没有什么实际意义,如果真的是要记录,我喜欢事件探察器,自己要写详细记录,难免要用到游标


回复
zjcxc 元老 2004-09-02
//5:存放数据记录的表如何设计?(关键是保存数据的字段是什么类型?)
//执行的语句 nvarchar(255))
这字段怕是不能保存执行的语句或是操作数据吧!

只能取得sql语句的前255个字符
回复
lifeforu 2004-09-02
to zjcxc(邹建) :
首先非常感谢你的帮助!!!
//3:若不用游标有什么好的办法从inserted,deleted读取数据?
//要看你的记录是记录到什么程度了
当然是读取所有的操作并记录

//4:在update触发器中inserted和deleted中的数据是不是一定匹配为第一行对应第一行...?
//不一定,如果你的表中有主键/聚集索引,当改变了这种字段的值的时候,就会出现两个表中的记录对不上号的情况
那如何是好?(无奈???)我如何来对应这些数据?

//5:存放数据记录的表如何设计?(关键是保存数据的字段是什么类型?)
//执行的语句 nvarchar(255))
这字段怕是不能保存执行的语句或是操作数据吧!

//6:SQLSERVER的数据类型在保存到文件再次更新时有哪些限制,如text,ntext,image字段能不能取出文本保存并以UPDATE的方法来更新?如果不可以有没有解决方案?
//你不用变量,直接插入数据,就可以解决超长的问题,当然,你的转换是有问题的,不是所有的字段类型直接用cast转换都没有问题.
//insert 日志记录表select cast(field1 as varchar)
//+cast(field2 as varchar)
//+cast(field3 as varchar)
//from inserted--deleted
我的语句实际上是把inserted中所的记录都给读出来了,你这语句是不是仅针对一条数据?

//不过,sql提供了事件探察器来跟踪SQL的操作,并且可以把跟踪操作保存到表/文件中,并且提供了很多选项来筛选要跟踪的内容
//个人觉得用它来记录日志,比自己写触发器强多了,而且对数据库性能的影响也很小.
我的方案1就是用trace实现的,但是这样做风险较大,因为所有的命令都必须依赖于上一条的命令成功执行
还有问题就是SQL2000不支持通过命令记录日志到表,这样就需要用户手工开启一个记录到表的trace

在你的示例中也用到了游标,如果采用游标的方法那么我就要数据库中的所有触发器中使用游标,我非常但心由此对数据库造成灾难

我觉得现在主要问题就是如何解决变量不够长的问题
其次就是解决update时inserted与deleted中数据匹配的问题
//select mid=identity(int,1,1),* into #i from inserted
//select mid=identity(int,1,1),* into #d from deleted
我感觉你这样做也是默认了行数匹配吧
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-09-02 05:53
社区公告
暂无公告