SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 TRIGGER
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/10/2013 :  12:18:47  Show Profile  Reply with Quote
Please am being asked to do the following tasks on a table in a Database.

*Quantity received should be added to QuantityinHand in the Items Table.
*When a record is inserted into the table, QuantityinHand in the Items table should be updated automatically.

And a friend of mine advised me to create a trigger and apply logic. But am new to triggers and I really don't know how to use them. I went to the Microsoft website and got this message below which is so complex for my understanding. Can someone please help me out with how to go about this ? Please..

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

... http://technet.microsoft.com/en-us/library/ms189799.aspx


Best Regards.

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/10/2013 :  14:13:01  Show Profile  Reply with Quote
couple things:
1. You may want to consider a different design model where [QuantityInHand] is not stored/maintained but rather is derived on the fly whenever someone wants to know.

2. If you want to stick with the trigger idea then you'll need to provide some specifics - like DDL for all objects involved. What are all the actions that could affect the [QuantityInHand] both adding and removing?


Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  14:25:31  Show Profile  Reply with Quote
something like this
Assuming itemID is column by which both tables (Items and OrderDetails are related)
as per
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188527

CREATE TRIGGER Trg_ItemQuantityConsolidate
ON ORDERDETAILS
FOR INSERT,UPDATE,DELETE
AS
BEGIN 
UPDATE i
SET i.QuantityinHand + COALESCE(ins.TotalQty,0) - COALESCE(del.TotalQty,0)
FROM Items i
LEFT JOIN (SELECT ItemID,
           SUM(QuantityReceived) AS TotalQty
           FROM INSERTED 
           GROUP BY ItemID
           )ins
ON ins.ItemID = i.ItemID
LEFT JOIN (SELECT ItemID,
           SUM(QuantityReceived) AS TotalQty
           FROM DELETED 
           GROUP BY ItemID
           )del
ON del.ItemID = i.ItemID
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/10/2013 :  14:37:23  Show Profile  Reply with Quote
Thanks Bro. But I got this error messages after copy and pasting even though I DON'T UNDERSTAND. Gol

Msg 102, Level 15, State 1, Procedure Trg_ItemQuantityConsolidate, Line 7
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure Trg_ItemQuantityConsolidate, Line 13
Incorrect syntax near 'ins'.
Msg 102, Level 15, State 1, Procedure Trg_ItemQuantityConsolidate, Line 19
Incorrect syntax near 'del'.


Best Regards.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  14:40:54  Show Profile  Reply with Quote
quote:
Originally posted by Hinduson

Thanks Bro. But I got this error messages after copy and pasting even though I DON'T UNDERSTAND. Gol

Msg 102, Level 15, State 1, Procedure Trg_ItemQuantityConsolidate, Line 7
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure Trg_ItemQuantityConsolidate, Line 13
Incorrect syntax near 'ins'.
Msg 102, Level 15, State 1, Procedure Trg_ItemQuantityConsolidate, Line 19
Incorrect syntax near 'del'.


Best Regards.



that was a typo

CREATE TRIGGER Trg_ItemQuantityConsolidate
ON ORDERDETAILS
FOR INSERT,UPDATE,DELETE
AS
BEGIN 
UPDATE i
SET i.QuantityinHand = i.QuantityinHand + COALESCE(ins.TotalQty,0) - COALESCE(del.TotalQty,0)
FROM Items i
LEFT JOIN (SELECT ItemID,
           SUM(QuantityReceived) AS TotalQty
           FROM INSERTED 
           GROUP BY ItemID
           )ins
ON ins.ItemID = i.ItemID
LEFT JOIN (SELECT ItemID,
           SUM(QuantityReceived) AS TotalQty
           FROM DELETED 
           GROUP BY ItemID
           )del
ON del.ItemID = i.ItemID
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/10/2013 :  15:06:04  Show Profile  Reply with Quote
Thanks Bro It executed, But as a matter of fact I DON'T UNDERSTAND HOW YOU DID IT. Can you please refer me to a website excluding the Microsoft one because its composite and confusing for me. Or will you like to do me the honors of explaining please, so that if am asked any questions i can answer.. I really appreciate bro


