First, create a view v_tishi, count the license plate for each car and the total number of bus: establish a table: tishi, used to store the car license plate and driving kilometers, then, by comparing v_tishi and tishi The number of kilometers depends on the result of the prompt.
Some important SQL statements used in the program:
- calculate the difference between the select tishi.car_chepai, V_tishi.sumlc-tishi.ts_gongli as chaefrom V_tishi inner join tishi on V_tishi.car_chepai = tishi.car_chepaiwhere V_tishi.sumlc-tishi.ts_gongli> = 6000 --- oil change update tishi set tishi. ts_gongli = V_tishi.sumlcfrom tishi inner join V_tishi on tishi.car_chepai = V_tishi.car_chepai --- flip-flop, the corresponding number of kilometers delete CREATE tRIGGER tri_tishi ON [dbo]. [shiyongjl] FOR dELETE ASdeclare @chepai varchar (50), @gongli float, @tsgongli floatselect @ chepai = car_chepai, @ gongli = sy_licheng from deletedselect @ tsgongli = ts_gongli from tishi where car_chepai = @ chepaiif @@ rowcount = 0returnelseupdate tishi set ts_gongli = @ tsgongli- @ gongli where car_chepai = @ chepai
