Trigger for update columns on other table needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger for update columns on other table needed

Author

Topic

vanpersie
Starting Member

3 Posts

Posted - 2011-05-06 : 15:37:09

Hi guys

I have a database involving the following table
Inventory TableItem_code int primary key,
Item_name varchar(50),
Item_cost money,
Imported_quantity numeric(18,0),
Item_Quantity numeric (18,0)'

Sale Table:-
item_code int foreign key references Inventory(item_code) ,
withdrawal_id foreign key References Withdrawal_Operations(withdrawal_id)
,quantity_Available_for_Sale numeric(18,0),
quantity_withdrawan numeric(18,0),
selling_price money,
quantity_sold numeric(18,0),
Sale_Operation_Date datetime
,
Item_cost money.

Withdrawal_Operations Table
Withdrawal_id int primary key,item_code foreign key references Inventory(item_code),
quantity_withdrawan numeric(18,0)

I need A trigger for the following operation
A user Want to transfer some quantity from the store(inventory table) to the Sale Table on the quantity_Available_for_Sale column , of course the quantity in the store(invetory table ) on the item_quantity column

column will decrease.

And the quantity on the sale table will be increase

the value quantity transfered is inserted on the quantity_withdrawan column.

I may need to add the quantity_withdrawan column to the Inventory Table to do this transaction.

My database diagram here
[url]http://img849.imageshack.us/img849/3900/salediagram.jpg[/url]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-06 : 19:42:28

Your DDL was pretty good! You almost used all the ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. But there are some problems.

You do not know what an attribute property is and how to name it. I am making guesses about withdrawn products since you told us nothing.

Everything is NULL-able, you actually used MONEY (Wow! Nobody does that any more) and your columns are insanely oversized garbage collection points. Your inventory is not based on any industry standards. Do you know why we use DECIMAL() and not NUMERIC(), even tho they are the same in T-SQL dialect? Oh, this is stolen from an ORACLE schema!!

Okay, let's first try to fix the DDL:

CREATE TABLE Inventory
(upc CHAR(13) NOT NULL PRIMARY KEY, ---industry standards!
item_name VARCHAR(50) NOT NULL,
item_cost DECIMAL (12,2) NOT NULL
CHECK(item_cost > 0.00),
imported_qty INTEGER NOT NULL
CHECK(imported_qty >= 0),
item_qty INTEGER NOT NULL
CHECK(item_qty >= 0));

CREATE TABLE Sales
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc) ,
withdrawal_upc <<no data type in narrative!!>>
REFERENCES Withdrawn_products (withdrawal_upc),
available_for_sale_qty INTEGER NOT NULL,
withdrawn_qty INTEGER NOT NULL,
sale_price DECIMAL (12,2) NOT NULL,
sale_qty INTEGER NOT NULL,
sale_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
item_cost DECIMAL (12,2) NOT NULL);

CREATE TABLE Withdrawn_Products
(withdrawn_upc CHAR(13) PRIMARY KEY,
REFERENCES Inventory(upc)
withdrawn_qty INTEGER NOT NULL);

>> need trigger for the following operation A user Want to transfer some quantity from the store(inventory table) to the Sale Table on the salable Sale_qty column, of course the quantity in the store (inventory table) on the item_qty column column will decrease. And the quantity on the sale table will be increase <<

Your whole approach is TOTALLY WRONG –-- let me repeat that in case you missed it -– TOTALLY WRONG. This is the way we did inventory in the 1950's with punch cards attached to physical units of inventory. A card would be moved by an inventory clerk from one deck to another. You mimic it with a trigger.

SQL programers use a status for an inventory item. This is so bad we cannot do it in a Forum. But you are inviting slow performance that will not be notched with the total lack of data integrity.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

vanpersie
Starting Member

3 Posts

Posted - 2011-05-06 : 23:00:57

Thank you for your valuable informations.
However my project is virtual and not for industry,And I need a trigger to perform my mentioned operation
I have created this trigger on Withdrawal_Operation Table.


ALTER  trigger [dbo].[tr_withdrawal_To_Sale] 
ON [dbo].[Withdrawal_Operations] FOR INSERT -- do we want to handle deletes and updates on the Import_to_Inventory table?
AS
 
 update Sal
 
set

quantity_Available_for_Sale = coalesce(Sal.quantity_withdrawan,0 )+  (quantity_Available_for_Sale), 
quantity_withdrawan =  coalesce(Sal.quantity_withdrawan ,0)+W.Wit  
from Sale Sal 
INNER JOIN (select  item_code,Withdrawal_id , sum(quantity_withdrawan) as Wit from inserted 
GROUP BY Withdrawal_id,item_code) as W on Sal.Withdrawal_id = W.Withdrawal_id

insert into Sale (item_code,Withdrawal_id ,quantity_withdrawan,quantity_Available_For_Sale) 
select item_code,Withdrawal_id ,sum(quantity_withdrawan),  sum(quantity_withdrawan)  
from inserted W GROUP BY Item_code,Withdrawal_id.

Withdrawal_Operations Table
Item_Code Withdrawal_id Quantity_withdrawan
6 18 1
6 19 1
Sale Table
Item_Code Withdrawal_id
quantity_withdrawan
quantity_available_for_sale
6 18 1 1
6 19 1 1
quantity_available_for_sale should be 2 by trigger supposed action.
please help.
thank you again for you valuable informations.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources