Mysql/MariaDB Trigger issue and debug

Question

I currently develop a token reader (RFID) service for, belive it or not, my own pleasure and knowledge. I speak french better than english, so please appologies. Google translate is my friend, but...

My project is really simple:

RFID Reader buyed on Wish
An HTML webpage receiving the RFID code read
An Ajax Query making a SELECT on my table t_rfids codes to retrieve the attached user
IF the_users_id IS NOT NULL INSERT t_accesslogs entry.

This is the table structure:

CREATE TABLE test_punch.t_accesslogs (
  id_tokenaccesslog bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  punchtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  id_ref_user bigint(20) UNSIGNED NOT NULL,
  PRIMARY KEY (id_tokenaccesslog)
)
ENGINE = INNODB,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

So by making a simple INSERT INTO t_accesslogs (id_ref_user) VALUES (3); i got my entry for the CURRENT_TIMESTAMP.

Everything work well but I try to optimize the accesslogs into a timecard entry with a date, time_start, time_end. Table structure:

CREATE TABLE test_punch.t_timecards (
  id_timecard bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  id_ref_user bigint(20) UNSIGNED NOT NULL,
  day_date date NOT NULL,
  time_begin time NOT NULL,
  time_end time DEFAULT NULL,
  PRIMARY KEY (id_timecard)
)
ENGINE = INNODB,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

I try to figure out the best way to fillup this tables without using outside codes, so I give a try to the Triggers. There is the trigger code:

CREATE 
DEFINER = 'root'@'localhost' 
TRIGGER test_punch.add_timecard_entry
    AFTER INSERT
    ON test_punch.t_accesslogs
    FOR EACH ROW
    BEGIN
      DECLARE bint_IdTimeCard BIGINT; 
      DECLARE dt_Punch DATE;
      DECLARE time_Start TIME;
      DECLARE time_End TIME;

      SELECT  t_timecards.id_timecard INTO bint_IdTimeCard 
      FROM t_timecards
      WHERE t_timecards.id_ref_user = NEW.id_ref_user 
        AND  day_date = CAST(NEW.punchtime as DATE)
        AND  time_begin IS NOT NULL
        AND  time_end IS NULL;

      IF (bint_IdTimeCard IS NOT NULL) THEN
        UPDATE t_timecards 
        SET t_timecards.time_end = CAST(NEW.punchtime AS TIME) 
        WHERE t_timecards.id_timecard=bint_IdTimeCard
          AND t_timecards.id_ref_user=NEW.id_ref_user;
      ELSE
        INSERT INTO t_timecards (id_ref_user,day_date,time_begin) 
        VALUES (NEW.id_ref_user,
                CAST(NEW.punchtime AS DATE),
                CAST(NEW.punchtime AS TIME));
      END IF;
END

The first thing I'm not sure is the value returned if the SELECT value is Null. it is a real NULL or Empty? Like said earlier, hard to debug even I use dbForge Studio (see Debugging MySQL Triggers)

Actually the code behavior didn't looks like to handle correctly the IF statement. Any idea about what I'm doing wrong?

Any help will be appreciated.

Thank you

Martin

"Actually the code behavior didn't looks like to handle correctly the IF statement. Any idea about what I'm doing wrong?" i don't see you set the bint_IdTimeCard variable anywhere... Edited never mind i readed over SELECT t_timecards.id_timecard INTO bint_IdTimeCard — Raymond Nijland
– Raymond Nijland, Commented Nov 2, 2018 at 15:08
Try using DECLARE bint_IdTimeCard BIGINT UNSIGNED DEFAULT 0 and IF (bint_IdTimeCard > 0) THEN instead .. — Raymond Nijland
– Raymond Nijland, Commented Nov 2, 2018 at 15:14
One thing i forget to mention SELECT t_timecards.id_timecard INTO bint_IdTimeCard FROM t_timecards WHERE t_timecards.id_ref_user = NEW.id_ref_user AND day_date = CAST(NEW.punchtime as DATE) AND time_begin IS NOT NULL AND time_end IS NULL because there can be only one record in a a MySQL variable. — Raymond Nijland
– Raymond Nijland, Commented Nov 2, 2018 at 15:18
Raymond, Seems to work but it's always the field time_end who's updated. Once I've INSERT a new entry in the access_log table having already an entry with the id_ref_user value, the script still update the existent row... — Martin Leduc
– Martin Leduc, Commented Nov 2, 2018 at 20:19

James K. Lowden · Accepted Answer · 2018-11-02 17:35:11Z

0

If I may: don't. Don't use triggers for anything like this. Use triggers for one reason only: to maintain referential integrity. Preferably, don't use them for that either, unless you have to. Rely on DRI instead.

There is no need to adjust your data in this way at time of insert. Doing so will make your application more complicated and less reliable.

Instead, I recommend a view. Write a view that represents your table as you would like to use it. See if that suits your purpose.

If the view performs badly, it might help to persist the other representation. If so, use a stored procedure to do what you want above the level of inserting into the table, and require the application to use the stored procedures. You may find it convenient to use a second table to hold the derived information.

answered Nov 2, 2018 at 17:35

James K. Lowden

7,8551 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2019-01-10 21:46:09Z

Thanks for your comments. I found it easier to use a trigger instead of a stored procedure but I will give a try to the stored procedure. The fact is that my table t_accesslogs will be and remain my "truth" table. In my approach, I didn't plan to add/remove any entry from this tables, only in the t_timecards tables. Probably my logical have to be review.

I try to write a view and it's working well except that this kill the exception, like if a person "pushes" twice by mistake.

Here my view code:

SELECT
    `t_accesslogs`.`id_ref_user` AS `id_user`,
    `t_accesslogs`.`dt_scan`     AS `dt_scan`,
    MIN(`t_accesslogs`.`dt_scan`) AS `dt_scan_min`,
    MAX(`t_accesslogs`.`dt_scan`) AS `dt_scan_max`
FROM `t_accesslogs`
GROUP BY CAST(`t_accesslogs`.`dt_scan` AS DATE)

Like I said, that's work, but I try to automate the begin and the end timestamp. I will try a stored procedure and let you know.

Thanks,

EDIT:

Hummmm weird. Using the article on the debugging, I've found that the problem was the SELECT. I've changed my WHERE condition from:

WHERE t_timecards.id_ref_user = NEW.id_ref_user 
  AND  day_date = CAST(NEW.punchtime AS DATE)
  AND  time_begin IS NOT NULL 
  AND time_end IS NULL;

by

WHERE t_timecards.id_ref_user = NEW.id_ref_user 
  AND  day_date = CAST(NEW.punchtime AS DATE)
  AND  (time_begin IS NOT NULL AND time_end IS NULL);

and that fixes the behavior. I guess I have to read more.

Thanks for your help and I will convert my trigger to a stored procedure. You're right, easier to debug.

Collectives™ on Stack Overflow

Mysql/MariaDB Trigger issue and debug

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related