📋

Description

MariaDB Error 1414 occurs when an `OUT` or `INOUT` argument for a stored routine, invoked from within a `BEFORE` trigger, is not a variable or the `NEW` pseudo-variable. This typically happens when you attempt to pass a literal value, an expression, or a table column name directly instead of a valid variable or `NEW.column_name` reference.

💬

Error Message

OUT or INOUT argument %d for routine %s is not a variable or NEW pseudo-variable in BEFORE trigger

🔍

Known Causes

3 known causes

⚠️

Passing Literal Values

Providing a constant or literal value (e.g., 'text', 123) as an `OUT` or `INOUT` argument to a routine called from a `BEFORE` trigger.

⚠️

Using Expressions as Arguments

Supplying an expression (e.g., `column + 1`, `CONCAT('prefix', value)`) instead of a variable or `NEW` pseudo-variable for an `OUT` or `INOUT` parameter.

⚠️

Direct Column Reference

Attempting to pass a table column name (e.g., `my_column`) directly to an `OUT` or `INOUT` parameter within a `BEFORE` trigger, instead of `NEW.my_column`.

🛠️

Solutions

3 solutions available

1. Correct Trigger Argument Usage easy

Ensure OUT or INOUT arguments in BEFORE triggers are always variables or NEW pseudo-variables.

1

Identify the trigger causing the error. The error message will often point to the routine name (the trigger name).

2

Examine the trigger's body. Look for any `OUT` or `INOUT` parameters being used in the trigger's logic. In `BEFORE` triggers, these parameters *must* be assigned to either a declared variable within the trigger or to the `NEW` pseudo-variable (for `INSERT` and `UPDATE` triggers).

DELIMITER $$ 
CREATE TRIGGER my_before_trigger 
BEFORE INSERT ON my_table 
FOR EACH ROW 
BEGIN 
  -- Incorrect usage: Directly using a literal or a column that's not NEW 
  -- CALL my_procedure(@some_value_or_column_name);  -- if @some_value_or_column_name is not NEW.column or a declared var

  -- Correct usage: Assigning to a declared variable or NEW
  SET @temp_var = NEW.some_column; 
  CALL my_procedure(@temp_var); 
END$$ 
DELIMITER ;

3

If the `OUT` or `INOUT` argument is being passed a literal value or a column from the `OLD` pseudo-variable (in `BEFORE` triggers, `OLD` is not available for modification), you must first store that value in a variable (declared within the trigger or a session variable) and then pass that variable. For `UPDATE` triggers, you can use `NEW.column_name`.

DELIMITER $$ 
CREATE TRIGGER update_before_trigger 
BEFORE UPDATE ON my_table 
FOR EACH ROW 
BEGIN 
  DECLARE my_output_param INT;
  -- Assuming my_procedure takes an INOUT parameter
  CALL my_procedure(NEW.some_column, my_output_param);
  -- Now use my_output_param as needed, it's a variable.
  SET NEW.another_column = my_output_param;
END$$ 
DELIMITER ;

2. Convert to AFTER Trigger if Variable Assignment is Not Feasible medium

If direct variable assignment within the BEFORE trigger is complex or impossible, consider using an AFTER trigger.

1

Analyze the logic within the trigger. Determine if the operation involving the `OUT` or `INOUT` argument can be performed *after* the row has been inserted or updated.

2

If the operation is safe to perform after the data modification, rewrite the trigger as an `AFTER` trigger. In `AFTER` triggers, you can access the `NEW` pseudo-variable (for `INSERT` and `UPDATE`) and `OLD` pseudo-variable (for `UPDATE` and `DELETE`) for reading, but not for modification of the current row being processed. However, you can use them to pass values to procedures or other logic.

DELIMITER $$ 
CREATE TRIGGER my_after_trigger 
AFTER INSERT ON my_table 
FOR EACH ROW 
BEGIN 
  -- In an AFTER trigger, you can read NEW.column and pass it to a procedure.
  -- If the procedure modifies an OUT/INOUT parameter, you'll need to store it in a variable.
  SET @procedure_result = NULL;
  CALL my_procedure_that_uses_out_param(NEW.some_column, @procedure_result);
  -- Now you can use @procedure_result for further actions (e.g., logging, another table).
END$$ 
DELIMITER ;

3

If the procedure's `OUT` or `INOUT` parameter needs to directly affect the row being inserted/updated, and this cannot be done by modifying `NEW` in a `BEFORE` trigger, you might need a multi-step approach. For example, an `AFTER` trigger could update a *different* table or log the result, and then a separate process or another trigger (if carefully designed) could use that information.

3. Refactor Stored Routine to Use IN Parameters advanced

Modify the stored routine to accept `IN` parameters instead of `OUT` or `INOUT` if the logic allows.

1

Examine the stored routine (procedure or function) that the trigger is calling. Identify the `OUT` or `INOUT` parameters.

2

Determine if the routine's logic actually *needs* to return a value via an `OUT` or `INOUT` parameter to be used *within* the trigger's `BEFORE` context. Often, routines are designed to return values that are then assigned to `NEW.column` or other variables. If the primary purpose is to *provide* data to the routine, and the routine's modifications are secondary or can be handled differently, consider changing the parameter type.

3

If the routine's output is intended to be assigned back to a column of the row being modified, and this assignment can be done directly within the `BEFORE` trigger using `NEW.column_name = routine_result;`, then refactor the stored routine to use `IN` parameters and have it return a value using `RETURN` (for functions) or by setting a variable that the trigger then uses.

Example: Refactoring a procedure to be a function.

-- Original Procedure (might cause error in BEFORE trigger if @result is not a variable)
-- CREATE PROCEDURE my_proc (IN p1 INT, OUT p2 INT) ...

-- Refactored Function
DELIMITER $$ 
CREATE FUNCTION my_func (p1 INT) RETURNS INT 
DETERMINISTIC 
BEGIN 
  DECLARE result_val INT;
  -- ... logic to calculate result_val ...
  RETURN result_val;
END$$ 
DELIMITER ;

-- Trigger using the function
DELIMITER $$ 
CREATE TRIGGER my_before_trigger 
BEFORE INSERT ON my_table 
FOR EACH ROW 
BEGIN 
  SET NEW.some_column = my_func(NEW.another_column);
END$$ 
DELIMITER ;

4

If the routine truly needs to modify an `OUT` or `INOUT` parameter that the trigger *must* then use, and this is critical in a `BEFORE` trigger, you might need to use session variables (`@variable_name`) as intermediaries. The trigger passes a session variable to the procedure, the procedure modifies it (if it's `INOUT`), and the trigger then reads the modified session variable. This is a workaround and can make code harder to follow.

DELIMITER $$ 
CREATE TRIGGER my_before_trigger 
BEFORE INSERT ON my_table 
FOR EACH ROW 
BEGIN 
  SET @trigger_output = NULL; -- Initialize session variable
  -- Assuming my_procedure takes INOUT param
  CALL my_procedure(NEW.some_column, @trigger_output);
  -- Now use @trigger_output, which was modified by the procedure
  SET NEW.target_column = @trigger_output;
END$$ 
DELIMITER ;

🔗

Related Errors

5 related errors

MariaDB Error 1414: Invalid Trigger Argument Type