Need help with Triggers - 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

General SQL Server Forums

New to SQL Server Programming

Need help with Triggers

Author

Topic

sdiva1982
Starting Member

5 Posts

Posted - 2010-04-03 : 10:56:01

Hello. I am new to SQL need help writing 2 triggers. Here is the info. I can't seem to get them to work. Not sure what I am doing wrong. Can anyone help?

CREATE a trigger on the ORDERLINE table for the insert event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by adding the value of QTY to QTY_ORDERED.

CREATE a trigger on the ORDERLINE table for the delete event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by subtracting the value of QTY FROM QTY_ORDERED.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-03 : 12:24:24

Show us what you have so far, and then we'll help you with your assignment. We need to at least see some effort first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sdiva1982
Starting Member

5 Posts

Posted - 2010-04-03 : 12:54:54

This is what I have for the insert. Thanks so much.

CREATE TRIGGER tr_order_value_insert
ON Orderline FOR INSERT
AS
BEGIN
UPDATE Quantity_Ordered
SET order_value = order_value +
(SELECT qty from INSERTED) * (SELECT QTY_Ordered from INSERTED)
WHERE orders.orderno = (SELECT orderno from INSERTED);
END;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 12:59:13

[code]CREATE TRIGGER tr_order_value_insert
ON Orderline
AFTER INSERT,DELETE
AS
BEGIN
UPDATE p
SET p.QTY_ORDERED = p.QTY_ORDERED + CASE WHEN i.ProductId IS NOT NULL THEN i.QTY ELSE (-1) * d.QTY END
FROM PRODUCT p
LEFT JOIN INSERTED i
ON i.ProductId = p.ProductId
LEFT JOIN DELETED d
ON d.ProductId = p.ProductId
WHERE i.ProductId IS NOT NULL
OR d.ProductId IS NOT NULL
END
[/code]

as per your initial explanation.
Your posted query has different table and column names specified though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sdiva1982
Starting Member

5 Posts

Posted - 2010-04-03 : 12:59:28

What am I doing wrong. I am going by an example.

sdiva1982
Starting Member

5 Posts

Posted - 2010-04-03 : 13:14:55

quote:
Originally posted by visakh16

CREATE TRIGGER tr_order_value_insert
ON Orderline
AFTER INSERT,DELETE
AS
BEGIN
UPDATE p
SET p.QTY_ORDERED = p.QTY_ORDERED + CASE WHEN i.ProductId IS NOT NULL THEN i.QTY ELSE (-1) * d.QTY END
FROM PRODUCT p
LEFT JOIN INSERTED i
ON i.ProductId = p.ProductId
LEFT JOIN DELETED d
ON d.ProductId = p.ProductId
WHERE i.ProductId IS NOT NULL
OR d.ProductId IS NOT NULL
END

as per your initial explanation.
Your posted query has different table and column names specified though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, I didn't know you could do it that way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 13:19:01

try it and let us know if thats what you're looking for!

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

sdiva1982
Starting Member

5 Posts

Posted - 2010-04-03 : 13:22:24

Can someone answer another question? I have to insert the following in the ORDERS row and the ORDERLINE Row. I can get the ORDERS row to work but the ORDERLINE comes up with an error. Can anyone explain what I am doing wrong or what the error means.

Here is the info I must insert:
Orderno Customer OrderDate ProductID Qty
801051 10123 2/1/2009 Round 67

INSERT INTO orders VALUES (801051, 10123, '2/1/2009')

SELECT * FROM orders where orderno = 801051

INSERT INTO orderline values ('Round',801051,10,20.11,'1/1/2010');
SELECT * FROM orders where orderno = 801051

The error I get:
Msg 208, Level 16, State 1, Procedure tr_order_value_insert, Line 5
Invalid object name 'Quantity_Order'.

Why is it saying that about the Quantity_Order and how do I fix it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 13:26:48

its certainly not in query you posted as I can see no Quantity_Order in posted query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources