Trigger for incrementing

Question

Trigger for incrementing

fareedhmohamed

Ten Centuries

Points: 1035
More actions
April 13, 2016 at 10:57 pm

#315520

Hi,
I have a question regarding increments in trigger. I want to update a field in customer table, if the same customer has more than two orders. I am not sure on how do I go about doing this?
Thanks,

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

fareedhmohamed (4/13/2016)
Hi,
I have a question regarding increments in trigger. I want to update a field in customer table, if the same customer has more than two orders. I am not sure on how do I go about doing this?
Thanks,

Well neither do we unless you provide some details. Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

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/

tripleAxe SSCertifiable Points: 5706 More actions · Answer 2

I'm guessing at why you want to do this and from my guess I say "don't do it". Tell me why you want to do this and I might change my opinion. If you want to find customers with more than one order then query the order table.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

You want to be careful with triggers, and think through carefully if this always needs to happen, including when DBAs need to fix data.

However, if you do need to do this, in your trigger, you'll need to query back to the table and determine if a user has multiple orders. Do you need to do this only for the orders changed/added or all orders? There are valid reasons for choosing either case.

The "inserted" table is a virtual table you reference inside a trigger as "inserted" and use that to join to your table. For example.

select primarykeycol

from mytable a

inner join inserted i

on a.primarykeycol = i.primarykeycol

The structure of inserted is the same as your table.

Write the code you want and use this to check things outside of a trigger. So if you want to detect two orders, write that query first, and see if it works. Then look at adding logic that might do something.

fareedhmohamed Ten Centuries Points: 1035 More actions · Answer 4

Hi,

Just all the orders ordered by the same customer.

Thanks,

Michael L John One Orange Chip Points: 27056 More actions · Answer 5

So, you have a customers table that contains a field that needs to store the number of orders if they have more than one?

Not sure why this would need to stored as opposed to being queried when this is needed.

When an insert occurs on the order table, add code to update that field in the customers table.

Why do you need to store this?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

fareedhmohamed (4/14/2016)
Hi,
Just all the orders ordered by the same customer.
Thanks,

Huh? That is not helping us understand what you are having a problem with. There are three people so far in this thread that are demonstrating a desire to help you. If you want help you have to put in some effort and give us some details. We can't see your screen, we do not know your table structures, we do not know your application and we do not know your business needs or rules. The only thing we know is what you post, which at this point is almost nothing other than a plea for somebody to help.

If you trying to store the number of orders for a given customer I would argue against storing that value at all. Unless you have a really large number of orders this should be calculated on the fly. Or it should be a computed column on the customer table.

_______________________________________________________________

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/

fareedhmohamed Ten Centuries Points: 1035 More actions · Answer 7

Hi,

I am sorry I have not provided the correct information regarding the problem.

I am trying to create a trigger, which will provide a discount of 10%, if the same customer number has at least bought 4 items. I have posted 3 of the table structures that would be involved in the trigger.

Thanks for the help provided

fareedhmohamed Ten Centuries Points: 1035 More actions · Answer 8

I am not sure how to write a query, that will display the customer who has more than one order in the order table. I have attached the table structures.

THanks

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

fareedhmohamed (4/16/2016)
I am not sure how to write a query, that will display the customer who has more than one order in the order table. I have attached the table structures.
THanks

is this trigger/query to update exisiting records in a table or is it to be performed on insert of new records?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

fareedhmohamed Ten Centuries Points: 1035 More actions · Answer 10

fareedhmohamed

Ten Centuries

Points: 1035

April 17, 2016 at 5:54 am

#1872585

Hi,

Yeah, that is correct.

Thanks,

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

fareedhmohamed (4/17/2016)
Hi,
Yeah, that is correct.
Thanks,

which one is correct?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

fareedhmohamed Ten Centuries Points: 1035 More actions · Answer 12

fareedhmohamed

Ten Centuries

Points: 1035

April 17, 2016 at 5:58 am

#1872587

The fact that, it has to happen after INSERT

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 13

personally I wouldnt put a trigger on the order/orderdetails tables...I would probably get the front end application to do the necessary calculations PRIOR to writing to the back end tables and post both original unit price and "discounted" prices.

Quite often in a retail environment there are multiple promotions and the front end app needs to determine which are applicable and decide which to use ...so I would also record the promotion used as well.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

fareedhmohamed Ten Centuries Points: 1035 More actions · Answer 14

I agree with what you are saying but however this is more of a practise trigger for me to try as I just want to get accustomed to triggers hence I am wondering how this would be done.

Thanks,