April 17, 2016 at 4:06 pm
Hi,
Well, I would like to know how to create a trigger?
Thanks,
April 18, 2016 at 7:20 am
fareedhmohamed (4/17/2016)
Hi,Well, I would like to know how to create a trigger?
Thanks,
I understand being a bit overwhelmed when working with something new but you have got to learn some ways to pick up some of this stuff using a search engine. A quick search on google for "sql server create trigger" will return you this link as the #1 link. https://msdn.microsoft.com/en-us/library/ms189799.aspx This is a link to books online (BOL). If you want to have any kind of future with sql server you need to be able to do this kind rudimentary research.
This forum is a good one because this thread would have downvoted and closed by now on most other forums. A huge kudos to JLS for persevering and demonstrating utmost patience. It is very difficult to offer a lot of help here because you seem to be all over the place.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2016 at 8:47 am
A couple hints here for you. Not that I won't help, but I'd like you to learn why, rather than cut/paste.
In terms of finding out customers that have multiple orders, or items, the COUNT helps, but you want to limit things. For example, if I had one order, and I use your COUNT() code, I get a "1" back for the count. If my borther has two orders, we see a "2" for him. However we'd only want his name back.
I'd look at the HAVING clause. This is below the GROUP BY, and acts as a filter, in essence, a WHERE clause for the groups. Use that to limit the values returned.
In terms of a trigger, start by writing a query that joins your table, the Order table, to the "inserted" table, and return the orders you'd like to create a discount for. Doing this is a good way to start understanding the trigger.
For example, here's a short one:
CREATE TABLE Orders
( OrderID INT PRIMARY KEY
, CustomerID int
, Qty INT
, UnitCost INT
)
;
GO
INSERT dbo.Orders
VALUES (1, 10, 10, 5)
, (2, 10, 12, 6)
, (3, 30, 5, 12)
, (4, 20, 6, 8)
GO
CREATE TABLE TriggerTests
( TriggerTime DATETIME DEFAULT GETDATE()
, OrderID INT
, CustomerID INT
, Qty INT
, UnitCost int
)
GO
CREATE TRIGGER Orders_Insert_Discount ON dbo.Orders FOR INSERT
AS
BEGIN
INSERT dbo.TriggerTests
SELECT GETDATE()
, o.*
FROM Orders o
INNER JOIN inserted i
ON i.OrderID = o.OrderID
END
GO
INSERT ORders VALUES (5, 20, 10, 10)
GO
SELECT * FROM dbo.TriggerTests AS tt
GO
DROP TABLE ORders
GO
Now, change my trigger code to count the orders for a customer. You might need to change the "triggertests" table to accomodate this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 18, 2016 at 10:48 am
fareedhmohamed (4/17/2016)
Hi,Well, I would like to know how to create a trigger?
Thanks,
ok...but lets walk before we run (with triggers)
given the following and according to your rules that an orderiD with four or more rows gets a 10% discount
CREATE TABLE #yourtable(
OrderId INTEGER NOT NULL
,SalesUnitPrice NUMERIC(13,2) NOT NULL
);
INSERT INTO #yourtable(OrderId,SalesUnitPrice) VALUES
(1,67.22),(1,84),(2,15),(2,49),(2,19.66),(2,93.33);
SELECT * FROM #yourtable;
DROP TABLE #yourtable
can you write code that results in this
+------------------------------------------+
¦ OrderId ¦ SalesUnitPrice ¦ discountprice ¦
¦---------+----------------+---------------¦
¦ 1 ¦ 67.22 ¦ 67.22 ¦
¦ 1 ¦ 84.00 ¦ 84.00 ¦
¦ 2 ¦ 15.00 ¦ 13.50 ¦
¦ 2 ¦ 49.00 ¦ 44.10 ¦
¦ 2 ¦ 19.66 ¦ 17.69 ¦
¦ 2 ¦ 93.33 ¦ 84.00 ¦
+------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2016 at 12:53 pm
No, I dont get that result that you are getting. How did you get the discount column price?
Can I please say that thank you so much for trying to help me out. I know my question was not clear, but you still are helping. Its really apprciated so much.
Thanks
April 18, 2016 at 1:11 pm
fareedhmohamed (4/18/2016)
No, I dont get that result that you are getting. How did you get the discount column price?Can I please say that thank you so much for trying to help me out. I know my question was not clear, but you still are helping. Its really apprciated so much.
Thanks
so are those the results you are expecting?
what code did you try that didnt work?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2016 at 1:20 pm
here is one way to get the results
CREATE TABLE #yourtable(
OrderId INTEGER NOT NULL
,SalesUnitPrice NUMERIC(13,2) NOT NULL
);
INSERT INTO #yourtable(OrderId,SalesUnitPrice) VALUES
(1,67.22),(1,84),(2,15),(2,49),(2,19.66),(2,93.33);
SELECT * FROM #yourtable;
WITH cte_cnt as (
SELECT OrderId, COUNT(*) AS cnt
FROM #yourtable
GROUP BY OrderId
)
SELECT y.OrderId,
y.SalesUnitPrice,
CASE
WHEN cte_cnt.cnt > = 4
THEN CAST(y.SalesUnitPrice * 0.9 AS DECIMAL(18, 2))
ELSE y.SalesUnitPrice
END AS discountprice
FROM #yourtable y
INNER JOIN cte_cnt ON y.OrderId = cte_cnt.OrderId;
DROP TABLE #yourtable
at the moment I feel we are a long way off discussing triggers...they are not a magic bullet, can be very difficult to code properly and at this stage in your SQL development are probably a step too far.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply