求一个触发器的写法？
表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