TRIGGER - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

TRIGGER

Author

Topic

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-10 : 12:18:47

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-10 : 14:13:01

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 14:25:31

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

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-10 : 14:37:23

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 14:40:54

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

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-10 : 15:06:04

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 02:28:40

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

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-12 : 10:58:09

UNDERSTOOD. SO LET ME TRY SOMETHING... I'LL LET YOU KNOW WHAT THE OUTCOME IS.. THANKS ALOT BRO.

Best Regards.

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-12 : 11:02:17

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.

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-12 : 11:05:42

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-12 : 14:02:14

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

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-14 : 06:53:45

Thanks Bro.

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 07:24:08

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources