0

I've come across a slight issue when designing a trigger statement in our MySQL DB today:

We have the following relevant tables:

  • quote (quote_key, date, discount_rate, discount_period, supplier_key)
  • part (part_key, part_name, ...)
  • part_quote (pq_key, part_price, fk_quote_key, fk_part_key)
  • part_approval (papproval_key, is_approved, fk_part_quote_key)

Now for an explanation of the logic:

Every supplier issues quotes for different inputs into a manufacturing process (e.g. parts and components). If the price for a part is about right, they will be approved for manufacturing and can thus be used by our engineers. Since we want to be able to receive quotes from different suppliers for the same parts to enable a comparison, I've tried to model this process by using the part_quote and part_approval table.

On to my problem: If I want to approve a new part_quote, I would like the BOOL flag "is_approved" in all (or the most recent) old quotes to be automatically set to FALSE.

I tried to get this done by issuing a trigger statement: CREATE TRIGGER update_approval BEFORE INSERT ON part_approval FOR EACH ROW --- ??

I have some problems selecting the right rows to update:

  1. How do I select the part_key, which will ultimately identify all relevant rows that require updating?
  2. How do I select only the old (or most recent) rows?

I would have loved to include a screener but unfortunately, I do not have 10reps yet :/

Thank you so much in advance, All the best, Marius

mmmarius
  • 15
  • 3

2 Answers2

0

First I will answer your question as-is to the best of my ability since it is helpful to know more about how to use triggers. Then I will explain why you shouldn't actually be using triggers.

Triggers provide access to two special aliases, which are not otherwise available: OLD and NEW. NEW lets you access the new values and works in update/insert triggers. OLD lets you access the old values and works in update/delete triggers.

For your case you would probably want something like this:

CREATE TRIGGER `update_approval` BEFORE INSERT ON `part_approval`
FOR EACH ROW
UPDATE `table_x` SET `value` = y WHERE `z` = NEW.a;

For more information and some useful examples of triggers, I would suggest reading this: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

HOWEVER, triggers in MySQL cannot update the same table which they were triggered on, which is what you are looking to do. There is a good question someone had relating to this here: MySQL - Trigger for updating same table after insert and a good answer that you should use a stored procedure in this case.

Community
  • 1
  • 1
Brendan F
  • 619
  • 3
  • 8
0

I'm not sure this will solve my problem entirely, but based on your suggestions I came across a couple of helpful posts, that were able to implement similar updates within a trigger with multiple statements.

CREATE TRIGGER update_approval BEFORE INSERT ON part_approval
FOR EACH ROW BEGIN 
DECLARE part_id INT;
    SELECT part_key INTO part_id
    FROM part p, part_quote pq, part_approval a 
    WHERE NEW.part_quote_key=pq.part_quote_key AND pq.part_key = p.part_key;
UPDATE part p, part_quote pq, part_approval a 
    SET a.is_approved=DEFAULT 
    WHERE pq.part_key=part_id AND a.approval_date<NEW.approval_date;
END;

will only be able to try it out on monday after the DB has been populated. thanks for the help!

mmmarius
  • 15
  • 3