Member Avatar for Fenerbahce

Hi,

When i add new product into "Products" table, a trigger will add a record into "stocks" table. Example; stock=10,productId=99

How can i do that? Thanks

CREATE TABLE products(
productId INT(10) NOT NULL AUTO_INCREMENT, 
productName VARCHAR(10),
PRIMARY KEY (productId));

CREATE TABLE stocks(
stock INT(10), 
productId INT(10),
FOREIGN KEY (productId) REFERENCES products(productId));
  • 3 Contributors
  • 5 Replies
  • 119 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Fenerbahce

Dani AI

Generated

Two problems appear in the thread: creating a stocks row that references the auto-incremented product id when a product is inserted, and getting syntax/logic errors in an UPDATE trigger. The safe, reliable pattern for the first is to use an AFTER INSERT trigger that uses NEW.productId (the auto-increment value is guaranteed after the row is written). For UPDATE logging, either set NEW columns in a BEFORE trigger (if the intention is to change the row being written) or compute values in an AFTER trigger and insert them into a history table.

Common causes of errors seen in the posts:

  • Missing delimiter handling and BEGIN...END for multi-statement triggers (the parser stops at the first semicolon).
  • Wrong attempt to fetch values from the same table with ad-hoc SET/SELECT instead of using NEW/OLD or SELECT ... INTO.
  • Confusing BEFORE vs AFTER: auto-increment id is not reliable in BEFORE INSERT; NEW can be modified only in BEFORE triggers; AFTER triggers cannot change NEW.
  • Foreign key failures when the storage engine is not InnoDB (check with SHOW CREATE TABLE).

Example patterns (adjust names and column lists to match the actual schema):

DELIMITER $$
CREATE TRIGGER trg_products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
  INSERT INTO stocks (productId, stock)
  VALUES (NEW.productId, 10);
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER trg_product_after_update
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
  DECLARE delta INT;
  SET delta = NEW.amount - OLD.amount;

  INSERT INTO detail
    (id, oldAmount, newAmount, girdi, cikti, kalan)
  VALUES
    (OLD.id,
     OLD.amount,
     NEW.amount,
     CASE WHEN delta > 0 THEN delta ELSE 0 END,
     CASE WHEN delta < 0 THEN -delta ELSE 0 END,
     NEW.amount);
END$$
DELIMITER ;

Notes tied to the thread: ’s attempts used SELECT-like constructs where NEW/OLD should be used. ’s advice about keeping posts organised is relevant — keeping one clear example per post helps diagnosis. For precise syntax and trigger restrictions see the MySQL manual on triggers: MySQL Trigger documentation.

Recommended Answers

All 5 Replies

Member Avatar for Fenerbahce 
CREATE TRIGGER update_detail BEFORE UPDATE ON product
FOR EACH ROW

SET @id = select id from product;
SET @remain = select amount from product;

insert into detail (id,remain) values (@id ,@remain );

Above code doesn't work. It says "id" cannot be null. Either SET doesn't set or insert line has problem. ?????

Member Avatar for Fenerbahce 
CREATE TRIGGER update_detail 
BEFORE UPDATE ON product
FOR EACH ROW

     IF OLD.amount<NEW.amount THEN
         SET NEW.girdi = NEW.amount - OLD.amount ;
     ELSEIF OLD.amount>NEW.amount THEN
         SET NEW.cikti = OLD.amount - NEW.amount ;
     END IF;

INSERT INTO DETAIL 
(id, oldAmount, newAmount, girdi, cikti, kalan) 
VALUES 
(OLD.id, OLD.amount, NEW.amount, NEW.girdi, NEW.cikti, NEW.amount);

Error is on this line :SET NEW.girdi = NEW.amount - OLD.amount ;
What is wrong?

Member Avatar for jbennet

keep it organised!

Member Avatar for peter_budo

please take your time to read forum policies as you are creating havoc around. Once you created post and your next question is related to original post just post new comment bellow the original post

Member Avatar for Fenerbahce

sorry

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.