June 15, 2009 at 3:43 am
Dear All,
I need some help making a triggers/function. this is the situation:
i have 4 tables:
- article
- articlenr, descr
- receipt
- receiptnr
- articlenr (fk from table article)
- amount
- consumed
- consumednr
- articlenr (fk from table article)
- amount
- stock
- articlenr (fk from table article)
- amount
This has to be happen:
when inserting a article into the table 'receipt', the table 'stock' has to be updated for each article:
amount of stock = sum of all receipts - sum of all consumed
I hope you guys can help me.
Thanks.
June 15, 2009 at 3:51 am
Please post DDL scripts for tables with sample data and desired output.
Here is help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 15, 2009 at 4:00 am
Since I guess your problem is not how to create a trigger but the UPDATE statement. Try this example:
DECLARE @articles TABLE (ArticleNr INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, article VARCHAR(30))
DECLARE @receipt TABLE (ReceiptNr INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, ArticleNr INT NOT NULL, Amount INT)
DECLARE @consumed TABLE (ConsumedNr INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, ArticleNr INT NOT NULL, Amount INT)
DECLARE @Stock TABLE (StockNr INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, ArticleNr INT NOT NULL, Amount INT)
INSERT INTO @articles
SELECT 'Woll socks'
INSERT INTO @receipt
SELECT 1, 100
INSERT INTO @consumed
SELECT 1, 50
INSERT INTO @Stock
SELECT 1, 0
; WITH
rec AS
(
SELECT ArticleNr, SUM(Amount) Amount FROM @receipt GROUP BY ArticleNr
),
cons AS
(
SELECT ArticleNr, SUM(Amount) Amount FROM @consumed GROUP BY ArticleNr
)
UPDATE s SET
s.Amount = rec.Amount - cons.Amount
FROM @Stock s
JOIN rec ON s.ArticleNr = rec.ArticleNr
JOIN cons ON s.ArticleNr = cons.ArticleNr
SELECT * FROM @Stock
For future posts provide DDL and sample data, please. As ps wrote.
Flo
June 15, 2009 at 4:29 am
Dear ps,
Thank you for response.
Here some more about it:
table article:
CREATE TABLE [dbo].[article](
[oarticlenr] [int] NOT NULL,
[odescr] [varchar](500) COLLATE Latin1_General_CI_AS NULL,
[odate] [datetime] NULL CONSTRAINT [DF_article_odate] DEFAULT (getdate()),
CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED
(
[oarticlenr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table receipt:
CREATE TABLE [dbo].[receipt](
[oreceiptnr] [int] NULL,
[oarticlenr] [int] NULL,
[oamount] [nchar](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[receipt] WITH CHECK ADD CONSTRAINT [FK_receipt_article] FOREIGN KEY([oarticlenr])
REFERENCES [dbo].[article] ([oarticlenr])
GO
ALTER TABLE [dbo].[receipt] CHECK CONSTRAINT [FK_receipt_article]
GO
ALTER TABLE [dbo].[receipt] WITH CHECK ADD CONSTRAINT [FK_receipt_receipt] FOREIGN KEY([oreceiptnr])
REFERENCES [dbo].[receipt] ([oreceiptnr])
GO
ALTER TABLE [dbo].[receipt] CHECK CONSTRAINT [FK_receipt_receipt]
GO
ALTER TABLE [dbo].[receipt] WITH CHECK ADD CONSTRAINT [CK_receipt] CHECK (([oamount]>=NULL))
GO
ALTER TABLE [dbo].[receipt] CHECK CONSTRAINT [CK_receipt]
table consumed:
CREATE TABLE [dbo].[consumed](
[oconsumednr] [int] NULL,
[oarticlenr] [int] NULL,
[oamount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[consumed] WITH CHECK ADD CONSTRAINT [FK_consumed_article] FOREIGN KEY([oarticlenr])
REFERENCES [dbo].[article] ([oarticlenr])
GO
ALTER TABLE [dbo].[consumed] CHECK CONSTRAINT [FK_consumed_article]
GO
ALTER TABLE [dbo].[consumed] WITH CHECK ADD CONSTRAINT [FK_consumed_uitgifte] FOREIGN KEY([ouitgiftenr])
REFERENCES [dbo].[uitgifte] ([ouitgiftenr])
GO
ALTER TABLE [dbo].[consumed] CHECK CONSTRAINT [FK_consumed_uitgifte]
GO
ALTER TABLE [dbo].[consumed] WITH CHECK ADD CONSTRAINT [CK_consumed] CHECK (([oamount]>=NULL))
GO
ALTER TABLE [dbo].[consumed] CHECK CONSTRAINT [CK_consumed]
table stock:
CREATE TABLE [dbo].[stock](
[oarticlenr] [int] NULL,
[oamount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[stock] WITH CHECK ADD CONSTRAINT [FK_stock_article] FOREIGN KEY([oarticlenr])
REFERENCES [dbo].[article] ([oarticlenr])
GO
ALTER TABLE [dbo].[stock] CHECK CONSTRAINT [FK_stock_article]
---------------------------
There are no data now, bud this had to be done:
ofter inserting a new record into the table 'receipt', table 'stock'(this table has always all article numbers) most be update too:
stock.oamount = sum receipt.oamount - sum consumed.oamuont
I hope it's clear now,
Thanks.
June 15, 2009 at 5:02 am
i removed all the constraints from the definitions u posted.
---=====================
CREATE TABLE [dbo].[article](
[oarticlenr] [int] NOT NULL,
[odescr] [varchar](500) COLLATE Latin1_General_CI_AS NULL,
[odate] [datetime] NULL CONSTRAINT [DF_article_odate] DEFAULT (getdate()))
---====================
CREATE TABLE [dbo].[receipt](
[oreceiptnr] [int] NULL,
[oarticlenr] [int] NULL,
[oamount] [nchar](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
--========================
CREATE TABLE [dbo].[consumed](
[oconsumednr] [int] NULL,
[oarticlenr] [int] NULL,
[oamount] [int] NULL
) ON [PRIMARY]
GO
--============================
CREATE TABLE [dbo].[stock](
[oarticlenr] [int] NULL,
[oamount] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO STOCK VALUES(1,100)
INSERT INTO STOCK VALUES(2,100)
INSERT INTO STOCK VALUES(3,100)
INSERT INTO STOCK VALUES(4,100)
INSERT INTO STOCK VALUES(5,100)
CREATE TRIGGER TRG_RECEIPT ON RECEIPT FOR INSERT
AS
UPDATE STOCK SET STOCK.OAMOUNT= STOCK.OAMOUNT-INSERTED.OAMOUNT
FROM INSERTED
INNER JOIN STOCK ON (INSERTED.oarticlenr = STOCK.OARTICLENR)
INSERT INTO RECEIPT VALUES(1,1,10)
SELECT * FROM RECEIPT
SELECT * FROM STOCK
OUTPUT:-
--------------
oreceiptnr oarticlenr oamount
----------- ----------- ----------
1 1 10
(1 row(s) affected)
oarticlenr oamount
----------- -----------
1 90
2 100
3 100
4 100
5 100
(5 row(s) affected)
---==================================
1. i assumed the moment u enter details of an article in reciepts table, the oamount column from the stock table should be reduced by as much amount.
2. i didnt take consumed table into consideration. may be u can modify the trigger to suit ur requirement.
Let us know if this is close to what u want?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply