I have a simple INSERT query that is run on event click of a button, this gets variables and inserts them into `order` table.
I also would like to UPDATE my bands table to reduce the stock by 1 until it reaches 0 then show null or sold out rather than go into negative eg: -6
to know what band is selected I can use :
WHERE Band_id = $Band_id");
in the same statement.
the current INSERT looks like :
"INSERT INTO orders (band_id,user_id,user_name,band_name,band_venue) VALUES('$Band_id', '$user_id', '$user_name', '$name', '$venue')";
**Conclusion**
Need to alter my INSERT to also UPDATE WHILE checking that its the current selected band.
**ANSWER PROVIDED CHECK**
this solution has been wonderfully provided and it makes sense but im not quite skilled enough to understand why its not functioning correctly.
$sql = "INSERT INTO orders (band_id,user_id,user_name,band_name,band_venue) VALUES('$Band_id', '$user_id', '$user_name', '$name', '$venue')";
$sql_create ="CREATE TRIGGER tg_ai_orders
AFTER INSERT ON orders
FOR EACH ROW
UPDATE bands
SET stock = stock - 1
WHERE band_id = NEW.band_id";
$sql_deleat = "CREATE TRIGGER tg_ad_orders
AFTER DELETE ON orders
FOR EACH ROW
UPDATE bands
SET stock = stock + 1
WHERE band_id = OLD.band_id";
mysql_query ($sql, $sql_deleat, $sql_create, $linkme)
or die ("could not add to database");