# TO\_BASE64 & CONCAT\_WS in a trigger?

**URL:** <https://forums.percona.com/t/to-base64-concat-ws-in-a-trigger/7627>\
**Category:** Other MySQL® Questions\
**Created:** [May 14, 2020, 7:21am UTC](https://forums.percona.com/t/to-base64-concat-ws-in-a-trigger/7627 "2020-05-14T07:21:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rdab100](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rdab100/32/8_2.png) [@rdab100](https://forums.percona.com/u/rdab100)\
**Post date:** [May 14, 2020, 7:21am UTC](https://forums.percona.com/t/to-base64-concat-ws-in-a-trigger/7627/1 "2020-05-14T07:21:48Z")

</div>

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&nbsp; (`auditAction` varchar(255), `WHO` varchar(255), `bigcol` longtext);  
  
CREATE TRIGGER `user_inserts`&nbsp;&nbsp; AFTER INSERT ON `app_user`&nbsp;&nbsp; FOR EACH ROW INSERT INTO `audit_schema`.`audit_user`&nbsp; (`auditAction`,`WHO`,`bigcol`) VALUES&nbsp;&nbsp;&nbsp;&nbsp; (‘INSERT’,@CURRENT\_USER,concat\_ws(‘,’,NEW.id,NEW.name, NEW.note));  
  
Add some valuesMariaDB [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&nbsp; Duplicates: 0&nbsp; Warnings: 0  
  
Check the audit table:  
MariaDB [mf]\> select \* from audit\_schema.audit\_user;  
±------------±-----±-----------+  
| auditAction | WHO&nbsp; | bigcol&nbsp;&nbsp;&nbsp;&nbsp; |  
±------------±-----±-----------+  
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 | NULL | 1,dom,me&nbsp;&nbsp; |  
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 | NULL | 2,fred,you |  
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 | NULL | 3,mark,him |  
±------------±-----±-----------+  
Now drop the trigger and try to create the values in base64…  
MariaDB [mf]\> CREATE TRIGGER `user_inserts`&nbsp;&nbsp;   
AFTER INSERT ON `app_user`&nbsp;&nbsp;   
FOR EACH ROW INSERT INTO `audit`.`audit_user`&nbsp; (`auditAction`,`WHO`,`bigcol`) VALUES&nbsp;&nbsp;&nbsp;&nbsp; (‘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 fineThe base64 functions are there.MariaDB [mf]\> select to\_base64(name) from app\_user;  
±----------------+  
| to\_base64(name) |  
±----------------+  
| ZG9t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |  
| ZnJlZA==&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |  
| bWFyaw==&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |  
±----------------+  
3 rows in set (0.00 sec)MariaDB [mf]\> select @@version;  
±-------------------------+  
| @@version&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |  
±-------------------------+  
| 10.1.44-MariaDB-1~xenial |  
±-------------------------+

---

<div class="post-metadata">

**Author:** ![rdab100](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rdab100/32/8_2.png) [@rdab100](https://forums.percona.com/u/rdab100)\
**Post date:** [May 14, 2020, 7:24am UTC](https://forums.percona.com/t/to-base64-concat-ws-in-a-trigger/7627/2 "2020-05-14T07:24:57Z")

</div>

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&nbsp; Duplicates: 0&nbsp; Warnings: 0  
  
MariaDB [mf]\> select \* from audit\_schema.audit\_user;  
±------------±-------±-----------+  
| auditAction | WHO&nbsp;&nbsp;&nbsp; | bigcol&nbsp;&nbsp;&nbsp;&nbsp; |  
±------------±-------±-----------+  
| INSERT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | tester | 1,dom,me&nbsp;&nbsp; |  
| INSERT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | tester | 2,fred,you |  
| INSERT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | tester | 3,mark,him |  
| INSERT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | tester | 4,jane,her |  
±------------±-------±-----------+  
4 rows in set (0.00 sec)  
  
MariaDB [mf]\>

---

<div class="post-metadata">

**Author:** ![rdab100](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rdab100/32/8_2.png) [@rdab100](https://forums.percona.com/u/rdab100)\
**Post date:** [May 19, 2020, 2:28am UTC](https://forums.percona.com/t/to-base64-concat-ws-in-a-trigger/7627/3 "2020-05-19T02:28:06Z")

</div>

Anyone?

---

<div class="post-metadata">

**Author:** ![svetasmirnova](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/svetasmirnova/32/21_2.png) [@svetasmirnova](https://forums.percona.com/u/svetasmirnova)\
**Post date:** [July 1, 2020, 5:39am UTC](https://forums.percona.com/t/to-base64-concat-ws-in-a-trigger/7627/4 "2020-07-01T05:39:30Z")

</div>

@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.