Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to make a column in a relation unmodifyable for consistency reasons.

The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that anyone is able to change the IDs of the relationship partners.

What I have come up with so far:

Creating a trigger that checks if the NEW value is the same as the OLD value. But I don't know how to deny the UPDATE action if the condition proves as TRUE.

Here is my trigger so far:

CREATE TRIGGER deny_nton_change BEFORE UPDATE ON Schueler_in_Klasse
FOR EACH ROW BEGIN
    IF NEW.Schueler_ID != OLD.Schueler_ID OR NEW.Klasse_ID != OLD.Klasse_ID THEN

    END IF;
END;
share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

Rather than abort the update, just override it?

...
FOR EACH ROW BEGIN
    SET NEW.Schueler_ID = OLD.Schueler_ID;
    SET NEW.Klasse_ID = OLD.Klasse_ID;
END
share|improve this answer
    
+1 because this is much simpler than my approach. I am going to look over the MySQL Stored Procedure Language Book (amazon.com/dp/0596100892) and see it suggests this in the same chapter. –  RolandoMySQLDBA Nov 24 '11 at 18:22
    
BTW congrats on 20000 rep and being the first DBA.SE trusted user. –  RolandoMySQLDBA Nov 28 '11 at 17:10
    
I think I'll use this then. Thanks! –  sinni800 Nov 29 '11 at 11:07
add comment

A fairly nice and clean way to abort and return a readable error message is by using a signal:

SIGNAL sqlstate '45000' SET message_text = 'Schueler_ID and Klasse_ID may not be altered!';

Do make sure your MySQL version supports this, it was introduced in MySQL 5.5. In earlier versions you need hacks like inserting a non existant field into a non existant table.

As gbn mentions, overwriting the new values with the old ones is a good option too.

share|improve this answer
    
As suggested by @RolandoMySQLDBA, this seems to not work... It says there is an error in my SQL syntax –  sinni800 Nov 29 '11 at 11:06
    
like I said, check the mysql version. It works on 5.5 fine. link To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.” –  Jannes Dec 1 '11 at 11:37
add comment

I actually learned a back-alley approach to aborting triggers. It is very crude, but works because SIGNAL processing is not implemented in MySQL's Stored Procedure Language. It can be emulated at best.

Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers' suggests making a SELECT of a character string into a dummy integer (in terms of syntax, it is correct, but dies on execution [NO PUN INTENDED]):

CREATE TRIGGER deny_nton_change BEFORE UPDATE ON Schueler_in_Klasse 
FOR EACH ROW BEGIN
    DECLARE dummy INT; 
    IF NEW.Schueler_ID != OLD.Schueler_ID OR NEW.Klasse_ID != OLD.Klasse_ID THEN 
        SELECT 'Cannot Carry Out This Trigger Any Further'
        INTO dummy FROM table_you_know_does_not_exist WHERE 1=1 LIMIT 1;
    END IF; 
END; 
share|improve this answer
    
Wow, that is really... Back-alley. Nice one... –  sinni800 Nov 24 '11 at 18:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.