Mysql – Does this trigger make sense

MySQL

I've written a trigger for my database which is supposed to conduct the following business rule: if a customer doesn't have a valid driving licence, they are not allowed to rent a car. They are allowed to come back with a valid one (obviously).

Does this make sense? I have a column called status, but the receptionist will have to fill out the status anyway? Also, would you recommend doing a trigger for age separately? Thanks.

DELIMITER //

CREATE TRIGGER check_licence
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN 
DECLARE Status ENUM('Approved','Unapproved');
IF NEW.valid_licence = 'Yes' THEN
SET Status='Approved';
ELSE 
SET Status='Unapproved';
END IF;
END //

DELIMITER ;

Best Answer

I'll create a small example, that you perhaps can use:

create table customers 
( customer_no int not null primary key
, valid_licence char(3) default 'No' not null
, status varchar(10) default 'Unapproved' not null
);

insert into customers (customer_no) values (1),(2),(3);

So now we have 3 customers:

select * from customers;
+-------------+---------------+------------+
| customer_no | valid_licence | status     |
+-------------+---------------+------------+
|           1 | No            | Unapproved |
|           2 | No            | Unapproved |
|           3 | No            | Unapproved |
+-------------+---------------+------------+
3 rows in set (0.000 sec)

If I get it right your trigger should look like:

delimiter //
create trigger check_licence 
BEFORE UPDATE ON customers                 
FOR EACH ROW BEGIN 
    SET NEW.Status = CASE WHEN NEW.valid_licence = 'Yes' 
                          THEN 'Approved'
                          ELSE 'Unapproved'
                     END;
END //
delimiter ;

Test:

update customers set valid_licence = 'Yes' where customer_no = 2;

select * from customers;
+-------------+---------------+------------+
| customer_no | valid_licence | status     |
+-------------+---------------+------------+
|           1 | No            | Unapproved |
|           2 | Yes           | Approved   |
|           3 | No            | Unapproved |
+-------------+---------------+------------+
3 rows in set (0.000 sec)

As for your question "Also, would you recommend doing a trigger for age separately?". First of all, don't use age, it will change as time passes by. Use birth_date and calculate the age. Not sure what you intend to do there though, can you clarify?

Related Question