MySQL default value based on view
- by Jake
Basically I have a bunch of views based on a simple discriminator column (eg. CREATE VIEW tablename AS SELECT * FROM tablename WHERE discrcolumn = "discriminator value").
Upon inserting a new row into this view, it should insert "discriminator value" into discrcolumn.
I tried this, but apparently MySQL doesn't figure this out itself, as it throws an error "Field of view viewname underlying table does not have a default value". The discriminator column is set to NOT NULL of course.
How do I mend this? Perhaps a pre-insert trigger?
UPDATE: Triggers won't work on views, see below comment.
Would it work to create a trigger on the table which uses a variable, and set that variable at establishing the connection? For each connection the value of that variable would be the same, but it could differ from other connections.
EDIT:
This appears to work...
Setup:
CREATE TRIGGER insert_[tablename] BEFORE INSERT ON [tablename]
FOR EACH ROW SET NEW.[discrcolumn] = @variable
Runtime:
SET @variable = [descrvalue];
INSERT INTO [viewname] ([columnlist]) VALUES ([values]);
