Not the answer you need?
Register and ask your own question!

TO_BASE64 & CONCAT_WS in a trigger?

rdab100rdab100 ContributorCurrent User Role Patron
edited May 14 in Other MySQL® Questions
I am sure this is down to syntax but a bit stumpted.
Got a simple three column table appuser.
create table app_user (id int, name varchar(255), note varchar(255));
I need to create a trigger that creates base64, delimited & concatenated version of the row AFTER insert with a few extra columns and inserts that into an audit schema.
create table audit_schema.audit_user  (`auditAction` varchar(255), `WHO` varchar(255), `bigcol` longtext);

CREATE TRIGGER `user_inserts`   AFTER INSERT ON `app_user`   FOR EACH ROW INSERT INTO `audit_schema`.`audit_user`  (`auditAction`,`WHO`,`bigcol`) VALUES     ('INSERT',@CURRENT_USER,concat_ws(',',NEW.id,NEW.name, NEW.note));

Add some values
MariaDB [mf]> insert into app_user values (1,'dom','me'),(2,'fred','you'),(3,'mark','him');
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

Check the audit table:

MariaDB [mf]> select * from audit_schema.audit_user;
+-------------+------+------------+
| auditAction | WHO  | bigcol     |
+-------------+------+------------+
|           0 | NULL | 1,dom,me   |
|           0 | NULL | 2,fred,you |
|           0 | NULL | 3,mark,him |
+-------------+------+------------+
Now drop the trigger and try to create the values in base64...
MariaDB [mf]> CREATE TRIGGER `user_inserts`  
AFTER INSERT ON `app_user`  
FOR EACH ROW INSERT INTO `audit`.`audit_user`  (`auditAction`,`WHO`,`bigcol`) VALUES     ('INSERT',@CURRENT_USER,concat_ws(',',(TO_BASE64('NEW.id'),TO_BASE64('NEW.name'), TO_BASE64('NEW.note'));
(I've removed the BEGIN and END and the error handling.)
Anyone spot where I have the syntax wrong? (this isn't some test).
Without the BASE64 bit it creates and works fine
The base64 functions are there.
MariaDB [mf]> select to_base64(name) from app_user;
+-----------------+
| to_base64(name) |
+-----------------+
| ZG9t            |
| ZnJlZA==        |
| bWFyaw==        |
+-----------------+
3 rows in set (0.00 sec)
@version;
+--------------------------+
@version               |
+--------------------------+
| 10.1.44-MariaDB-1~xenial |
+--------------------------+









Answers

  • rdab100rdab100 Contributor Current User Role Patron
    edited May 14
    Neglected to add the @current_user bit... the application will do this.
    MariaDB [mf]> set @CURRENT_USER = "tester";
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [mf]> insert into app_user values (1,'dom','me'),(2,'fred','you'),(3,'mark','him'),(4,'jane','her');
    Query OK, 4 rows affected (0.12 sec)
    Records: 4  Duplicates: 0  Warnings: 0

    MariaDB [mf]> select * from audit_schema.audit_user;
    +-------------+--------+------------+
    | auditAction | WHO    | bigcol     |
    +-------------+--------+------------+
    | INSERT      | tester | 1,dom,me   |
    | INSERT      | tester | 2,fred,you |
    | INSERT      | tester | 3,mark,him |
    | INSERT      | tester | 4,jane,her |
    +-------------+--------+------------+
    4 rows in set (0.00 sec)

    MariaDB [mf]>


  • rdab100rdab100 Contributor Current User Role Patron
    Anyone?

  • svetasmirnovasvetasmirnova Percona Percona Staff Role
    edited July 1
    @rdab100
    Can you send full trigger definition with to_base64 function and error you get? It is not clear what is wrong with the information you provided.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.