Search code examples
mysqlsqldatabaserdbmsdatabase-trigger

Error for before insert or update on

I am a new to queries in dbms, so If someone could figure out what should be done here. Also, I am using Mysql.

code:

-> create trigger age_trigger before insert or update of age on employee
-> for each row
-> when( new.age <25)
-> begin
-> select 'Age can not be less than 25'
-> end;

error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'or update of age on employee for each row when( new.age <25) begin select 'Age c' at line 1

Solution

  • Below can be one of the option to implement BEFORE INSERT to obtain your desired result.

    CREATE TRIGGER age_trigger
BEFORE INSERT ON table1
FOR EACH ROW 
BEGIN 
IF NEW.age < 25 
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age can not be less than 25'; 
END IF; 
END;

    Similar syntax can be used for before update trigger.

    DEMO