MySQL Trigger | MySQL Before/After Insert Trigger | MySQL Before/After Update Trigger | MySQL Before/After Delete Trigger

MySQL Trigger | MySQL Before/After Insert Trigger | MySQL Before/After Update Trigger | MySQL Before/After Delete Trigger:

DROP TRIGGER IF EXISTS `example_table_before_insert`;
DELIMITER $$
CREATE TRIGGER `example_table_before_insert`
BEFORE INSERT ON `example_table` FOR EACH ROW BEGIN
  DECLARE ready INT DEFAULT 0;
  DECLARE rnd_str TEXT;
  IF NEW.CODE IS NULL OR CHAR_LENGTH(NEW.CODE) = 0 THEN
    WHILE NOT ready DO
      SET rnd_str := UPPER(SUBSTR(MD5(CONCAT(rand(), now())), 1, 5));
      IF NOT exists(SELECT * FROM example_table WHERE CODE = rnd_str) THEN
        SET NEW.CODE = rnd_str;
        SET ready := 1;
      END IF;
    END WHILE;
  END IF;
END
$$
DELIMITER ;

DROP TRIGGER IF EXISTS `example_table_before_update`;
DELIMITER $$
CREATE TRIGGER `example_table_before_update`
BEFORE UPDATE ON `example_table` FOR EACH ROW BEGIN
  DECLARE ready INT DEFAULT 0;
  DECLARE rnd_str TEXT;
  IF NEW.CODE IS NULL OR CHAR_LENGTH(NEW.CODE) = 0 THEN
    WHILE NOT ready DO
      SET rnd_str := UPPER(SUBSTR(MD5(CONCAT(rand(), now())), 1, 5));
      IF NOT exists(SELECT * FROM example_table WHERE CODE = rnd_str) THEN
        SET NEW.CODE = rnd_str;
        SET ready := 1;
      END IF;
    END WHILE;
  END IF;
  SET NEW.updated_at = now();
END
$$
DELIMITER ;

DROP TRIGGER IF EXISTS `example_table_after_delete`;
DELIMITER $$
CREATE TRIGGER `example_table_after_delete`
AFTER DELETE ON `example_table` FOR EACH ROW BEGIN
  DELETE FROM example_table_associations WHERE example_id=OLD.id;
END
$$
DELIMITER ;

Create a query for MYSQL Trigger

First i have 2 tables named "item" and "buy_item" //the "stock" column is in item table and the "qty" column is ini buy_item table then I have SQL SERVER query to create a trigger like this

CREATE TRIGGER trigger1
ON dbo.buy_item
FOR UPDATE
AS begin
UPDATE item SET stock = stock - qty FROM deleted WHERE item.id = deleted.id
UPDATE item SET stock = stock + qty FROM inserted WHERE item.id = deleted.id
end

I need help to create the same function of this query in MYSQL query and i already do this

CREATE TRIGGER trigger1
BEFORE UPDATE ON buy_item
FOR EACH ROW
BEGIN
UPDATE item SET stock = stock - buy_item.qty WHERE item.id=buy_item.id
UPDATE item SET stock = stock + NEW.qty WHERE item.id=buy_item.id
END

but this isn't work at all, it says the syntax is wrong

maybe anyone can help about this

Thanks before

---

Assuming that you can't change item id in buy_item your trigger in MySql should look like this

CREATE TRIGGER trigger1
AFTER UPDATE ON buy_item
FOR EACH ROW
  UPDATE item
     SET stock = stock + NEW.qty - OLD.qty
   WHERE id = NEW.id;

Here is SQLFiddle demo

MySQL: UPDATE trigger. Get the value of a column used in the UPDATE where clause if it does not match any rows?

MySQL: In update trigger's body, can I obtain the value of a column that is specified in the where clause of the triggering query if the where clause does not match any rows at all?

I have to do the following, but NOT USING direct query such as ON DUPLICATE KEY UPDATE so on:

If I have:

UPDATE my_table SET idiotism_level=5 WHERE name='Pencho'

... and the where clause match NO ROWS, I'd want to automatically trigger an insertion of a row having name='Pencho' before the update, and then the UPDATE would presumably match, and work properly.

Is it possible ?

---

This could be make in a RULE in other database systems (PostgreSQL), that does not exists in MySQL. It's a Rule and not a trigger as you should analyse the query and not the result of the query.

But for MySQL you can make pre-query jobs by using MySQL-Proxy. You should be able to alter your update query and build an insert, By running some 'check row exists' extra query from the MySQL-Proxy (I'm not saying this is a nice solution, but if you have no way to make the code to act better you can fix it at this level).