Hi,

Thanks.  

It is very useful for me. but another thing is if the table B have a few
factor in one item, is it possible to use this to convert to 'KG'.  for
eg example the Table B as this:


Table B
Item		uom		uom2		factor
Item1		bottle	kg		10
Item1		box		bottle	12
Item1		carton	box		6
Item2		box		kg		0.3
Item2		carton	box		40
Item3
.
.
.
.


-----Original Message-----
From: Ali Karimi [mailto:a97alika@s...] 
Sent: Friday, July 26, 2002 5:00 AM
To: sql language
Subject: [sql_language] SV: RE: SV: RE: SV: RE: SV: SV: RE: SV:

Hi!
Below is the TRIGGER that will solve your problem.
This trigger will fire each time there is an intention
on inserting a new tupple/row in TABLE_A (the transaction table).
This is an "INSTEAD OF TRIGGER". It will check if there should be
any conversion on the uom or there should not be any changes (i.e.
if the uom is already "kg"). After the conversion (i.e. if that was
needed) it will insert into TABLE_A.

OBSERVE!
########

I did not spend much time on this solution :o) so the performance
has not been in consideration. Like i said above, this TRIGGER will
fire when a new insertion is to take place on TABLE_A. I tested this
with a "BATCH-INSERTION" of 36500 and it took 5min and 30sec!

For tuning this, you should try to find a solution for NOT needing to
query TABLE_B so "many times" for each condition. (That would be the
place to work on, for better performance).

ALSO... if you already have data stored in TABLE_A that need to be
changed/need conversion (that will accur when insertion is made on
TABLE_A and there is still no corresponding row in TABLE_B (the
conversion table) OR you already have data stored on TABLE_A before
you started using this trigger), then you should create a script for
updating those tupples. That can be done in different ways, e.g. 
creating a script and config it as a job, OR creating another trigger
but this time on TABLE_B; this trigger will look for matching rows in
TABLE_A (i.e items in TABLE_A that match the new item in TABLE_B), OR
You can do this manually ;o). Indeed, no matter what solution, the 
script for solving this will be the same (more or less).

2 TIPS:
#######

A)I would create another column in TABLE_A for marking each tupple/row
that are alreay been changes (converted). That will make you life much
easier (and faster ;o)) when its time to look for tupples in TABLE_A
that could not be handled by "update_table_A" TRIGGER (the solution is
discussed onther "OBSERVE"). Also create a SORTING INDEX on that
attribute/column (here you should also consider how often the table is
updated and how often its queried, before deciding to create an index)

B)Consider if you can change your design. Maybe you should consider to
look
at noramlization.

SOME LAST THOUGHTS:
###################

* You can also use a similar SP instead of using a trigger (BUT i
recommend
  TRIGGER for the INTEGRITY of the system)!
* I STILL DO NOT KNOW if you have any primary key on your tables.
  Anyway, i did create one for each table when i was testing.


THE TRIGGER:
############

USE TEST_DB
GO

DROP TRIGGER update_table_A
GO

CREATE TRIGGER update_Table_A ON TABLE_A
INSTEAD OF INSERT
AS
BEGIN

	/* Check if the uom for the inserted tupple/row (by the client)
	   is equal to kg. If that's the case, then start searching
	   for the correct "conversion algorithm". Otherwise (because
the data is
	   already in kg). skip this and store the data as it is in
TABLE_A. */

	IF ((SELECT uom from inserted)<>'kg')
	BEGIN
		/* The "inserted table" IS NOT POSSIBLE to update
		   (in some other DBMS this is possible; e.g. INTERBASE)
so we will
		   move the the tupple stored in inserted table to a
temp table for 
		   manipulation. */
		CREATE TABLE #TEMP (
					item	VARCHAR(10)	NOT
NULL,
					uom	VARCHAR(10)	NOT
NULL,
					qty	FLOAT		NOT
NULL)

		INSERT INTO #TEMP SELECT Item, uom, qty FROM inserted

		/* Here we will look for the correct conversion on
#TEMP.qty. 
		   COALESCE() is used for solving this problem. If there
is no match
		   in TABLE_B for the new Item, then the #TEMP.qty will
not be
		   changed. The data will be inserted as it was from the
beginning,
		   without any change (this is accomplished by given
#TEMP.qty as the
		   LAST argument for COALESCE. If no match is found then
#TEMP.qty
		   will be equal to #TEMP.qty (i.e. no changes). */

		UPDATE #TEMP
		SET #TEMP.qty = 
			COALESCE((SELECT #TEMP.qty * TABLE_B.factor
					FROM TABLE_B
					WHERE #TEMP.item = TABLE_B.item
AND
	
#TEMP.uom = TABLE_B.uom1),
				   (SELECT #TEMP.qty / TABLE_B.factor
					FROM TABLE_B
					WHERE #TEMP.item = TABLE_B.item
AND
	
#TEMP.uom = TABLE_B.uom2),
				   #TEMP.qty)

		/* It's time to store the updated data in TABLE_A */
		INSERT INTO TABLE_A (item, uom, qty) SELECT item, uom,
qty FROM #TEMP
	END
	ELSE
	BEGIN
		/* If the uom is already in kg then do not make any
changes, just
		   insert into TABLE_A */
		INSERT INTO TABLE_A (item, uom, qty) SELECT item, uom,
qty FROM inserted
	END
END
GO



Really hope that this will solve your problem as you wanted to do.
Regards 
/ Ali Karimi