历史记录的触发器求优化

Question

刚接触存储过程，东拼西凑的写了这些，因为急着用于做个表的历史记录。现求助大家。
1、不知这些语句有没有什么问题。
2、会不会效率很慢，有没有办法提高效率？
3、有人说，这个当，Qty,price,deliverydate都更改时，会插入三条，应该无论如何，只插入一条比较节约空间与效率，但只插一条的话，那个更改字段怎么办？哪位大侠有好办法吗？
4、有没有好办法真正获得IP,用户名，电脑名，而不是有时候有，有时候没有。
谢谢帮助。
IF EXISTS (SELECT * FROM dbo.sysobjects 
           WHERE id = object_id(N'[TR_OdrBatRec]') 
           AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER [TR_OdrBatRec]
GOCreate TRIGGER [TR_OdrBatRec] on [dbo].[Mkt_OdrBatch]
For Insert,Update
--此位置也可以写After Insert或before Insert等等
As--Set nocount on
--上一句先不使用，以利于发现出错。
declare @dtForwardDeliveryDate DateTime
declare @dtModiDeliveryDate DateTimedeclare @iForwardQty Int
declare @iModiQty intdeclare @fForwardPrice float
declare @fModiPrice floatdeclare @sFieldName char(13)
Declare @sNetAddress nchar(12)
Declare @sNtUserName varchar(50)
declare @sSQLcmd varchar(255)--要在此处赋值，否则有可能提示插入空值
--查找前后DeliveryDate
Select @dtForwardDeliveryDate=DeliveryDate From Deleted
Select @dtModiDeliveryDate=DeliveryDate From Inserted
--查找前后 Pcs的Qty
Select @iForwardQty=Qty From Deleted
Select @iModiQty=Qty From Inserted
--查找前后 Price
Select @fForwardPrice=Price From Deleted
Select @fModiPrice=Price From Inserted
--下面为获取当前用户最后一句SQL语句，因程序限制，只能获得255个字符。
if exists (Select * From sysobjects where id=Object_id(N'#tSQLCmd') and ObjectProperty(id, N'IsUserTable')=1)
  drop table #tSQLCmd
create table #ip(id int identity(1,1),re varchar(200) null)
Create Table #tSQLCmd(sEventType varchar(50),iParameters int,sEventInfo varchar(255))
insert #tSQLCmd Exec('DBCC InputBuffer (@@SPID)')
Select @sSQLCmd=sEventInfo From #tSQLCmd
--上面完成获得最后一句SQL语句的过程，有可能还需要改善。--此处不使用if update(DeliveryDate),因为有时数值相同，但也做update操作。
--更新DeliveryDate的更改。
--比较时要使用Convert 以免明明是同一内容，因存储的原因而比较成数据有差异，因为此处只使用到日期：2000-01-01，所以只使用到Char(10)
if (Convert(char(10),@dtForwardDeliveryDate,20)<>Convert(char(10),@dtModiDeliveryDate,20))
Begin
Select @sFieldName='DeliveryDate'
Select @sNetAddress=Net_Address from master..sysprocesses where spid=@@SPID
Select @sNtUserName=Nt_UserName From master..sysprocesses where spid=@@SPID  Insert into Mkt_OdrHisModi (BatchNo,ChangeNo,FieldName,ForwardContent,ModiContent,CreateBy,CreateDate,HostName,ProgName,NetAddress,sSQLCmd)
  Select BatchNo,ChangeNo,@sFieldName,@dtForwardDeliveryDate,@dtModiDeliveryDate,@sNTUserName,GetDate(),Host_Name(),App_Name(),@sNetAddress,@sSQLCmd From Inserted
End  
--更新pcs的Qty的更改。
else if (@iForwardQty<>@iModiQty)
BeginSelect @sFieldName='PcsQty'
Select @sNetAddress=Net_Address from master..sysprocesses where spid=@@SPID
Select @sNtUserName=Nt_UserName From master..sysprocesses where spid=@@SPID  Insert into Mkt_OdrHisModi (BatchNo,ChangeNo,FieldName,ForwardContent,ModiContent,CreateBy,CreateDate,HostName,ProgName,NetAddress,sSQLCmd)
  Select BatchNo,ChangeNo,@sFieldName,@iForwardQty,@iModiQty,@sNTUserName,GetDate(),Host_Name(),App_Name(),@sNetAddress,@sSQLCmd From Inserted
End  
--更表Price的更改
--此处使用Convert来限制数字，以免出现，3.5000000<>3.499999的情况。
else if (Convert(Numeric(18,4),@fForwardPrice)<>Convert(Numeric(18,4),@fModiPrice))
BeginSelect @sFieldName='Price'
Select @sNetAddress=Net_Address from master..sysprocesses where spid=@@SPID
Select @sNtUserName=Nt_UserName From master..sysprocesses where spid=@@SPID  Insert into Mkt_OdrHisModi (BatchNo,ChangeNo,FieldName,ForwardContent,ModiContent,CreateBy,CreateDate,HostName,ProgName,NetAddress,sSQLCmd)
  Select BatchNo,ChangeNo,@sFieldName,@fForwardPrice,@fModiPrice,@sNTUserName,GetDate(),Host_Name(),App_Name(),@sNetAddress,@sSQLCmd From Inserted
End  
--此处有两个函数一个为user_Name(),一个为sUser_Name()区别是什么？User_Name()有时能显示系统用户名，dbo，暂进觉得没用。
--当有人在后台修改数据时，ProgName会显示 SQL 查询分析器、管理器一类的程序名，其他程序修改时，不显示程序名，
--使用SQL管理器 修改数据时，时@sNtUserName会显示NT用户的名字。
--netAddress一般都会有数据，有时为空是因为ERP中更新时没写这个数据，但得到这个MAC地址后，要去客户端去查找，因为客户段如果多网卡，这个连接过来的MAC有可能是不正确的。
--sSQLCmd，由于程序的限制，只能读255个字符，足够发现问题时，去系统中查找，是哪个位置使用这个语句，造成出错的了。

Answer 1

有人说，这个当，Qty,price,deliverydate都更改时，会插入三条，应该无论如何，只插入一条比较节约空间与效率，但只插一条的话，那个更改字段怎么办？哪位大侠有好办法吗？ 
------------
三个字段一起插入不就行了吗？
一定要区分哪个字段做了更新的话，可以对比一下Inserted表与Deleted表中的值，不一样或者Deleted表中没有记录，就说明是更改过或者新插入的记录

Answer 2

可以参照以下方法编写各类触发器Create Table LLL(
ID int identity(1,1) not null, 
datetime datetime, 
txt varchar(8000) 
) 
go 
create talbe l(
id int,xx varchar(100),
bb varchar(100)
) 
go 
Create trigger T_L 
on L 
for update 
as 
declare @s varchar(8000) 
set @s='' 
select @s=@s+'xx:'+d.xx+'--->'+i.xx+';' 
from deleted d join inserted i on d.id=i.id and d.xx<>i.xx 
select @s=@s+'bb:'+d.bb+'--->'+i.bb+';' 
from deleted d join inserted i on d.id=i.id and d.bb<>i.bb 
if @s<>'' 
insert lll(datetime,txt) values(getdate(),@s) 
go 
update l set xx='xxx',bb='bbb' where id=7 
select * from lll
ID          datetime                txt
----------- ----------------------- -------------------------------------------------------------------------------------------------------
1           2009-02-13 16:55:14.687 xx:4--->xxx;xx:4--->xxx;xx:4--->xxx;xx:4--->xxx;bb:4--->bbb;bb:4--->bbb;bb:4--->bbb;bb:4--->bbb;========================================
http://www.dbtuning.cn
主营：中小企业数据库管理、优化、调校服务
======================================== 

Answer 3

确实很长,而且不清楚你具体想干什么?如果是想了解触发的工作,参考:触发器综述 
http://topic.csdn.net/u/20081005/11/57061a18-c234-40ee-ba4b-1f4c3bc7f09a.html

Answer 4

我也需要实现表修改时记录其修改的痕迹，jia_guijun的实现方法可以，但如果一个表的字段太多那触发器代码很长，而且要针对每个表写这样的触发器，如果表字段发生变化还要维护这个触发器，能否写一个通用的触发器：根据表名称获取其字段集合，然后循环比较如果值更改了则插入历史表中，本人触发器不会，恳请高手代劳，将另开帖送分，谢谢！

Answer 5

得到服务器的IP地址
create table #ip(id int identity(1,1),re varchar(200))
declare @s varchar(1000)
set @s='ping '+left(@@servername,charindex('\',@@servername+'\')-1)+' -a -n 1 -l 1'
insert #ip(re) exec master..xp_cmdshell @s
select 服务器名=@@servername,IP地址=stuff(left(re,charindex(']',re)-1),1,charindex('[',re),'')
from #ip
where id=2drop table #ip
1，exec master..xp_cmdshell'osql -L'
2，或者
declare @cmdStr varchar(100)
create table #table(id int identity,txt varchar(1000))
create table #t(hostname varchar(100), ip varchar(100))
declare @id int
declare @hostname varchar(100)
set @id = 50
set nocount on
while exists (select top 1 HostName from master..sysprocesses where spid > @id)
begin
    select top 1 @cmdStr='ping '+HostName, @id = spid, @hostname = hostname from master..sysprocesses where spid > @id order by spid 
    insert into #table(txt) exec master..xp_cmdshell @cmdStr
    insert #T select @hostname, substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2
    truncate table #table
end
set nocount off
select distinct * from #t
drop table #table
drop table #t

Answer 6

ping 这个试过了。使用第三方程序的时候，host_Name获得不了，更不用说ping出IP了，奇怪的是mac地址是一定能获得的，为什么不同获得IP。不过还是谢谢。

Answer 7

Create Table LLL(
    ID int identity(1,1) not null, 
    datetime datetime, 
    txt varchar(8000) 
) 
go 
create talbe l(
    id int,xx varchar(100),
    bb varchar(100)
) 
go 
Create trigger T_L 
    on L 
    for update 
as 
    declare @s varchar(8000) 
    set @s='' 
    select @s=@s+'xx:'+d.xx+'--->'+i.xx+';' 
        from deleted d join inserted i on d.id=i.id and d.xx<>i.xx 
    select @s=@s+'bb:'+d.bb+'--->'+i.bb+';' 
        from deleted d join inserted i on d.id=i.id and d.bb<>i.bb 
    if @s<>'' 
        insert lll(datetime,txt) values(getdate(),@s) 
go 
update l set xx='xxx',bb='bbb' where id=7 
select * from lll