求一个触发器的写法？
表househistoryprice的字段：
newcode(nvarchar)
historyprice(int)
ptime(datetime)
结果如下：
newcode historyprice ptime
2007417170403 3700 2007-08-09
另一个表dghouseprice字段,括号为字段类型：
newcode(nvarchar)
iPriceAverage(int)
bjrqyear(nvarchar)
bjrqmonth(nvarchar)
bjrqdate(nvarchar)
当我在表househistoryprice插入一条数据(两个表通过newcode关联),就将表dghouseprice中与之对应的值更新一下
结果如下：
newcode iPriceAverage bjrqyear bjrqmonth bjrqdate
2007417170403 3700 2007 08 09
这样的触发器怎么写？谢谢
[解决办法]
create trigger test
on househistoryprice
for inserted
as
insert into dghouseprice
select newcode,historyprice,year( ptime) as bjrqyear, month(ptime) as bjrqmonth, day(ptime) as bjrqdate from inserted
[解决办法]
create trigger tr_househistoryprice on househistoryprice
for insert
as
begin
update d
set d.bjrqyear = year(i.ptime),d.bjrqmonth = month(i.ptime)
,d.bjrqdate = day(i.ptime)
from dghouseprice d
join inserted i on i.newcode = d.newcode
end
go
[解决办法]
沒辦法用一個觸發器，要用兩個觸發器。
你這是在兩個不同表上操作觸發的動作。
Create Trigger TR_Insert_dghouseprice On househistoryprice
After Insert
As
Begin
--存在，就更新
Update A
Set iPriceAverage = B.historyprice, bjrqyear = Year(ptime), bjrqmonth = Right(100 + Month(ptime), 2), bjrqdate = Right(100 + Day(ptime), 2)
From dghouseprice A Inner Join Inserted B
On A.newcode =B.newcode
--如果不存在，插入
Insert dghouseprice Select newcode, historyprice, Year(ptime), Right(100 + Month(ptime), 2), Right(100 + Day(ptime), 2)
From Inserted A
Where Not Exists(Select newcode From dghouseprice Where newcode = A.newcode)
End
GO
Create Trigger TR_Insert_househistoryprice ON dghouseprice
After Insert
As
Insert househistoryprice Select newcode, iPriceAverage, bjrqyear + '- ' + bjrqmonth + '- ' + bjrqdate From Inserted Where iPriceAverage != 0
GO
