onyinyang onyinyang - 4 months ago 12
SQL Question

mysql trigger to auto complete variable dates

I am wondering if it is possible to make a mysql trigger in 5.1 to autocomplete a date field with a variable number of days once an initialization date is entered .

Something like this: MySQL add days to a date but with the number of days matching the corresponding field instead of a static number.

Here is an example of what I mean:

Example table

Ideally I would like a field "return date" in "Checked out" to autocomplete based on the date a booked is checked out and the type of rental it is. So, for example, in the case of ISBN 123, the return date should be July 2nd.

Answer

Updating a 'new' value inside a trigger must be done BEFORE an insert or the query will continue calling itself and crash.

The proper syntax for declaring a variable is also included below.

DELIMITER $$ CREATE TRIGGER Rental_INSERT BEFORE INSERT ON Rental FOR EACH ROW BEGIN DECLARE v INTEGER; SET @v := (SELECT val (in days) FROM Rental_Days WHERE Rental_Days.attribute = NEW.attribute); UPDATE Rental SET New.return_date = FROM_DAYS(TO_DAYS(New.borrowed_date) + v); END; $$

Comments