How do i create an INSERT trigger that based on a condition, either updates or inserts into another table?

Question

I'm trying to create a trigger in phpmyadmin and I have 2 tables, Items and Inventory. This is for a Library Database. When i insert a new item, if the items isbn already exists in the inventory, i want to add to the totalCopies of that item in the inventory. If not, i want it to insert into inventory a new row with the new items isbn. This is my first time using triggers and I'm getting syntax errors. This is what i have right now.

BEGIN
IF ((SELECT COUNT(*) FROM inventory WHERE inventory.isbn = NEW.isbn) > 0) THEN
(
    UPDATE inventory
    SET inventory.totalCopies = inventory.totalCopies + 1 
    WHERE inventory.isbn = NEW.isbn
    SET inventory.totalAvailable = inventory.totalAvailable + 1
    WHERE inventory.isbn = NEW.isbn
    )
ELSE
INSERT INTO inventory VALUES( , NEW.isbn, 1, 1, 0)
END

The inventory columns are : inventoryID (PrimaryKey)(autoincrement), isbn, totalCopies, totalAvailable, totalCheckedOut.

Answer 1

I assume for this trigger that inventory id is an auto_increment value, or else the insert gives an error.

the UPDATE query can be a little simplifoed

DELIMITER $$

CREATE TRIGGER insorup
AFTER INSERT
ON ORDERS FOR EACH ROW
BEGIN
    IF (EXISTS(SELECT 1 FROM inventory WHERE inventory.isbn = NEW.isbn) ) THEN
        UPDATE inventory
           SET inventory.totalCopies = inventory.totalCopies + 1 ,
           inventory.totalAvailable = inventory.totalAvailable + 1
        WHERE inventory.isbn = NEW.isbn;
    ELSE
        INSERT INTO inventory VALUES ( NEW.isbn, 1, 1, 0);
    END IF;
END$$

DELIMITER ;

as you see in the comment, Shadow pointed out that it is also possible to reduce the inner Part. When you have the column ISBN declared as UNIQUE

DELIMITER $$

CREATE TRIGGER insorup
AFTER INSERT
ON ORDERS FOR EACH ROW
BEGIN

    INSERT INTO 
       inventory VALUES ( NEW.isbn, 1, 1, 0)
    ON DUPLICATE KEY UPDATE inventory.totalCopies = inventory.totalCopies + 1 ,
               inventory.totalAvailable = inventory.totalAvailable + 1;
END$$

DELIMITER ;