0

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 ;
7
  • 1
    Is Status a column in the table customers? Mar 13, 2019 at 14:02
  • Yes its in customers Mar 13, 2019 at 14:10
  • You may want to use a BEFORE trigger instead. What's the purpose of the ENUM Status? Mar 13, 2019 at 14:43
  • Before update? Yeah i just realized it probably would be better to have varchar. Purpose of enum was dropdown Mar 13, 2019 at 14:45
  • I'm confused, you can't interact with the trigger. What happens happens Mar 13, 2019 at 14:47

1 Answer 1

1

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?

2
  • Thats exactly what I was missing.why are you using BEFORE UPDATE ON and what does CASE indicate? Thanks for help , I can read and understand now Mar 13, 2019 at 15:22
  • BEFORE fires before the row is updated, so you can modify the "NEW" row before it hits the table. CASE is an expression that is somewhat similar to the ternary operator ?: in languages like C or Java. I.e. status = NEW.valid_licence = 'Yes' ? 'Approved' : 'Unapproved' Mar 13, 2019 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.