Re: UDF to access OLD and NEW from trigger

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.

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

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