🔒 Locked

[web] MySQL insertable VIEW setting field value

Started by benryves Aug 6, 2009 at 7:39 AM 3 replies 1.7k views

Original Post

Aug 6, 2009 at 7:39 AM

I have a table containing text strings for a website. It's a pretty simple table:

+----------+---------+
| id       | content |
+----------+---------+
| greeting | Hello   |
+----------+---------+

Now, I'd like to extend the table to support other languages:

+----------+----------+---------+
| id       | language | content |
+----------+----------+---------+
| greeting | en       | Hello   |
| greeting | fr       | Salut   |
+----------+----------+---------+

To reduce the number of code changes that this would require, I've renamed the table and created a view:

CREATE OR REPLACE
  VIEW `content`
  AS
    SELECT
      `content_translated`.`id` AS `id`,
      `content_translated`.`content` AS `content`
    FROM
      `content_translated`
    WHERE `language`='en'
  WITH CHECK OPTION;

This works beautifully for SELECT and UPDATE, but INSERT doesn't work at all - thanks to the WITH CHECK OPTION clause I get the CHECK OPTION failed 'content' error as language is not getting set. Is there a way to set a default value for language (other than on the underlying content_translated table)? Or is this a harebrained scheme from the start?

Arkantis

Aug 8, 2009 at 11:45 AM

You may try to create a stored procedure for inserts instead. http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

benryves

Aug 8, 2009 at 12:49 PM

Using stored procedures is a good idea for future references, but that would require rewriting the existing code "properly" (we didn't have the luxury of the ability to choose modern versions of MySQL on the other projects). [sad] I was looking for a quick fix, and triggers seem suitable:

CREATE OR REPLACE  VIEW `content`  AS    SELECT      `content_translated`.`id` AS `id`,      `content_translated`.`content` AS `content`    FROM      `content_translated`    WHERE `language`='en'  WITH CHECK OPTION;DROP TRIGGER IF EXISTS `content_set_language`;CREATE  TRIGGER `content_set_language` BEFORE INSERT  ON `content_translated`  FOR EACH ROW    SET NEW.`language`='en';

BeanDog

Aug 11, 2009 at 3:53 PM

Please note that you need SUPER privileges to create triggers in MySQL 5.0 (and in 5.1 if you have binary logging enabled, which you probably do). Make sure that you'll have SUPER privileges in MySQL wherever you'll be deploying this project.

benryves

Aug 12, 2009 at 4:15 AM

Thanks for the warning, a point well made. [smile]

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.