Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 :

PHP
WHERE Band_id = $Band_id");


in the same statement.

the current INSERT looks like :

PHP
"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.

PHP
$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");
Posted

1 solution

im not sure but try this before all of your triggers :


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

DROP TRIGGER /*!50032 IF EXISTS */ `tg_ad_orders`$$
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900