Creating a complex trigger in PHP MyAdmin

Hi I'm a total noob in SQL.

I'm trying to write a trigger to insert into PHP MyAdmin that accomplished the following:

1. Table = 'events', Fields = 'clock_on' (datetime field), 'clock_off' (datetime field) and 'time_clocked' (tinyint(11) field).

2. When 'clock_on' and 'clock_off' are not NULL then calculate the time elapsed and insert this time into the 'time_clocked' field then reset the values in 'clock_on' and 'clock_off' to NULL.

3. When 'clock_on' and 'clock_off' are not NULL then calculate the time elapsed and ADD this to the existing value in 'time_clocked' then reset the values in 'clock_on' and 'clock_off' to NULL.
   <br><br>This should give me a rolling figure on the 'clock_off' field for clocked time.

4. If the day is Mon, Tue, Wed or Thurs and 'clock_off' is NULL and the current time is 11pm then insert the current date and 16:15 into the 'clock_off' and run point #3.

5. If the day is Fiday and 'clock_off' is NULL and the current time is 11pm then insert the current date and 12:15 into the 'clock_off' and run point #3.

Using Google and in the spirit of botching I've come up with the following.... which does not work. Can anyone help me out with a working trigger?

DELIMITER ;
DROP TRIGGER IF EXISTS `calculate_duration`;
DELIMITER $$
CREATE TRIGGER `calculate_duration`
AFTER INSERT
ON `events`
FOR EACH ROW
BEGIN
IF NEW.clock_on IS NOT NULL AND NEW.clock_off IS NOT NULL THEN
  UPDATE events
    SET time_clocked = UNIX_TIMESTAMP(NEW.clock_off) - UNIX_TIMESTAMP(NEW.clock_on)
    WHERE id = NEW.id;
END IF;
IF (
  DAYOFWEEK(CURRENT_DATE()) IN (1, 2, 3, 4)
  AND HOUR(NOW()) = 23
  AND NEW.clock_off IS NULL
) THEN
  INSERT INTO events (clock_on, clock_off)
    VALUES (CURRENT_DATE(), '16:15');
END IF;
IF (
  DAYOFWEEK(CURRENT_DATE()) = 5
  AND HOUR(NOW()) = 23
  AND NEW.clock_off IS NULL
) THEN
  INSERT INTO events (clock_on, clock_off)
    VALUES (CURRENT_DATE(), '12:15');
END IF;
END$$
DELIMITER;

Results of SHOW CREATE TABLE events

127.0.0.1/machines/events/		http://localhost/phpmyadmin/index.php?route=/table/sql&db=machines&table=events
Your SQL query has been executed successfully.

SHOW CREATE TABLE EVENTS;



EVENTS	CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `name` text DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `resource_id` int(11) DEFAULT NULL,
  `color` varchar(200) DEFAULT NULL,
  `join_id` int(11) DEFAULT NULL,
  `has_next` tinyint(1) NOT NULL DEFAULT 0,
  `TIMESTAMP` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `contract` text DEFAULT NULL,
  `part` text NOT NULL,
  `operation` text NOT NULL,
  `jobcard` text NOT NULL,
  `drawing` text DEFAULT NULL,
  `clock_on` datetime DEFAULT NULL,
  `clock_off` datetime DEFAULT NULL,
  `time_clocked` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci	

If the value stored in time_clocked has any real business value, then I would suggest that you store the individual clock_on/clock_off pairs in their own table, so that you maintain the data required to evidence the value in time_clocked.

You could use something like this to maintain the value of time_clocked, as you have described:

DELIMITER $$

CREATE TRIGGER `events_before_update_calculate_duration`
BEFORE UPDATE ON `events` FOR EACH ROW
BEGIN
    IF NEW.clock_on IS NOT NULL AND NEW.clock_off IS NOT NULL THEN
        SET NEW.time_clocked = COALESCE(OLD.time_clocked, 0) + TIMESTAMPDIFF(SECOND, NEW.clock_on, NEW.clock_off);
        SET NEW.clock_on = NULL, NEW.clock_off = NULL;
    END IF;
END$$

DELIMITER ;

Note the use of COALESCE() to deal with your DEFAULT NULL for time_clocked.

And, to deal with clocking off events that are still clocked on at 23:00, you could use an event like:

CREATE EVENT `event_clock_off_daily`
    ON SCHEDULE
      EVERY 1 DAY
      STARTS CURRENT_DATE + INTERVAL '23:00' HOUR_MINUTE
    COMMENT 'Clocks off events'
    DO
      UPDATE machines.events
      SET clock_off = CURRENT_DATE + INTERVAL IF(DAYOFWEEK(CURRENT_DATE) = 6, '12:15', '16:15') HOUR_MINUTE
      WHERE DAYOFWEEK(CURRENT_DATE) IN (2, 3, 4, 5, 6) -- only run Mon - Fri
      AND clock_on >= CURRENT_DATE
      AND clock_off IS NULL;

Note that DAYOFWEEK():

> Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard. Returns NULL if date is NULL.