2

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.

  • Have you checked to see what your select statement returns when it's run by itself(not in the trigger)? – Stephan Mar 25 '15 at 16:33
  • 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? – Giovanni Mar 25 '15 at 22:11
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

  • Try to update your question, instead answering it to thank people. – Racer SQL Oct 21 '15 at 11:37
1

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)).

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.