Bug #105162 Parameter columns/fields that can also be calculated fields

• Submitted: 7 Oct 2021 7:55
• Modified: 7 Oct 2021 12:51
• Reporter: Justin Levene
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: columns, fields, Parameters

[7 Oct 2021 7:55] Justin Levene

Description:
In a table define rows that NEVER save the data, they are just used to pass parameters that can be used inside triggers such as "BEFORE UPDATE", "AFTER_UPDATE", "BEFORE_INSERT", etc.

There are loads of uses from security, verification, integrity, field calculation, etc.

The parameter fields should be type casted.

Also maybe add and "AFTER_SELECT" trigger that can calculate parameter fields, as this would sort of be an alternative for calculated fields.

We do have global variables, but for things like bulk insert, global variables are very clumsy and inefficient.

How to repeat:
These parameter fields are never saved, and can only be used in triggers, so its a way of passing parameters to the triggers.

Examples of use:

* For security, as many hacks infiltrate a read/write user, that user won't be able to see the code for the triggers, so won't know the algorithm for a security key.

* For data generation, you could pass the length and width as parameter fields which calculates the area that is save to the "AREA" column.  

* Alternatively for integrity pass the area and if the area does not = length x width, raise an error.

The above are simplistic, but you get the idea.

Also there should be default trigger values that can be set using "set trigger.name='hello'".  This will default every table trigger that is called "name" to "hello" unless it is set in the update, insert, etc.

Suggested fix:
This is something not done by any other database, so it's something that MySQL can set a precedence on. 

My personal preference is the security it can add.

[7 Oct 2021 12:51] MySQL Verification Team

Hi Mr. Levene,

Thank you for your feature request.

MySQL is strictly following SQL standard as well as many other standards. None of these standards allow that rows are not saved. Hence, your feature request is unacceptable.

For your purpose you can use temporary table and if you do not wish to have those data on the permanent storage, you can use MEMORY engine. Hence, you have already got a solution to your idea.