MySQL Forums
Forum List  »  Triggers

Trigger syntax - stop duplicates
Posted by: Gavin McKay
Date: April 10, 2017 01:04AM

I've looked at a couple of other posts but being a newbee just cannot get this to work! I am using the following trigger to fill a table 'bookings_report' with data using the trigger below. Each time it runs it enters all records from the bookings table using joins to give the data that I need. This works ok but after each insert on the bookings table, every record is inserted into the bookings_report not just the latest insert. I understand why and think I need to either use 'INSERT IGNORE' or a 'WHERE' condition to somehow only put the latest record into the 'bookings_report' table but I can't work out the best approach. Any Help would be appreciated!


CREATE TRIGGER bookings_report_insert
AFTER INSERT ON bookings
FOR EACH ROW
BEGIN
INSERT INTO bookings_report (report_id, booking_id, date_ammended, action, firstname, lastname, booking_date, period, resource, booking_notes)
SELECT NULL, bookings.booking_id, NOW(), "INSERT", users.firstname, users.lastname, bookings.date, periods.name, rooms.name, bookings.notes
FROM((( bookings
JOIN users ON bookings.user_id = users.user_id)
JOIN periods ON periods.period_id = bookings.period_id)
JOIN rooms ON rooms.room_id = bookings.room_id);
END

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger syntax - stop duplicates
1316
April 10, 2017 01:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.