Hi Patrick,
Patrick Aljord wrote:
I would like to prohibit the value 'xxx' on my column title, and if it
does contain the value I would like to create an exception by
assigning 'xxx' to the primary key id which is int(5).
This is what I do but I get an error on its creation so I guess it's
not the right way:
CREATE TRIGGER testref BEFORE INSERT ON bookmarks
FOR EACH ROW
BEGIN
if NEW.title like '%xxx%'
set NEW.id='xxx';
END;
the error:
server version for the right syntax to use near ':
set NEW.id='xxx' at line 4
You have your IF syntax a little wrong, try this:
CREATE TRIGGER testref BEFORE INSERT ON bookmarks
FOR EACH ROW
BEGIN
IF NEW.title LIKE '%xxx%' THEN
SET NEW.id ='xxx';
END IF;
END;
//
mysql> INSERT INTO bookmarks values ('two', 'hawt xxx sex')//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM bookmarks//
+------+--------------+
| id | title |
+------+--------------+
| one | one bookmark |
| xxx | hawt xxx sex |
+------+--------------+
2 rows in set (0.27 sec)
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
