MySQL before trigger

Question

I got this two tables:

   CREATE TABLE `reservation` (
   `id_reservation` int(11) PRIMARY KEY,
   `reserved_date` date NOT NULL,
   `id_event` int(11) NOT NULL,   
   FOREIGN KEY (`id_event`) REFERENCES `event` 
   )

   CREATE TABLE `event` (
   `id_event` int(11) PRIMARY KEY,
   `begin_date` date NOT NULL,
   `end_date` date NOT NULL,   
   )

Basically I insert a reservation that has a single reserved_date. The reservation is related to the event, that has a begin_date and an end_date.

I need to check that reserved_date is included (y <= x <= z) in the begin_date and end_date of that particular event.

score 0 · Accepted Answer · answered Jan 13 '12 at 13:44

0

Try this trigger -

CREATE TRIGGER trigger1
  BEFORE INSERT
  ON reservation
  FOR EACH ROW
BEGIN
  DECLARE id_event_var INT DEFAULT NULL;
  SELECT id_event INTO id_event_var FROM `event` WHERE id_event = new.id_event AND new.reserved_date >= begin_date AND new.reserved_date <= end_date;
  IF id_event_var IS NULL THEN
    ... -- wrong date!
  END IF;
END

answered Jan 13 '12 at 13:44

Devart

119,203
23
166
186

It returns error #1064 on line 6. What kind of statement do I have to write after "IF id_event_var IS NULL THEN"? – Jan 14 '12 at 13:26
Write your code here. For example you may throw an error, e.g.: 'CALL unlnown_function();' – Devart Jan 16 '12 at 08:42
Ok, let's think I want to throw a feedback for that error, for example i want to print out: "Sorry, you cannot". How can i do it? – Jan 18 '12 at 17:18
In general, how do I throw error in order to stop the INSERT query? – Jan 18 '12 at 17:19
Call unknown stored procedure or try this for MySQL 5.5 - http://stackoverflow.com/questions/8913999/custom-errors-in-mysql-trigger/8914094#8914094 – Devart Jan 19 '12 at 07:37

MySQL before trigger

1 Answers1