mysql - Column value of first table needs to be update automatically whenever the second table row is updated or added -
column value of first table needs update automatically whenever second table row updated or added.
i have 2 table ccncsr , csrtimereporting
mysql> select * ccncsr; select * 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 rows in set (0.00 sec)
+-----------+-------+------------------+--------------+-------+ | 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 rows in set (0.00 sec)
where in table ccncsr there column "totaltimereported", column value needs update automatically whenever second table column "timereported" updated or new row added.
how can this?
this how can it
create trigger trigger_name on csrtimereporting insert begin declare @time_reported decimal, @csr_number nvarchar(50) -- select data type need select @csr_number = csrnumber , @time_reported = timereporteddate csrtimereporting update ccncsr set totaltimereported = @time_reported csrnumber = @csr_number
Comments
Post a Comment