MySQL Trigger with SELECT statement

Question

As an example, say I have a these tables...

person (id, currentyear)

personteam (id, workyear, team)

A person is assigned a team for each year they work at a company. 'personteam' stores a record of the teams. The insert into 'personteam' is only allowed if the 'currentyear' field in 'person' equals the 'workyear' in 'personteam'.

I want to do some checks when a user inserts into PERSON_TEAM, based on what is stored in PERSON. So I am writing a trigger with a select statement. I am not a 100% sure on the syntax of MySQL triggers so hopefully this makes sense...

CREATE TRIGGER mytesttrigger BEFORE INSERT ON personteam

FOR EACH ROW
SELECT currentyear AS @variablename
FROM person p
WHERE p.id = NEW.id

IF @variablename != NEW.workyear
ERROR 
END

Could anyone provide a revised, corrected syntax for such an operation?

Thank you.

(This example is trivial so I can learn so please excuse how meaningless it may sound)

Now assuming... FOR EACH ROW BEGIN SET @variablename = (SELECT currentyear FROM person p WHERE p.id = NEW.id); IF @variablename != NEW.workyear THEN ERROR; ENDIF; END; — ajr
– ajr, Commented Oct 28, 2009 at 23:10

Costique · Accepted Answer · 2012-04-16 16:52:03Z

3

According to the MySQL standard, the proper way to handle this would be the SIGNAL command to throw an error back to the client. since this is a BEFORE trigger, an error in the trigger will prevent mysql from moving on and inserting the row.

However, mysql does not yet support SIGNAL, so we have to come up with a way to cause an arbitrary error.

One way to do this is to CALL a non-existent procedure, as demonstrated here.

A better idea would be to remove INSERT rights from this table and instead use a stored procedure to handle the insert for you.

edited Apr 16, 2012 at 16:52

Costique

23.7k4 gold badges78 silver badges79 bronze badges

answered Oct 29, 2009 at 13:44

longneck

12.3k3 gold badges39 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Trigger with SELECT statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related