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 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 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 fineThe 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)MariaDB [mf]> select @@version;
±-------------------------+
| @@version |
±-------------------------+
| 10.1.44-MariaDB-1~xenial |
±-------------------------+
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]>
Anyone?
@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.