Search code examples
mariadbdatabase-trigger

Trigger doesn't work at all

I have these tables: Attendence(id,start_time as TIME,end_time as TIME) and editattendencerequest(id,attendence_id,new_start_time,new_end_time,status as ENUM('Approved','Rejected','Pending')

Now when an edit request is approved by updating its status I want the database to automatically update the specified attendence's start_time and end_time to new_start_time and new_end_time.

I defined this triger through phpmyadmin: enter image description here

And here is the trigger in code :

IF NEW.status <> OLD.status THEN
    IF NEW.status = 'Approved' THEN
        UPDATE attendence set start_time = NEW.start_time and end_time=NEW.end_time where id = NEW.attendence_id;
    END IF;
END IF

Yet when I insert an attendence, insert an edit request and then update its status to Approved all through phpmyadmin I don't see the attendence updated at all.

So why isn't it working ? and how to make it work ?

My OS is windows 7 and using xampp 7.1.11.

Solution

  • The and word in the SET portion of the UPDATE is the issue. The fields in the SET portion should be separated by commas, try this instead:

    IF NEW.status <> OLD.status THEN
    IF NEW.status = 'Approved' THEN
        UPDATE attendence 
           SET start_time = NEW.start_time, end_time = NEW.end_time
         WHERE id = NEW.attendence_id;
    END IF;
END IF