Trigger

[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));

[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. ?????

[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?

[jbennet]

keep it organised!

[peter_budo]

@Fenerbahce 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

[Fenerbahce]

sorry