Here are my tables and query. I always end up inserting new row but not updating/replacing current data. I hope you guys can figure out what I have done wrong.
CREATE TABLE [imf_products] (
[imfprod_id] integer NOT NULL
, [imf_id] int NOT NULL
, [company_id] int NOT NULL
, [product_id] int NOT NULL
, [color_id] int NOT NULL
, [size_id] int NOT NULL
, [imf_type] nvarchar(100) NOT NULL
, [date] datetime NOT NULL
, price nvarchar(100) not null
, [comment] nvarchar(100) NULL
, quantity int not null
, CONSTRAINT [sqlite_master_PK_imfprod] PRIMARY KEY ([imfprod_id])
);
CREATE TABLE [stocks] (
[stocks_id] integer NOT NULL
, [product_id] int NOT NULL
, [color_id] int NULL
, [size_id] int NULL
, [price] int NULL
, [quantity] int NULL
, [status] nvarchar(100) NULL
, CONSTRAINT [sqlite_master_PK_product] PRIMARY KEY ([stocks_id])
);
CREATE TRIGGER add_stock9 AFTER INSERT ON imf_products
FOR EACH ROW
when new.imf_type='Inv-IN'
BEGIN
INSERT OR REPLACE INTO stocks (color_id, size_id, price, quantity, product_id) values (new.color_id, new.size_id,
new.price, new.quantity,(select * from stocks where product_id=new.product_id));
END;
insert into imf_products (imf_id, company_id, product_id, color_id, size_id, imf_type, date, price, quantity) values
(2,2,9,3,4,'Inv-IN',('now'),100,6);
Also If you can help me add old quantity with new one.
Aucun commentaire:
Enregistrer un commentaire