Need help with SQL trigger :)

[altalus]

Hello !

I want to allow my users to cancel a sale manually and it will also allow a bot to check for old sales. Now I created a trigger but something is wrong with it and I'm not quite sure what... any help ?

Code: Select all

DELIMITER $$

USE `dspdb`$$

DROP TRIGGER /*!50032 IF EXISTS */ `auction_house_cancel`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `auction_house_cancel` BEFORE DELETE ON `auction_house` 
    FOR EACH ROW BEGIN
	DECLARE num_items INTEGER DEFAULT 1;
        IF OLD.stack = 1 THEN
	    SET num_items = 12; /* Probably wrong, not all stacks are x12, some x99 */
	END IF;
	IF OLD.sale = 0 THEN INSERT INTO delivery_box VALUES (OLD.seller, OLD.seller_name, 1, 0, OLD.itemid, 0, num_items, 0, 'AH-Jeuno'); END IF;
END;
$$

DELIMITER ;

[altalus]

Fixed it, but posting solution here in case it helps someone else:

Code: Select all

DELIMITER $$

USE `dspdb`$$

DROP TRIGGER /*!50032 IF EXISTS */ `auction_house_cancel`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `auction_house_cancel` BEFORE DELETE ON `auction_house` 
    FOR EACH ROW BEGIN
	DECLARE num_items INTEGER DEFAULT 1;
        IF OLD.stack = 1 THEN
	    SET num_items = 12;
	END IF;
	IF OLD.sale = 0 THEN INSERT INTO delivery_box (charid, charname, box, itemid, itemsubid, quantity, senderid, sender) VALUES (OLD.seller, OLD.seller_name, 1, OLD.itemid, 0, num_items, 0, 'AH-Jeuno'); END IF;
END;
$$

DELIMITER ;