mysql after insert trigger which updates another table's column

Question

i'm trying to write a trigger, I have following tables: BookingRequest:

  +-----------+---------+------+-----+---------+----------------+
    | Field     | Type    | Null | Key | Default | Extra          |
    +-----------+---------+------+-----+---------+----------------+
    | idRequest | int(11) | NO   | PRI | NULL    | auto_increment |
    | roomClass | int(11) | NO   |     | NULL    |                |
    | inDate    | date    | NO   |     | NULL    |                |
    | outDate   | date    | NO   |     | NULL    |                |
    | numOfBeds | int(11) | NO   |     | NULL    |                |
    | status    | int(11) | NO   | MUL | NULL    |                |
    | idUser    | int(11) | NO   | MUL | NULL    |                |
    +-----------+---------+------+-----+---------+----------------+

status table:

+------------+--------------------------------------------------+------+-----+---------+-------+
| Field      | Type                                             | Null | Key | Default | Extra |
+------------+--------------------------------------------------+------+-----+---------+-------+
| idStatus   | int(11)                                          | NO   | PRI | NULL    |       |
| nameStatus | enum('underConsideration','approved','rejected') | YES  |     | NULL    |       |
+------------+--------------------------------------------------+------+-----+---------+-------+

OccupiedRoom:

+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| idOccupation | int(11) | NO   | PRI | NULL    | auto_increment |
| idRoom       | int(11) | NO   |     | NULL    |                |
| idRequest    | int(11) | NO   |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+

i need a trigger which will change status in BookingReques to 1 if request with the same id is inserted into OccupiedRoom table, so i tried something like this

create trigger occupy_trig after insert on OccupiedRoom 
for each row
begin
  if BookingRequest.idRequest= NEW.idRequest
   then
       update BookingRequest
       set status = '1';
       where idRequest = NEW.idRequest;

    end if;
END;

and it doesn't work, so any suggestions would be very appriciated

removing the semi-colon after `set status ='1'` should do it — mhafellner, Jun 03 '13 at 08:06

score 47 · Accepted Answer · edited Jun 11 '15 at 11:22

47

Try this:

DELIMITER $$
CREATE TRIGGER occupy_trig
AFTER INSERT ON `OccupiedRoom` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       SELECT 1
       INTO @id_exists
       FROM BookingRequest
       WHERE BookingRequest.idRequest= NEW.idRequest;

       IF @id_exists = 1
       THEN
           UPDATE BookingRequest
           SET status = '1'
           WHERE idRequest = NEW.idRequest;
        END IF;
END;
$$
DELIMITER ;

edited Jun 11 '15 at 11:22

Tomasz Kowalczyk

10,472
6
52
68

answered Jun 03 '13 at 07:57

user4035

22,508
11
59
94

This answer is the best answer I've ever found on Stack Overflow for my problems. Thank you. – Will Lacey Dec 09 '19 at 20:13

score 45 · Answer 2 · answered Jun 03 '13 at 08:08

45

With your requirements you don't need BEGIN END and IF with unnecessary SELECT in your trigger. So you can simplify it to this

CREATE TRIGGER occupy_trig AFTER INSERT ON occupiedroom 
FOR EACH ROW
  UPDATE BookingRequest
     SET status = 1
   WHERE idRequest = NEW.idRequest;

answered Jun 03 '13 at 08:08

peterm

91,357
15
148
157

2

@DeadKennedy Why don't you make it as the accepted answer? It'll help others to look at this answer. – Prashanth Dec 05 '17 at 11:12
1

Yup, this should be the accepted answer, in my opinion. – Luis Milanese Dec 14 '17 at 19:24
I have a syntax error when I try using this code on the WHERE line: `drop trigger if exists actualizaDataApto; CREATE TRIGGER actualizaDataApto AFTER INSERT ON doacao FOR EACH ROW UPDATE doador SET Data_Apto = DATE_ADD(Data_Apto, INTERVAL 3 MONTH); WHERE doador.id_Doador = NEW.Doador_id_Doador;` Could you please help me? – C. Rib Nov 21 '18 at 19:36
@C.Rib Comments section is not a good place for this. Please post a proper question and I or other SO members will be happy to help you. – peterm Nov 21 '18 at 19:52

mhafellner · Answer 3 · 2013-06-03T07:59:38.057

3

Maybe remove the semi-colon after set because now the where statement doesn't belong to the update statement. Also the idRequest could be a problem, better write BookingRequest.idRequest

edited Jun 03 '13 at 07:59

answered Jun 03 '13 at 07:49

mhafellner

458
3
9

You can't change it's value in `AFTER` trigger. But you can still use it to update another table. – user4035 Jun 03 '13 at 08:01

score -3 · Answer 4 · answered Jul 22 '15 at 13:42

DELIMITER //

CREATE TRIGGER contacts_after_insert
AFTER INSERT
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the INSERT into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
   INSERT INTO contacts_audit
   ( contact_id,
     deleted_date,
     deleted_by)
   VALUES
   ( NEW.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;

mysql after insert trigger which updates another table's column

4 Answers4

Linked