Best Regards.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2013 :  02:28:40  Show Profile  Reply with Quote
TRIGGER gets fired for each DML changes in source table for which its defined for (ie for INSERT,UPDATE and DELETE operations in ORDERDETAILS table)
There are two internal temporary tables used by SQL Server called DELETED and INSERTED for performing these operations
Following are the details of what happens for various operations

INSERT - In this case INSERTED table will have the values that are inserted to the main table OrderDetails
DELETE - In this case DELETED table will have the values that are to be deleted from the main table OrderDetails
UPDATE - In this case DELETED table will contain the earlier value (prior to UPDATE) and INSERTED table will contain updated values (after the UPDATE)

What I'm doing in logic above is to take total qty from these table and add INSERTED ones and remove the DELETED ones from QuanityOnHand in Items table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/12/2013 :  10:58:09  Show Profile  Reply with Quote
UNDERSTOOD. SO LET ME TRY SOMETHING... I'LL LET YOU KNOW WHAT THE OUTCOME IS.. THANKS ALOT BRO.

Best Regards.
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/12/2013 :  11:02:17  Show Profile  Reply with Quote
I Tried this but i get errors. And the error message is below.

CREATE TRIGGER TRG_INSERT
ON TRANSACTIONS_ORDERDETAILS(QUANTITYRECEIVED)
AFTER INSERT ON TRANSACTIONS_ORDERDETAILS(QUANTITYRECEIVED)
FOR INSERT,UPDATE
AS
BEGIN
UPDATE ITEMS_ITEMDETAILS(QUANTITYINHAND)
AFTER INSERT ON TRANSACTIONS_ORDERDETAILS(QUANTITYRECEIVED)
END

Msg 102, Level 15, State 1, Procedure TRG_INSERT, Line 2
Incorrect syntax near '('.
Msg 102, Level 15, State 31, Procedure TRG_INSERT, Line 7
Incorrect syntax near '('.

Please advice


Best Regards.
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/12/2013 :  11:05:42  Show Profile  Reply with Quote
And the question is QUANTITYRECEIVED SHOULD BE ADDED TO QUANTITYINHAND IN THE ITEMS TABLE.

And QUANTITYRECEIVED IS IN THE TRANSACTIONS_ORDERDETAILS TABLE, WHILES QUANTITYINHAND IS IN THE ITEMS_ITEMDETAILS TABLE.

Best Regards.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2013 :  14:02:14  Show Profile  Reply with Quote
quote:
Originally posted by Hinduson

I Tried this but i get errors. And the error message is below.

CREATE TRIGGER TRG_INSERT
ON TRANSACTIONS_ORDERDETAILS(QUANTITYRECEIVED)
AFTER INSERT ON TRANSACTIONS_ORDERDETAILS(QUANTITYRECEIVED)
FOR INSERT,UPDATE
AS
BEGIN
UPDATE ITEMS_ITEMDETAILS(QUANTITYINHAND)
AFTER INSERT ON TRANSACTIONS_ORDERDETAILS(QUANTITYRECEIVED)
END

Msg 102, Level 15, State 1, Procedure TRG_INSERT, Line 2
Incorrect syntax near '('.
Msg 102, Level 15, State 31, Procedure TRG_INSERT, Line 7
Incorrect syntax near '('.

Please advice


Best Regards.


the syntax is wrong. you just need the below


CREATE TRIGGER Trg_ItemQuantityConsolidate
ON TRANSACTIONS_ORDERDETAILS
FOR INSERT,UPDATE,DELETE
AS
BEGIN 
UPDATE i
SET i.QuantityinHand = i.QuantityinHand + COALESCE(ins.TotalQty,0) - COALESCE(del.TotalQty,0)
FROM ITEMS_ITEMDETAILS i
LEFT JOIN (SELECT ItemID,
           SUM(QuantityReceived) AS TotalQty
           FROM INSERTED 
           GROUP BY ItemID
           )ins
ON ins.ItemID = i.ItemID
LEFT JOIN (SELECT ItemID,
           SUM(QuantityReceived) AS TotalQty
           FROM DELETED 
           GROUP BY ItemID
           )del
ON del.ItemID = i.ItemID
END

the INSERTED and DELETED table will contain contents of TRANSACTIONDETAILS table after the operationa as explained previously

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 10/14/2013 :  06:53:45  Show Profile  Reply with Quote
Thanks Bro.


Best Regards.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/14/2013 :  07:24:08  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000