MySQL - create trigger before insert to replace date

Question

I'm having a small issue.

I want to do an insert in a table with a timestamp field called date in MySQL.

if I do a regular insert using a query with str_to_date, it works nicely. however, my program does not support using functions in the queries. so I need to find another way doing it, I was thinking about using a trigger, such as :

DELIMITER //
CREATE TRIGGER audit_oracle_date
BEFORE INSERT ON audit_oracle FOR EACH ROW
BEGIN
SET NEW.date = STR_TO_DATE(NEW.date, "%d %b %Y %H:%i:%s");
END
//
DELIMITER ;

but it does not work. the trigger is created, but when trying to enter a query like :

insert into audit_oracle values("20 Jan 2017 18:01:25","IFOMCP00","sdfdsfds","zerzerez","aaaa","SYSDBA","1","'select * from bla'","ddd","eee","2")

it gives me an error message saying "date field cannot be NULL"

any idea on how to code this trigger please ?

a regular, working query (with str_to_date) is for example :

insert into audit_oracle values(str_to_date("20 Jan 2017 18:01:25", "%d %b %Y %H:%i:%s"),"IFOMCP00","sdfdsfds","zerzerez","aaaa","SYSDBA","1","blabla","ddd","eee","2")

thanks again ! regards,

Why don't you fix your program to send the data across "correctly"? (which of course could include the embedded call to str_to_date if needed) — Rowland Shaw
– Rowland Shaw, Commented Jan 23, 2017 at 12:43
hi, because I didn't write the program myself, it's called syslog-NG (it's a well-known log management program, able to write to MySQL databases, but it's very limited) — olivierg
– olivierg, Commented Jan 24, 2017 at 12:56

Gordon Linoff · Accepted Answer · 2017-01-23 12:45:12Z

2

For better or worse, the conversion to date occurs when MySQL processes VALUES. The problem is that MySQL doesn't recognize the date format.

My recommendation would be to do the conversion on the spot, as in your last example.

If you really want to do this using a trigger, you can add another column which is a string, insert into that column, and then use the trigger:

insert into audit_oracle(datestr, . . .)
     values('20 Jan 2017 18:01:25', 'IFOMCP00', 'sdfdsfds', 'zerzerez', 'aaaa', 'SYSDBA', '1', '''select * from bla''', 'ddd', 'eee', '2');

Then your trigger would look like:

DELIMITER //
CREATE TRIGGER audit_oracle_date
BEFORE INSERT ON audit_oracle FOR EACH ROW
BEGIN
    SET NEW.date = STR_TO_DATE(NEW.datestr, "%d %b %Y %H:%i:%s");
END
//
DELIMITER ;

answered Jan 23, 2017 at 12:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

olivierg Over a year ago

thanks, it works nicely with the additional column ! I will use this solution (as I cannot do the conversion directly in the insert query due to the program restrictions, syslog-NG) thanks again !

Collectives™ on Stack Overflow

MySQL - create trigger before insert to replace date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related