The trigger does not specify the select value



  • I have a few tables with the same field, whose values should not be repeated. I thought I'd make it a generosity in the recording. Trigger just pulls the maximum value and adds 1. But somehow after. SELECT MAX(account_number) AS last_account_number INTO last_account_number FROM companies; last_account_number always null, although there are values there and the request itself is working normally.

    The trigger.

    CREATE DEFINER=`root`@`localhost` TRIGGER    generate_account_number_for_companies
    before insert
    ON companies FOR EACH ROW
    

    BEGIN
    DECLARE account_number INT;
    DECLARE last_account_number INT;

    SELECT MAX(account_number) AS last_account_number INTO last_account_number FROM companies;
    
    IF last_account_number is null THEN
            SET New.account_number = 10000000;
    ELSE
            SET New.account_number = last_account_number + 1;
    END IF;
    

    END

    I tried the same thing through SET. The result is the same.

    DECLARE last_account_number INT;
    SET last_account_number = (SELECT MAX(account_number) account_number FROM companies );



  • You're declaring a variable in your trigger. DECLARE account_number INT;♪ Her name matches the name of the column in your table. And when you do SELECT MAX(account_number) AS last_account_number INTO last_account_number FROM companies; MySQL decides the name of the local variable to a higher priority than the name of the column and chooses the meaning of this variable, and you haven't given a meaning to it.

    Given that this variable is not used in the code at all, just remove it. And in the future, avoid calling the variables the same as the columns in the tables you work with. Either in select, clearly indicate the column with the name of the table: SELECT MAX(companies.account_number) INTO last_account_number FROM companies;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2