MySQL triggers

The below are examples of 2 triggers I set up on a database recently.

The first one was to evaluate a condition on inserting a new record, while the second one was to evaluate the same condition but this time on update.


Basically, I had several columns but this only concerned three of them, two of them were time stamps and one was a boolean (actually it was a tiny int, but we won't worry about this here)

We had fault_occured, fault_resolved and resolved.

The first trigger;

DELIMITER $$
CREATE TRIGGER before_insert

BEFORE INSERT

ON log FOR EACH ROW

BEGIN

IF NEW.fault_resolved>NEW.fault_occured THEN

SET NEW.resolved=1;

END IF;

END$$

DELIMITER ;

The second trigger;
DELIMITER $$

CREATE TRIGGER before_update

BEFORE UPDATE

ON log FOR EACH ROW

BEGIN

IF NEW.fault_resolved>OLD.fault_occured THEN

SET NEW.resolved=1;

END IF;

END$$

DELIMITER ;

I wasn't so sure about the OLD.fault_occured in the second one but it works when tested.


A few notes;

The table was log
We can see all triggers with;

SHOW TRIGGERS;

We can drop a trigger with;

DROP TRIGGER trigger_name;

I believe the above before_insert trigger can also be achieved with the following being run every time after an insert;

UPDATE log
SET resolved='1'WHERE (id=(SELECT MAX(id)) and fault_resolved > fault_occured);

Not so relevant, but here for completeness, the below is a construct of the table and al the sql to test the above triggers;

This tests that nothing happens to resolved when an insert is made with no fault_resolved is added;

INSERT INTO log (fault_occured, machine, module, fault, responsibility, username, details)VALUE ('2020-03-07 09:45','D','Sabre','Other','Engineering','hnisbet','');
This tests that resolved is properly set to 1 when an insert is made with a fault_resolved which is greater then the fault_occured;


INSERT INTO log (fault_occured, machine, module, fault, responsibility, username, details,fault_resolved)VALUE ('2020-03-07 09:10','D','Sabre','Other','Engineering','hnisbet','','2020-03-07 09:11');

The below is the sql to create the table that all of the above referred to;

CREATE TABLE log
(
    id             int AUTO_INCREMENT,
    fault_occured  timestamp  DEFAULT '0000-00-00 00:00:00' NOT NULL,
    machine        varchar(6)                               NOT NULL,
    module         varchar(20)                              NOT NULL,
    fault          varchar(50)                              NOT NULL,
    responsibility varchar(20)                              NOT NULL,
    resolved       tinyint(1) DEFAULT 0                     NOT NULL,
    username       varchar(20)                              NOT NULL,
    details        varchar(250)                             NULL,
    fault_resolved timestamp  DEFAULT '0000-00-00 00:00:00' NULL,
    CONSTRAINT log_id_uindex
        UNIQUE (id),
    CONSTRAINT log_module_fk
        FOREIGN KEY (module) REFERENCES modules (module)
            ON UPDATE CASCADE,
    CONSTRAINT log_users_username_fk
        FOREIGN KEY (username) REFERENCES users (username)
);

ALTER TABLE log
    ADD PRIMARY KEY (id);

NOTE:

The line fault_occured  timestamp  DEFAULT '0000-00-00 00:00:00' NOT NULL had to be manually changed as an after thought, as it originally was set to  NOT NULL and I guess as a result of this; DEFAULT was set to current timestamp, which was fine until I added the triggers, where I found on any update this would be changed to the current time, which was of course no good here.