CODEWITHSUNDEEP
mysqltriggers
user2540748
user2540748

Reputation:

MySQL Trigger to enforce constraint

I'm trying to create a table to enforce the following constraints on my movie table

Here's my trigger:

Create trigger trigger1 before insert on movies
For each row
Begin
    If (year < 1929 or length <30 or length > 480) then
Set message_text = “Invalid Input”’
End If
End

Would this be the correct way of enforcing the constraints in this situation? Or should I be doing something else?

Upvotes: 0

Views: 772

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

Check constraints are currently not supported by MySQL.

You are allowed to define one but are silently ignored by the engine. They are being allowed to define for future support.

You can add an explicit trigger to throw an error state with proper message.

Example: 

signal sqlstate 1062 set message_text = error_message;

Change your trigger body as below: 

Create trigger trigger1 before insert on movies
For each row
Begin
    If (year < 1929 or length <30 or length > 480) then
        Set error_message = 'Invalid Input ';

        signal sqlstate 1062 set message_text = error_message;
    End If;
End

Upvotes: 1

Related Questions