How to use a trigger to only insert one row

Question

I am doing a third year project using an intel galileo that reads an RFID card, and signs a student into a class, and I am using multiple tables.

I have nearly completed it as all reads are been recorded into a table called read_log. What I want to achieve is when a record is entered into this read_log, set a trigger to just insert the card number, which again is been sent this table read_log.

So what i have so far is :

DROP TRIGGER IF EXISTS `test`;
CREATE DEFINER=`root`@`localhost` TRIGGER `test` AFTER UPDATE ON `readlog` 
FOR EACH ROW
BEGIN 
INSERT into attendence(S_no) 
SELECT s.Stu_no from student s 
JOIN readLog r ON s.Stu_no=r.Cardid WHERE s.Stu_no=r.Cardid; 
END

I can confirm the trigger is working, as once i scan the rfid card over the galileo it inserts into the database, and the trigger triggers but enters the number 29 times, so there is 29 records entered everytime i read.

Can someone provide a tip as to what to look for or how to go about writing a trigger that will only insert one row. I have tried update instead of insert but this does nothing.

• Stephan: Have you checked to see what your select statement returns when it's run by itself(not in the trigger)?
• Giovanni: You could simply ON s.Stu_no=r.Cardid WHERE s.Stu_no=r.Cardid erasing the where clause. What are the primary key of table attendence? Could you show the output of show create table attendence?

Answer 1

Thanks for ll your help, it gave me alot of ideas to try. As i only wanted it to concentrate on the last entry from read_log I took WHERE s.Stu_no=r.Cardid and changed it to WHERE s.Stu_no=(SELECT MAX(id) from read_log) and it now only concentrates on the last entry in read_log.

cheers for the helps lads

• Racer SQL: Try to update your question, instead answering it to thank people.

Answer 2

Instead of INSERT, use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE.

The former (IGNORE) checks UNIQUE key(s) to avoid inserting a second time. The latter (IODKU) will either insert a new row or update an existing row (based on any UNIQUE key(s)).