这个触发器该如何实现？？

[cong323]

表A中有字段 user,num,NO,invest
表B中有字段 num,NO
表C中有字段 NO，rate
表D中有字段 user,all
现要求当B中插入数据时，判断是否与A中的num,NO相同，如果相同就根据C中的NO把对应的rate*invest的结果放到D中的对应user的all中，这个触发器该如何写呢？请高手指点指点。

[saucer]

something like


CREATE TRIGGER B_insert
ON B
FOR INSERT
AS
UPDATE D SET [all] = A.invest * C.rate FROM D
INNER JOIN A ON A.[user] = D.[user]
INNER JOIN C ON C.NO = A.NO
INNER JOIN inserted i ON i.num = A.num AND i.NO = A.NO

[cong323]

如果A中一个user有多个NO,invest，最后在D中的all是前面几个NO的rate*invest的和呢？

[saucer]

something like


CREATE TRIGGER B_insert
ON B
FOR INSERT
AS
UPDATE D SET [all] =
(
SELECT SUM(A.invest * C.rate) FROM A INNER JOIN C ON C.NO = A.NO
WHERE A.[user] = d1.[user]
)
FROM D d1 INNER JOIN A ON A.[user] = d1.[user]
INNER JOIN inserted i ON i.num = A.num AND i.NO = A.NO

[cong323]

谢谢！！

[cong323]

最后我要把结果放到D中的作为一条新的记录？

[saucer]

something like

CREATE TRIGGER B_insert
ON B
FOR INSERT
AS
INSERT INTO D ([user],[all])
SELECT A.[user],SUM(A.invest * C.rate) FROM A INNER JOIN C ON C.NO = A.NO
INNER JOIN inserted i ON i.num = A.num AND i.NO = A.NO
GROUP BY [user]