DROP TRIGGER IF EXISTS `remove_products_trig`;
DELIMITER ;;
CREATE TRIGGER `remove_products_trig`
AFTER UPDATE ON `products`
FOR EACH ROW 
BEGIN
    DECLARE user_type_var INT;
    IF OLD.archived = false AND NEW.archived = true AND OLD.archived_by IS NULL AND NEW.archived_by IS NOT NULL THEN
        SELECT type.user_type INTO user_type_var FROM user_type_t AS type INNER JOIN users AS user ON user.type = type.user_type WHERE user.id = NEW.archived_by;
        INSERT INTO `audit_trail` (`action_type`,`user_id`,`user_type`,`action_timestamp`) VALUES (14, NEW.archived_by, user_type_var, NOW());
        IF OLD.user_id != NEW.archived_by THEN
            INSERT INTO `notifications` (`notification_type`,`user_id`,`product_id`,`timestamp`) VALUES (2, NEW.user_id, NEW.id, NOW());
        END IF;
    END IF;
END;;
DELIMITER ;