How can I determine why my trigger does not run?-CodePudding

   create database triggers;
    
    use triggers;
    
    create table if not exists Customers(
        custID INT unsigned not null auto_increment,
        age int,
        name varchar(30),
        primary key(custID)
    );
    
    delimiter //
    create trigger age_verify
    before insert on customers
    for each row
    if new.age < 0 then set new.age = 0;
    end if; //
    
    insert into Customers
    values (101, 27, 'James'),
    (102, -40, 'Ammy'),
    (103, 32, 'Ben'),
    (104, -39, 'Angela');
    
    select * from Customers;

For some reason my trigger in MySQL workbench does not run and when I run select * from customers it prints the negatives still and does not update the value. How can I debug this?

CodePudding user response：

Your trigger is syntactically incorrect. Multiple-statement trigger code must be enclosed with BEGIN-END block:

delimiter //
create trigger age_verify
before insert on customers
for each row
BEGIN
    if new.age < 0 then 
        set new.age = 0;
    end if;
END //
DELIMITER ;

But in your particular case you do not need in BEGIN-END and DELIMITER, use simple

CREATE TRIGGER age_verify
BEFORE INSERT ON customers
FOR EACH ROW
SET NEW.age = GREATEST(NEW.age, 0);

CodePudding user response：

delimiter //
    create trigger age_verify before
    insert on Customers
    for each row
    begin
    if new.age < 0 then set new.age = 0;
    end if;
    end
 //
delimiter ;