MySQL Forums
Forum List  »  Triggers

Accessing NEW.x values from within stored procedures called by triggers
Posted by: Beat Vontobel
Date: November 02, 2005 04:28AM

I need some typical triggers checking and possibly correcting values on INSERTs and UPDATEs. I want to encapsulate their functionality in a stored procedure as the exactly same code is used by more than one trigger. My problem now is: How do I set NEW.x values from within a stored procedure?

The following approach doesn't work, as on every insert MySQL tells me "ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.check_data is not a variable":

CREATE PROCEDURE check_data(INOUT x INT) DETERMINISTIC NO SQL SET x = 5;
CREATE TRIGGER insert_check BEFORE INSERT ON my_data FOR EACH ROW CALL check_data(NEW.x);

It seems as OUT and INOUT parameters are really limited to just @user_variables and can't be used with other things that are allowed on the LHS of a SET statement.

It's also impossible to SET a NEW.x value directly inside a stored procedure. At the time I run the CREATE PROCEDURE statement the server of course doesn't know what to do with a NEW.x and just says "ERROR 1193 (HY000): Unknown system variable 'x'" on the following statement:

CREATE PROCEDURE check_data() DETERMINISTIC SET NEW.x = 5;

Any other (working!) suggestions on how to SET NEW.x values from within stored procedures?

Thanks!
Beat


(Please don't tell me to skip triggers completely and only allow inserts into the tables via stored procedures - that's just not what I want in this specific case ;-) )

Options: ReplyQuote


Subject
Views
Written By
Posted
Accessing NEW.x values from within stored procedures called by triggers
3650
November 02, 2005 04:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.