Bug #14635 Accept NEW.x as INOUT parameters to stored procedures from within triggers
Submitted: 4 Nov 2005 11:33 Modified: 24 May 2006 17:50
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.19-BK, 5.0.18 OS:Any (any)
Assigned to: Tomash Brechko CPU Architecture:Any

[4 Nov 2005 11:33] Beat Vontobel
Description:
MySQL currently doesn't allow NEW.x values in triggers to be used as INOUT (our OUT) parameters for stored procedures. This doesn't make sense as NEW.x values can well be used on the left hand side of SET statements within TRIGGERS.

With the current behaviour it's not possible to encapsulate the functionality of a trigger nicely within a stored procedure which would be very desirable for example to reuse the same functionality within multiple triggers.

How to repeat:
mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE PROCEDURE p(INOUT i INT) DETERMINISTIC NO SQL SET i = 1;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER tt BEFORE INSERT ON t FOR EACH ROW CALL p(NEW.i);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (2);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.p is not a variable

Suggested fix:
Handle NEW.x values from within triggers as you do handle declared variables already.

This should be possible within a TRIGGER:

... FOR EACH ROW CALL check_data(NEW.a, NEW.b, NEW.c, ...) 

But now you'll have to do something like this to workaround the limitation:

... FOR EACH ROW 
BEGIN 
DECLARE new_a TYPE DEFAULT NEW.a; 
DECLARE new_b TYPE DEFAULT NEW.b; 
DECLARE new_c TYPE DEFAULT NEW.c; 
... 
CALL check_data(new_a, new_b, new_c, ...); 
SET NEW.a = new_a; 
SET NEW.b = new_b; 
SET NEW.c = new_c; 
... 
END

See http://forums.mysql.com/read.php?100,52401,52401 for a discussion.
[2 Feb 2006 8:45] Beat Vontobel
Changed severity to S3 as this report hasn't been looked at for three months now as S4 and it actually conforms to the definition of S3: "Represents a minor loss of service, inconvenient usage; but a workaround is available."

Also the version was changed from 5.0.15 to 5.0.18 (just checked the behaviour again) and please note that the above link to the forums was wrong. Use http://forums.mysql.com/read.php?99,52409,52875 instead.
[2 Feb 2006 12:21] Valeriy Kravchuk
Thank you for a problem report. I still think that it is a feature request (that should be checked against SQL 2003 Standard, Oracle's implementation etc). 
You can use stored function that return value, instead of SP with OUT parameter...

But let the developers decide.

Verified just as described on 5.0.19-BK on Linux. The behaviour is stil the same:

mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE p(INOUT i INT) DETERMINISTIC NO SQL SET i = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER tt BEFORE INSERT ON t FOR EACH ROW CALL p(NEW.i);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (2);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.p is not a variable
[2 Feb 2006 12:49] Roland Bouman
Looks like Oracle (at least 9i) supports it. The following example was taken from the Oracle 9i SQL Reference (Release 9.2, Part No. A96540-02): Syntax for the CREATE TRIGGER statement, (p15-108):

CREATE TRIGGER hr.salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
FOR EACH ROW
WHEN (new.job_id <> ’AD_VP’)
CALL check_sal(:new.job_id, :new.salary, :new.last_name);

As far as I can see, the SQL standard is not that specific. For routine invocation, they just note that you can pass SQL arguments which are basically value expressions. As far as I can see, they do not even attempt to specify that you must pass an assignable expression (variable) to an OUT or INOUT parameter.
[2 Feb 2006 13:05] Beat Vontobel
Thanks, Valeriy and Roland for your comments. 

> As far as I can see, they do not even attempt to specify
> that you must pass an assignable expression (variable)
> to an OUT or INOUT parameter.

Then I think it would just be natural that every assignable expression can be used as an OUT or INOUT parameter.

> You can use stored function that return value, instead
> of SP with OUT parameter...

Unfortunately not if you have to check and possibly change multiple values that somehow depend on each other.
[2 Feb 2006 14:25] Roland Bouman
I hope I got it right as far as the standard's concerned. I mean, I could spend time and give it a really thorough read, but anyone that ever had a look at that monstrous evergrowing spec knows that that could take quite some time. For now I just briefly scanned the paragraphs on triggers in part 2 and 4. 

Beat, if you like: http://rpbouman.blogspot.com/2005/11/sql-2003-drafts-for-download.html (but don't tell anyone I told you ;-)
[5 Apr 2006 23:54] Trudy Pelzer
I've checked SQL:2003 and confirm that this -- CALL p(NEW.i) --
is a legal assignment in standard SQL.
[1 May 2006 12:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5747
[3 May 2006 18:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5912
[9 May 2006 9:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6138
[12 May 2006 9:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6293
[18 May 2006 21:04] Dmitry Lenev
Fixed in 5.0.22 and 5.1.11
[24 May 2006 17:50] Paul Dubois
Noted in 5.0.23, 5.1.11 changelogs.

It is now possible to use
<literal>NEW.<replaceable>var_name</replaceable></literal>
values within triggers as <literal>INOUT</literal> parameters
to stored procedures.