create somekind of LOG with proc

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

create somekind of LOG with proc

Author

Topic

sagitarius2k
Starting Member

5 Posts

Posted - 2011-11-20 : 04:24:01

okay, how do i create a proc to make a database that full log from other database??

here is a simple erd

[URL=http://imageshack.us/photo/my-images/526/41212450.png/]

[/URL]

Uploaded with [URL=http://imageshack.us]ImageShack.us[/URL]

Customer order contain
COid (PK)

Stuff contain
Stuff(PK)

so what i want is, how do i create a log that will automaticly write LOG stuff with identity as primary key??

here is the big picture of Stuff LOG

1|CO001|S001|24 July 2011|20 ( buying stock )|26 (last stock before purchase )
2|C0001|S002|24 July 2011|10|20
3|C0001|S003|24 July 2011|5|30
4|C0002|S002|24 July 2011|6|6

etc

why i making this, because with this log, Finance dept can decide to approve the request from warehouse to stocking stuff again, and to avoid fraud that cause by warehouse employee ( in database, still have 10 stock, but in real just 7 stock, so where the other?? )

sorry for bad english

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 06:17:28

where does other values come from? How are the tables related (ie by what columns)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sagitarius2k
Starting Member

5 Posts

Posted - 2011-11-20 : 09:20:31

Purchase order contain
COid ( PK ) = COxxx
Employeeid ( FK ) = from Employee ( not shown ) = EMxxx
Stuffid ( FK ) = Sxxx
etc atribute
quantity
input date

stuff contain
Stuffid ( PK ) = Sxxx
name
etc atribute
stock = 30
input date

in Log stuff, the related table is
Identity key ( PK ) = 1
COid = COxxx
Stuffid = Sxxx
date = taken from purchase order
stock while buying = taken from quantity at purchase order
stock before buying = taken from stock at stuff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 10:50:50

i think you should be creating a trigger on purchase order to do this on new inserts like


CREATE TRIGGER Trg_StockLog
ON PurchaseOrder
FOR INSERT 
AS
BEGIN
INSERT INTO Logstuff (COid,Stuffid,date,[stock while buying],[stock before buying])
SELECT i.COid,i.Stuffid,i.[input date],i.quantity,s.stock
FROM INSERTED i
INNER JOIN Stuff s
ON s.Stuffid = i.Stuffid
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sagitarius2k
Starting Member

5 Posts

Posted - 2011-11-20 : 11:51:59

thanks man, it work, but [stock before buying] is not working

i mean, [stock while buying] and [stock before buying] have calculation on that

for example

1|C0001|S002|24 July 2011|10|30

10 mean quantity amount he buy, while 30 is the last stock that record
so, if someone buy something again with same stuff (s002), the next log will be

1|C0002|S002|24 July 2011|5|20

now it 20 = ( 30(last stock at S002) - 10(quantity that customer buy from C0001)

and, i will make one more table between stuff and purchase order so the customer can buy more than one stuff

Purchase order contain
COid ( PK ) = COxxx
Employeeid ( FK ) = from Employee ( not shown ) = EMxxx
Stuffid ( FK ) = Sxxx
etc atribute
quantity
input date

stuff contain
Stuffid ( PK ) = Sxxx
name
etc atribute
stock = 30
input date

detailpurchaseorder contain
Stuffid (PK/FK)
COid (PK/FK)

thanks man :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 11:57:19

[code]
CREATE TRIGGER Trg_StockLog
ON PurchaseOrder
FOR INSERT
AS
BEGIN
INSERT INTO Logstuff (COid,Stuffid,date,[stock while buying],[stock before buying])
SELECT i.COid,i.Stuffid,i.[input date],i.quantity,s.stock-COALESCE(i1.prevqty,0)
FROM INSERTED i
INNER JOIN Stuff s
ON s.Stuffid = i.Stuffid
OUTER APPLY (SELECT SUM(quantity) AS prevqty
FROM INSERTED
WHERE Stuffid = i.Stuffid
AND COid < i.COid)i1
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources