--没有测试,不知有没有错... CREATE Trigger trgUpdRy ON dbo.P_archives FOR UPDATE AS declare @o_cardID int, @o_depart varchar(14), @o_duty varchar(20) , @o_basicMonthlyPay float, @cardID int, @depart varchar(14), @duty varchar(20) , @basicMonthlyPay floatif update(cardID) or update(depart) or update(duty) or update(basicMonthlyPay)begin --取出原值 select @o_cardID=d.cardID,@o_depart=d.depart,@o_duty=d.duty,@o_basicMonthlyPay=d.basicMonthlyPay from deleted d --取出更新后的值 select @cardID=i.cardID,@depart=i.depart,@duty=i.duty,@basicMonthlyPay=i.basicMonthlyPay from inserted i--insert insert personBG values(@cardID,getdate(), @o_cardID,@o_depart,@o_duty,@o_basicMonthlyPay, @cardID,@depart,@duty,@basicMonthlyPay, '说明') end
create trigger tr_p_archives_upd on p_archives for update as declare @count declare @cardID varchar(10) declare @depart varchar(10) declare @duty varchar(10) declare basicMonthlyPay varchar(10) select @count = count(1) from inserted if @count > 1 begin select @errmsg = '一次只能写一条数据,不允许批操作!!!' RAISERROR ('90001', 16, 1, @errmsg) end else begin if update(cardID) select @cardID = cardID from inserted if update(depart) select @depart = depart from inserted if update(duty) select @duty = duty from inserted if update(basicMonthlyPay) select @basicMonthlyPay = basicMonthlyPay from inserted
--没有更新值为NULL insert into personBG (cardID, changeTime, YcardID, Ydepart, Yduty, YbasicMonthlyPay, XcardID, Xdepart, Xduty, XbasicMonthlyPay) select @cardID, getdate(), cardID, depart,duty, basicMonthlyPay, @cardID, @depart, @duty, @basicMonthlyPay from deleted end
create trigger updateit on P_archives for update asif update(cardID) or update(depart) or update(duty) or update(basicMonthlyPay) insert into personBG select L.cardid,getdate() as changeTime,L.cardid as Ycardid ,L.depart as Ydepart,L.duty as Yduty,L.basicMonthlyPay as Ypay ,R.cardid as Xcardid ,R.depart as Xdepart,R.duty as Xduty,R.basicMonthlyPay as Xpay ,R.memo1 from P_archives L join inserted R on L.name1=R.name1
太高兴了,outwindows(窗外)的基本上实现了我的要求,我再试试 weixy() 的
--我建议这样写create trigger tr_process on P_archives for update as if update(cardID) or update(depart) or update(duty) or update(basicMonthlyPay) begin select id=identity(int,1,1),cardID,depart,duty,basicMonthlyPay into #i from inserted select id=identity(int,1,1),cardID,depart,duty,basicMonthlyPay into #d from deleted insert personBG(cardID,changeTime ,YcardID,Ydepart,Yduty,Ypay ,XcardID,Xdepart,Xduty,Xpay ,memo1) select i.cardID,getdate() ,d.cardID,d.depart,d.duty,d.basicMonthlyPay ,i.cardID,i.depart,i.duty,i.basicMonthlyPay ,'这里写你的变动说明' from #i i join #d d on i.id=d.id end
CREATE Trigger trgUpdRy ON dbo.P_archives
FOR UPDATE
AS
declare @o_cardID int,
@o_depart varchar(14),
@o_duty varchar(20) ,
@o_basicMonthlyPay float,
@cardID int,
@depart varchar(14),
@duty varchar(20) ,
@basicMonthlyPay floatif update(cardID) or update(depart) or update(duty) or update(basicMonthlyPay)begin
--取出原值
select @o_cardID=d.cardID,@o_depart=d.depart,@o_duty=d.duty,@o_basicMonthlyPay=d.basicMonthlyPay
from deleted d
--取出更新后的值
select @cardID=i.cardID,@depart=i.depart,@duty=i.duty,@basicMonthlyPay=i.basicMonthlyPay
from inserted i--insert
insert personBG values(@cardID,getdate(),
@o_cardID,@o_depart,@o_duty,@o_basicMonthlyPay,
@cardID,@depart,@duty,@basicMonthlyPay,
'说明') end
for update
as
declare @count
declare @cardID varchar(10)
declare @depart varchar(10)
declare @duty varchar(10)
declare basicMonthlyPay varchar(10) select @count = count(1) from inserted
if @count > 1
begin
select @errmsg = '一次只能写一条数据,不允许批操作!!!'
RAISERROR ('90001', 16, 1, @errmsg)
end
else
begin
if update(cardID)
select @cardID = cardID from inserted
if update(depart)
select @depart = depart from inserted
if update(duty)
select @duty = duty from inserted
if update(basicMonthlyPay)
select @basicMonthlyPay = basicMonthlyPay from inserted
--没有更新值为NULL
insert into personBG (cardID, changeTime, YcardID, Ydepart, Yduty, YbasicMonthlyPay, XcardID, Xdepart, Xduty, XbasicMonthlyPay)
select @cardID, getdate(), cardID, depart,duty, basicMonthlyPay, @cardID, @depart, @duty, @basicMonthlyPay
from deleted
end
for update
asif update(cardID) or update(depart) or update(duty) or update(basicMonthlyPay)
insert into personBG
select L.cardid,getdate() as changeTime,L.cardid as Ycardid
,L.depart as Ydepart,L.duty as Yduty,L.basicMonthlyPay as Ypay
,R.cardid as Xcardid
,R.depart as Xdepart,R.duty as Xduty,R.basicMonthlyPay as Xpay
,R.memo1
from P_archives L join inserted R
on L.name1=R.name1
weixy() 的
for update
as
if update(cardID) or update(depart) or update(duty) or update(basicMonthlyPay)
begin
select id=identity(int,1,1),cardID,depart,duty,basicMonthlyPay
into #i from inserted
select id=identity(int,1,1),cardID,depart,duty,basicMonthlyPay
into #d from deleted insert personBG(cardID,changeTime
,YcardID,Ydepart,Yduty,Ypay
,XcardID,Xdepart,Xduty,Xpay
,memo1)
select i.cardID,getdate()
,d.cardID,d.depart,d.duty,d.basicMonthlyPay
,i.cardID,i.depart,i.duty,i.basicMonthlyPay
,'这里写你的变动说明'
from #i i join #d d on i.id=d.id
end
不行,方法太复杂了,也没通过,再试
pisces007(蝶鱼)的