Accessing NEW.x values from within stored procedures called by triggers
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 ;-) )
Subject
Views
Written By
Posted
Accessing NEW.x values from within stored procedures called by triggers
3804
November 02, 2005 04:28AM
2188
November 02, 2005 08:12AM
2191
November 03, 2005 11:31AM
2534
November 04, 2005 03:05AM
2417
November 04, 2005 05:35AM
2042
November 04, 2005 06:36AM
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.