mysql - 每当更新或添加第二个表行时，第一个表的列值需要自动更新

Question

每当更新或添加第二个表行时，第一个表的列值需要自动更新。

我有两个表CcnCSR和CsrTimeReporting

mysql> select * from CcnCSR;      
 select * from CsrTimeReporting;

+-----------+-----------------------------------------+----------+-------------+-----------+-------------+------------+------------+------------+-------------------+--------+
| CSRNumber | Slogan                                  | Severity | Customer    | Status    | CreatedDate | CcnQDate   | TTS        | IAdate     | TotalTimeReported | Remark |
+-----------+-----------------------------------------+----------+-------------+-----------+-------------+------------+------------+------------+-------------------+--------+
|   2718122 | DIAMETER LINK FLICKS BETWEEN GGSN-CCN38 | High     | IdeaIndia   | NULL      | 2105-03-04  | 2105-03-03 | 2015-03-28 | 2105-03-13 |              NULL | NA     |
|   2718133 | Auto ZR                                 | High     | AirtelIndia | Analysing | 2105-03-20  | 2105-03-23 | 2105-04-10 | NULL       |              NULL | NULL   |
+-----------+-----------------------------------------+----------+-------------+-----------+-------------+------------+------------+------------+-------------------+--------+

一组 2 行(0.00 秒)

+-----------+-------+------------------+--------------+-------+
| CSRNumber | SeqId | TimeReportedDate | TimeReported | Shift |
+-----------+-------+------------------+--------------+-------+
|   2718122 |     1 | 2015-03-15       |            8 | NULL  |
|   2718122 |     2 | 2105-03-03       |          0.5 | NULL  |
|   2718122 |     3 | 2105-03-03       |            3 | NULL  |
|   2718122 |     4 | 2105-03-03       |          4.5 | NULL  |
|   2718122 |     5 | 2105-03-03       |         5.25 | NULL  |
|   2718122 |     6 | 2105-03-05       |         7.25 | NULL  |
|   2718133 |     8 | 2015-03-30       |            2 | NULL  |
|   2718133 |     9 | 2015-03-31       |          2.5 | NULL  |
|   2718133 |    10 | 2015-03-29       |          3.5 | NULL  |
+-----------+-------+------------------+--------------+-------+

一组 9 行(0.00 秒)

表CcnCSR中有“TotalTimeReported”列，每当更新第二个表列“TimeReported”或添加新行时，该列值需要自动更新。

我该怎么做？

Answer 1

这就是你可以做到的

create trigger trigger_name
on CsrTimeReporting
for insert
as
begin
declare 
@time_reported decimal,
@CSR_Number nvarchar(50) -- select data type as you need

select @CSR_Number = CSRNumber , @time_reported = TimeReportedDate from 
CsrTimeReporting              

Update CcnCSR set TotalTimeReported = @time_reported where 
CSRNumber = @CSR_Number