嘿，每个人都在这里触发计算每个客户的发票。所以我希望看到应付金额取决于他们租车的天数！
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 ;
表结构
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;
结束'在第5行
