Hi,
You might want to check-out StepSqlite PL/SQL compiler for SQLite at
http://www.metatranz.com/stepsqlite
Using it you can write the trigger (as part of a package body) almost the
way you wrote in original post. StepSqlite compiles the PL/SQL code to a
linux x86 shared library which can be linked in to your C++ app and used as
the database interface to SQLite. There is an easy-to-follow tutorial here:
http://www.metatranz.com/stepsqlite/tutorial.html
*If you do not use C++*, wait till StepSqlite adds support for creating a
loadable SQLite extension for SQLite and then you can simply load the
compiled shared library into your SQLite database.
(NOTE: below given is not pseudo code - this is actual code which you need
to write and leave the rest to StepSqlite):
===
CREATE TABLE stock_tab(stock_id NUMBER(5), prod_batch_code NUMBER(5),
stock_qty NUMBER(5), stock_date date);
CREATE TABLE purchase_tab(product_batch_code NUMBER(5), purchase_qty
NUMBER(5));
PACKAGE BODY MyDBinterface IS
BEGIN
CREATE TRIGGER insert_stock_from_product
AFTER INSERT ON purchase_tab
DECLARE
count NUMBER(5);
BEGIN
SELECT count(prod_batch_code) INTO count FROM stock_tab WHERE
prod_batch_code= :new.product_batch_code;
IF count > 0 THEN
UPDATE stock_tab
SET stock_qty = stock_qty + :new.purchase_qty
WHERE prod_batch_code= :new.product_batch_code
ELSE
INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
stock_date)
VALUES (20009, :new.product_batch_code, :new.purchase_qty,
sysdate() );
END IF;
END;
END;
===
-SK
On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew <[email protected]>wrote:
>
> guys i ll clarify the problem
> this is the purchase table here purchase id is PK
>
> purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date
> ----------- --------------- ---------------- ------------
> -------------------
> 1 1000 10000 100 2009-05-26
> 18:19:27
> 2 1001 10000 100 2009-05-26
> 18:19:31
> 3 1002 10000 100 2009-05-26
> 18:19:35
> 4 1003 10000 100 2009-05-26
> 18:19:49
>
> this is the stock table here stock_id is PK and prod_batch_code is FK
>
> stock_id prod_batch_code stock_qty stock_date
> ---------- --------------- ---------- -------------------
> 20001 1001 105 2009-05-26 18:19:27
> 20002 1002 100ps 2009-05-26 18:19:31
> 20003 1003 100ps 2009-05-26 18:19:35
> 20004 1003 100ps 2009-05-26 18:19:43
> 20005 1002 100ps 2009-05-26 18:19:44
> 20006 1001 100ps 2009-05-26 18:19:49
> 20007 1000 85 2009-05-26 18:19:50
> 20008 1000 85 2009-05-26 18:19:51
>
> i wrote a trigger
> CREATE TRIGGER insert_stock_from_product
> AFTER INSERT ON purchase_tab
> BEGIN
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date)
> values (new.purchase_id+20000, new.prod_batch_code,
> new.purchase_qty,
> new.purchase_date );
> END;
>
> instead of inserting the same products repeatedly in the stock table i jus
> want the quantity as well as the dates to be updated . and wen i insert a
> new product_batch_code to the purchase table its shuld be inserted in the
> stock table also...
>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
