1

I am new to MySQL triggers. i want to insert the newly inserted booking from booking table to bookings_clone table.

PROBLEM:

All the field in bookings table are filled but in bookings_clone only these fields are getting inserted (id, uniqueid, branch_id, user_id, status, sms_status, email_status, created_at, updated_at) and rest of them are null. Any help in pointing me to right direction is highly appreciated. The table structure of bookings_clone is same as bookings.

CREATE TRIGGER:

CREATE TRIGGER after_booking_insert
AFTER INSERT ON bookings
FOR EACH ROW
INSERT INTO bookings_clone
(id, unique_id, branch_id, pos_user_id, user_id,
 user_address_id, address, postcode, city, town,
 status, order_due_date, order_due_time,
 checkout_mode, sms_status, email_status, notes,
 time_taken, accepted_at, delivery_charges,
 discount_amount, total_charges, sub_total_charges,
 initiator, created_at, updated_at, deleted_at)
 VALUES
 (NEW.id, NEW.unique_id, NEW.branch_id, NEW.pos_user_id, NEW.user_id,
 NEW.user_address_id, NEW.address, NEW.postcode, NEW.city, NEW.town,
 NEW.status, NEW.order_due_date, NEW.order_due_time,
 NEW.checkout_mode, NEW.sms_status, NEW.email_status, NEW.notes,
 NEW.time_taken, NEW.accepted_at, NEW.delivery_charges,
 NEW.discount_amount, NEW.total_charges, NEW.sub_total_charges,
 NEW.initiator, NEW.created_at, NEW.updated_at, NEW.deleted_at);

I've also tried this but the result is same

DELIMITER //
CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings
FOR EACH ROW BEGIN  
    INSERT INTO bookings_clone select * from bookings where id = NEW.id;
END;//
DELIMITER ;

Bookings Table Structure:

enter image description here

3
  • You are trying to produce NULLs? Or avoid NULLs? Please provide an INSERT that demonstrates the problem. – Rick James Dec 10 '20 at 20:08
  • I'm trying to copy the last inserted row from bookings to bookings_clone – Wcan Dec 10 '20 at 21:04
  • only these fields data is inserted into bookings_clone (id, uniqueid, branch_id, user_id, status, sms_status, email_status, created_at, updated_at) I even switched from mysql 5.7 to mariadb 10.4 but the result is same. i cannot seem to firgure out why – Wcan Dec 10 '20 at 21:06
2

I can't reprocuce your behaviour.

CREATE TABLE bookings(ID int AUTO_INCREMENT PRIMARY KEY,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id  int)
CREATE TABLE bookings_clone(ID int ,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id  int)
CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings
FOR EACH ROW BEGIN  
    INSERT INTO bookings_clone select * from bookings where id = NEW.id;
END;
INSERT INTO bookings  VALUES(NULL,13,13,13,13,13)
SELECT * FROM bookings_clone
ID | unique_id | branch_id | pos_user_id | user_id | user_address_id
-: | --------: | --------: | ----------: | ------: | --------------:
 1 |        13 |        13 |          13 |      13 |              13

db<>fiddle here

So as long as you don't have a [mre] without images, you should check if the tables arethe same.

4
  • I checked out dbfiddle, when i changed the DB to MySQL 5.7 it does not work. – Wcan Dec 10 '20 at 18:39
  • @Wcan, nothing seems to work for 5.7 at db<>fiddle at the moment, it works with 5.6 – Lennart Dec 10 '20 at 19:23
  • @Wcan here is another fiddle db-fiddle.com/f/3woAVSw5hrav15jAmuWVdT/7 – nbk Dec 10 '20 at 20:01
  • The problem was not in trigger, both triggers were working as they were supposed to, some where in code an update statement was called on bookings, the initial insert was inserting exactly the same data which i mentioned above but then the update statement was putting more data in it. – Wcan Dec 11 '20 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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