TO_BASE64 & CONCAT_WS in a trigger?

rdab100rdab100 ContributorActive Member Contributor
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 Active Member Contributor
    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 Active Member Contributor
    Anyone?

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.