嘿，每個人都在這里觸發(fā)計算每個客戶的發(fā)票。所以我希望看到應(yīng)付金額取決于他們租車的天數(shù)！

DELIMITER //

CREATE TRIGGER update_booking_price_day

BEFORE update ON bookings

FOR EACH ROW BEGIN

SET NEW.TheDuration = DATEDIFF(NEW.end_date, NEW.start_date);

SET NEW.AMOUNT_DUE = DATEDIFF(NEW.end_date, NEW.start_date); * SELECT rate_per_day FROM vehicles WHERE vehicle_id=NEW.vehicle_id;

END;

//

DELIMITER ;

表結(jié)構(gòu)

CREATE TABLE bookings (

booking_id int(50) NOT NULL AUTO_INCREMENT,

booking_date date NOT NULL,

start_date date NOT NULL,

end_date date NOT NULL,

invoice_no int(10) NOT NULL ,

chauffeur_id int(10) NULL,

vehicle_id int(10) NOT NULL ,

customer_id int(50) NOT NULL ,

chauffeur_req ENUM('Yes','No') NOT NULL,

special_instructions varchar(255) NOT NULL,

PRIMARY KEY (booking_id),

KEY invoice_nofk2 (invoice_no),

KEY chauffeur_idfk1 (chauffeur_id),

KEY customer_idfk2 (customer_id),

KEY vehicle_idfk2 (vehicle_id),

CONSTRAINT invoice_nofk1 FOREIGN KEY (invoice_no) REFERENCES invoice (invoice_no),

CONSTRAINT chauffeur_idfk2 FOREIGN KEY (chauffeur_id) REFERENCES chauffeurs (chauffeur_id),

CONSTRAINT customer_idfk3 FOREIGN KEY (customer_id) REFERENCES customers (customer_id),

CONSTRAINT vehicle_idfk3 FOREIGN KEY (vehicle_id) REFERENCES vehicles (vehicle_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和

ALTER TABLE bookings

ADD COLUMN TheDuration varchar(10) NOT NULL;

ALTER TABLE bookings

ADD COLUMN AMOUNT_DUE varchar(16) NOT NULL;

和

CREATE TABLE vehicles (

vehicle_id int(10) NOT NULL AUTO_INCREMENT,

category ENUM('Sedan','Hatchback','SUV', 'Coupe', 'Crossover') NOT NULL,

no_of_seats int(11) NOT NULL,

brand varchar(20) NOT NULL,

model varchar(20) NOT NULL,

product_year int(5) NOT NULL,

rate_per_day int(11) NOT NULL,

PRIMARY KEY (vehicle_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我收到一個錯誤說......

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* SELECT rate_per_day FROM vehicles WHERE vehicle_id=NEW.vehicle_id;

結(jié)束'在第5行

總結(jié)

以上是生活随笔為你收集整理的mysql倍增表的内容,mysql - DATEDIFF不会在触发器内倍增 - SO中文参考 - www.soinside.com...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。