MySQL Forums
Forum List  »  Triggers

Re: inserting actual username of a person who edited record into a table with trigger MySQL
Posted by: Peter Brawley
Date: October 04, 2017 09:12AM

5.1 is nine years old, very old technology. Upgrade.

> CREATE TABLE `addresses_do-not-use`

Do not use illegal chars in database identifier names, it just makes trouble, eg use an undersscore instead of a dash.

>`ADD1` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
>`ADD2` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
>`ADD3` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
>`ADD4` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',

Major design error. If a table has more than one of some kind of thing, those things need to be in a child table with a foreign key pointing at this table. Read about relational database design.

The addresses_donotuse table is meant to log changes to addresses?

You have ...

INSERT INTO `addresses_do-not-use`
(`action`, `revision`, `username`, `dt_datetime`, ... )
SELECT 'delete', SUBSTRING_INDEX(USER(),'@',1), NULL, NOW(), ... )

... which inserts user info into `revision` and Null into `username`.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: inserting actual username of a person who edited record into a table with trigger MySQL
655
October 04, 2017 09:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.