求一触发器...谢谢
A表如下:
ID Month Event
PC123 1 sss
PC132 1 aaa
PC131 2 CCC
PC123 2 SSS
PC123 2 aaa
----------------------------------
B表 "列 "如下:
ID 1 2
PC123 1 2
PC132 1 0
PC131 0 1
要求如下:当在A表插入数据时
1.如果B表 "ID "列没有A表插入的ID值,将该ID值插入,并在该B表的对应的月份列加1.
2.如果B表 "ID "列已经存在A表插入的ID值,在该B表的对应的月份里统计该ID在该月的出现次数.
[解决办法]
表B的列是固定的嗎？
--建立測試環境
Create Table A
(IDVarchar(10),
[Month]Int,
EventVarchar(20))
Create table B
(IDVarchar(10),
[1]Int,
[2]Int)
GO
--建立觸發器
Create Trigger Update_B On A
For Insert
As
Begin
Update B Set [1] = B.[1] + A.[1], [2] = B.[2] + A.[2] From B Inner Join
(Select ID, SUM(Case [Month] When 1 Then 1 Else 0 End) As [1], SUM(Case [Month] When 2 Then 1 Else 0 End) As [2] From Inserted Group By ID) A
On A.ID = B.ID
Insert B Select ID, SUM(Case [Month] When 1 Then 1 Else 0 End) As [1], SUM(Case [Month] When 2 Then 1 Else 0 End) As [2] From Inserted
Where ID Not In (Select ID From B) Group By ID
End
GO
--測試
Insert A Select 'PC123 ', 1, 'sss '
Union All Select 'PC132 ', 1, 'aaa '
Union All Select 'PC131 ', 2, 'CCC '
Union All Select 'PC123 ', 2, 'SSS '
Insert A Select 'PC123 ', 2, 'aaa '
Select * From B
GO
--刪除測試環境
Drop Table A, B
--結果
/*
ID12
PC12312
PC13101
PC13210
*/
