syntax for trigger before insert

Question

Ok, so I see that this the wrong approach:

mysql> 
mysql> show tables;
+---------------------+
| Tables_in_nntp      |
+---------------------+
| articles            |
| newsgroups          |
| newsgroups_articles |
+---------------------+
3 rows in set (0.00 sec)

mysql> describe newsgroups;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| newsgroup | longtext | NO   |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table newsgroups;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| newsgroups | CREATE TABLE `newsgroups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsgroup` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE newsgroups ADD UNIQUE (newsgroup);
ERROR 1170 (42000): BLOB/TEXT column 'newsgroup' used in key specification without a key length
mysql>

The has should be populated with a trigger?

Ok, as root I made a trigger as so:

mysql> 
mysql> show tables;
+---------------------+
| Tables_in_nntp      |
+---------------------+
| articles            |
| newsgroups          |
| newsgroups_articles |
+---------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> delimiter |
mysql> CREATE TRIGGER make_hash BEFORE INSERT ON newsgroups
    ->   FOR EACH ROW BEGIN
    ->       INSERT INTO hash values ('0');
    ->   END;
    -> |
Query OK, 0 rows affected (0.18 sec)

mysql>

However, that's just dummy data. How can I make that trigger actually create the hash?

'TRIGGER command denied to user ...' - you should check what privileges your user has and run the create trigger with a different user if necessary. — Vatev, Jul 28 '12 at 23:41
@Vatev I'll try as root, but I don't think that's the problem, user java has `USAGE`. — Thufir, Jul 28 '12 at 23:55

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

I think you should leave your primary key as is.
You can add a hash column

ALTER TABLE `newsgroups` ADD COLUMN `hash` CHAR(32) NOT NULL DEFAULT '';

and then populate it with

UPDATE newsgroups SET hash = MD5(newsgroup);

then remove the duplicates and add your unique constraint.

You can also add BEFORE INSERT and BEFORE UPDATE triggers to set the hash value

CREATE DEFINER=`root`@`localhost` 
TRIGGER `before_insert_newsgroups` 
BEFORE INSERT ON `newsgroups` 
FOR EACH ROW BEGIN

    set new.hash = md5(new.newsgroup);

END

Depending on the SQL client you are using you might wan't to change the DELIMITER before and after the create trigger statement

edited May 23 '17 at 12:13

Community

1
1

answered Jul 28 '12 at 23:15

Vatev

7,493
1
32
39

I'm looking at the [manual](http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html) and are you giving SQL to alter the table? These commands are from the MySql console? – Thufir Jul 28 '12 at 23:22
It wasn't intended to be a complete statement, i added the missing part. – Vatev Jul 28 '12 at 23:39
ok created a dummy trigger, but can't get the syntax for the kind of trigger which you describe above. – Thufir Jul 29 '12 at 06:35
I seem to be missing something here. You need another table for the hashes ? – Vatev Jul 29 '12 at 12:48
the same table should have newsgroup (just a string, 80 chars or so) and hash, so that the hash can be set as unique. – Thufir Jul 29 '12 at 12:53
Why are you trying to insert into a table named 'hash' then? Also if your string is ~80 chars - just make it UNIQUE. The hash would be necessary for long texts (maybe > 500 or something). – Vatev Jul 29 '12 at 12:58

syntax for trigger before insert

1 Answers1