??? ?? ???? MySQL ???: ?????? ?? ???

MySQL ???? INSERT, UPDATE? ?? ???? ?? ???? ?? ???? ???????? ??? ??? ???? ??? ? ?? ??? ?????. ?? ??. ???? ?????? ???? ??? ??? ????? ??? ?? ?? ???? ??? ????, ??? ???? ????? ?? ??? ???? ? ?????.

? ?????? ???? ????, ??? ????? ???? MySQL??? ???? ???? ? ??? ?? ???? ?? ?????.

---

MySQL ???? ??????

???? ????? ?? ???? ??? ? MySQL? ?? ???? ??(?? "???")?? SQL ? ?????. ???? ?? ? ??? ? ????.

• INSERT: ?? ?? ?? ??????.
• UPDATE: ???? ?? ?? ??????.
• DELETE: ?? ?? ?? ??????.

??? ?? ?? ?? ????? ???? ??? ? ???? ??? ?? ??? ???? ??? ? ????.

??? ?? ??

MySQL?? ???? ???? ???? ??? ??? ????.

CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name
    FOR EACH ROW
    trigger_body;

• Trigger_name: ???? ?????.
• Trigger_time: ??? ?? ?? ??? ? ????.
• Trigger_event: INSERT, UPDATE ?? DELETE? ? ????.
• table_name: ???? ??? ???? ?????.
• Trigger_body: ???? ??? ? ??? SQL ????.

??? ???: BEFORE vs. AFTER

• ??? ?: ??? ??? ??? ??? ??(?: ??, ???? ?? ?? ?? ?)? ?????. ?? ?? ???? ???? ???? ?? ??? ? ????.

• AFTER Trigger: ??? ??? ??? ??(?: ???? ??, ???? ?? ??? ?) ?????. ?? ??? ?? ??? ?? ??? ?? ??? ????? ??? ?????.

---

MySQL ???? ?

1. ?? ??? ??: ??? ?? ??

created_at ?? ???? ?? ??? ??? ????? ???? ??? ??? ?????. AFTER INSERT ???? ???? ? ???? ??? ? Create_at ??? ?? ?????? ???? ??? ? ????.

CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name
    FOR EACH ROW
    trigger_body;

? ????:

• ?? ????? AFTER INSERT ???? ?????.
• NEW ???? ?? ??? ?? ???? ? ?????. NEW.id? ?? ??? ?? ID? ?????.
• ???? ?? ??? ???? Created_at ?? ?? ?????? ???????.

2. ???? ??? ?: ????? ?? ???? ??

?? ? ?? ?? ?? ?? ???? ??? ???. ??? ????? ??? ?? ?? ??? ???? ??????? ???.

CREATE TRIGGER set_created_at
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    UPDATE employees SET created_at = NOW() WHERE id = NEW.id;
END;

? ????:

• ?? ????? AFTER UPDATE ???? ?????.
• NEW.price? ????? ??? ????, OLD.price? ?? ?? ?????.
• ??? ??? ??(NEW.price <> OLD.price) ???? ? ??? ??? ???? ?? ??? ???????.

3. ?? ??? ??: ?? ??? ?? ??

???? ???? ?? ? ?? ??? ?? ???? ??? ??? ? ????. ?? ?? ?? ????? ?? ???? ??? ??? ???? ??? ? ? ????.

CREATE TRIGGER update_discounted_price
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        UPDATE products SET discounted_price = NEW.price * (1 - NEW.discount / 100) WHERE id = NEW.id;
    END IF;
END;




<p>? ????:</p>

<ul>
<li>?? ????? <strong>?? ?</strong> ???? ?????.</li>
<li>OLD ???? ???? ? ?? ???? ?????.</li>
<li>is_tical ?? 1? ??? ?? ???? SIGNAL ?? ???? ??? ???? ??? ?????.</li>
</ul>

<h4>
  
  
  4. <strong>??? ?? ??: ?? ?? ??</strong>
</h4>

<p>???? ??? ??? ?? ?? ??? ???? ? ?? ???? ??? ? ????. ??? ?? ???? ??? ?? ? ??? audit_log ???? ???? ????.<br>
</p>

<pre class="brush:php;toolbar:false">CREATE TRIGGER prevent_delete_critical_employee
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.is_critical = 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete critical employee';
    END IF;
END;

? ????:

• ?? ????? AFTER INSERT ???? ?????.
• ???? audit_log ???? ???? ???? ??(INSERT), ??? ??(employees), ??? ??? ID(NEW.id) ? ?????(NOW())? ?????.

??? ??

• ??? ??: information_schema.triggers ???? ???? ??????? ?? ???? ? ? ????.

CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name
    FOR EACH ROW
    trigger_body;

• ??? ??: ???? ? ?? ???? ?? ?? DROP TRIGGER ?? ???? ??? ? ????.

CREATE TRIGGER set_created_at
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    UPDATE employees SET created_at = NOW() WHERE id = NEW.id;
END;

??? ?? ? ?? ??

1. ??? ??? ??: ???? ?? SQL ?? ????? ?????? ??? ????? ?????. ?? ??? ????? ??? ??? ?? ??? ??? ??? ???? ??? ????.

2. ??? ??: ??? ???? ???? ???? ?????. ??? ?? ?? ??? ??? ??? ??? ??? ? ????. MySQL? ???? ???? ?? ???? ?? ???? ????(?? ???).

3. ??? ???: ???? ??? ?? ??? ???? ? ??? ???? ???? ? ????? ????? ??? ? ????. ?? ??? ??? ? ????? ??? ?????.

4. ??? ???: ???? ?? ???? ?????? ???? ???? ??? ??? ?? ? ???? ????? ???? ?? ?? ?? ?? ???? ???? ???? ??? ??????.

??

MySQL ???? ??, ????, ?? ? ?????? ??? ?? ???? ??? ????? ??? ?????. ???? ???? ???? ??? ???? ??? ???? ???? ?? ?? ??? ?? ??? ???? ? ????. ??? ??? ??? ??? ???? ???? ??? ??? ???? ??? ??? ??? ???? ???? ???? ?? ?????.

---

? ??? ??? ?? ???? MySQL ???: ?????? ?? ???? ?? ?????. ??? ??? PHP ??? ????? ?? ?? ??? ?????!