UDF to access OLD and NEW from trigger

Mansour Al Akeel

unread,

Jun 5, 2015, 7:58:43 AM6/5/15

to

Hello,

I am searching the possibility to write a UDF that needs access to all
the fields before and after an update of a record.
The function needs to convert this record to a CSV.

OLD and NEW keywords inside a trigger access the fields values only if
they are known in advance.
For example (OLD.id). I can not access the whole record or convert it
to a CSV as if (OLD.*).

Is there a way in mysql to convert the values of a record without
knowing its fields name (OLD and NEW) inside a trigger to a CSV ?

Thank you.

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

shawn l.green

unread,

Jun 5, 2015, 1:32:11 PM6/5/15

to

On 6/5/2015 7:53 AM, Mansour Al Akeel wrote:
> Hello,
>
>
> I am searching the possibility to write a UDF that needs access to all
> the fields before and after an update of a record.
> The function needs to convert this record to a CSV.
>
> OLD and NEW keywords inside a trigger access the fields values only if
> they are known in advance.
> For example (OLD.id). I can not access the whole record or convert it
> to a CSV as if (OLD.*).
>
> Is there a way in mysql to convert the values of a record without
> knowing its fields name (OLD and NEW) inside a trigger to a CSV ?
>
> Thank you.
>

The OLD and NEW psuedo tables only exist inside of Triggers. To pass
those values to a function, you need to pass those fields individually
as parameters. This should be easy as every trigger is created uniquely
per table and when you create the trigger you already know the columns
on the table.

What exactly are you attempting to accomplish with your UDF? There may
be easier ways to go about it.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

Mansour Al Akeel

unread,

Jun 6, 2015, 7:37:47 AM6/6/15

to

In postgres we can use OLD and NEW in a trigger to get hstore values.
I want to get the same values without knowing the fields of the table
in advance.

Please see https://wiki.postgresql.org/wiki/Audit_trigger_91plus

shawn l.green

unread,

Jun 8, 2015, 9:26:37 AM6/8/15

to

Hello Mansour,

On 6/6/2015 7:33 AM, Mansour Al Akeel wrote:
> In postgres we can use OLD and NEW in a trigger to get hstore values.
> I want to get the same values without knowing the fields of the table
> in advance.
>
> Please see https://wiki.postgresql.org/wiki/Audit_trigger_91plus
>
>

Please stop top posting.

I reviewed your link. The problem is not with OLD vs NEW, it is with the
fact that MySQL does not have an array-type data type.

This is the code you want to write:
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
audit_row.row_data = hstore(OLD.*) - excluded_cols;

But you have not auto-discovered all the columns in this table, those
were found for you earlier in the trigger here...
> audit_row = ROW(
> NEXTVAL('audit.logged_actions_event_id_seq'), -- event_id
> TG_TABLE_SCHEMA::text, -- schema_name
> TG_TABLE_NAME::text, -- table_name
> TG_RELID, -- relation OID for much quicker searches
> session_user::text, -- session_user_name
> ... remainder of columns skipped for brevity ...

This trigger code builds an array, then does array-type arithmetic
against it. You have not escaped the requirement to name every column
you want to check on the table in order to read its value.

In MySQL, you don't have array variables and you don't have the hstore()
function. Other than that, I see no major differences in how our two
platforms handle the OLD and NEW pseudo tables.