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
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
for update
as
declare @cardID int,@depart int,@duty int,@basicMonthlyPay int
if update(cardID) set @cardID=1 else set @cardID=0
if update(depart) set @depart=1 else set @depart=0
if update(duty) set @duty=1 else set @duty=0
if update(basicMonthlyPay) set @basicMonthlyPay=1 else set @basicMonthlyPay=0
if (@cardID+@depart+@duty+@basicMonthlyPay)>0
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
where (@depart=0 or (@depart=1 and isnull(d.depart,'')<>''))
and (@duty=0 or (@duty=1 and isnull(d.duty,'')<>''))
and (@basicMonthlyPay=0 or (@basicMonthlyPay=1 and isnull(d.duty,0)<>0))
end
您在上面写的触发器有一处我看不懂,能详细解释一下吗?
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
不知道写这两个临时表有什么作用?
第二个得到修改前的记录的记录集id=identity(int,1,1)是生成一个记录号,方便在下面更新的时候,将修改前后的记录对应起来
我目前也在做人事,我不太懂你的意思?能否舉例說明? 謝謝!!!解决办法是基本资料表中不要包含这种变动信息,变动信息由用户专门添加。在视图中来生成完整的员工资料
我试了,好像还有点不对.
比如有一个员工的工资原来是空的,我现在加入了,按保存,触发器就执行,出现了这样的错误:将VARCHAR值"财务主管"转换为数据类型为int的列时发生语法错误我想是不是那些int变量的类型定义错了?
for update
as
declare @cardID int,@depart int,@duty int,@basicMonthlyPay int
if update(cardID) set @cardID=1 else set @cardID=0
if update(depart) set @depart=1 else set @depart=0
if update(duty) set @duty=1 else set @duty=0
if update(basicMonthlyPay) set @basicMonthlyPay=1 else set @basicMonthlyPay=0if (@cardID+@depart+@duty+@basicMonthlyPay)>0
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
where (@depart=0 or (@depart=1 and isnull(d.depart,'')<>''))
and (@duty=0 or (@duty=1 and isnull(d.duty,'')<>''))
and (@basicMonthlyPay=0 or (@basicMonthlyPay=1 and isnull(d.basicMonthlyPay,0)<>0))
end