SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old October 16th, 2017, 08:50 AM
Stix83 Stix83 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2017
Posts: 0 Stix83 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 39 m 47 sec
Reputation Power: 0
Exclamation Generate number in specific format using insert trigger

0

down vote

favorite





I am very new at triggers infact this is my first one and i am rather flustered and confused! I need all the help i can get.

My main aim is the following:
1.Create a Trigger on table Opportunities
2.Generate a quote no in such a format: Q1500/10/2017
3.insert this number that has been generated into another table called UDF

so basically Once a new opportunity is created, the field in the udf table must be inserted only once the quote no is generated. (insert into the user fields table once the number has been generated.)

The quote number is no an autoincrement number - i basically have created a custom table with one column in that stores the quote no.

Could someone assist me with some sample code so i can get some direction.


What i have done so far is i created a custom table called QT this table has columns Client_ID and QT in it.

i created a simple trigger which runs fine however when i create an opportunity only the client_id gets inserted and not the QT or quotenumber - this is probably because i dont know how to insert the custom number:

my code below:

Code:
create trigger oninsert on Amgr_opportunity_tbl
for insert
as
begin

Declare @client_id varchar (20);
Declare @contact_number int;
Declare @QT int;
Declare @no int;

Select @client_id = i.client_id from inserted i ;
select @contact_number = i.contact_number from inserted i;
select @QT = QT, @no = QT + 1 from QT where @client_id = client_id;

Set @QT = 'Q' + Right('1500' + convert(varchar(5),@no),4) + '-' + MONTH(getdate()) + '-' + Year(getdate())

insert into QT (QT, client_id)
values (@QT,@client_id)


end


Results of this trigger shows a NULL for the QT number:

QT Client_ID
NULL 17041825165157527000

If someone can please advise how i can generate the quotenumber in my trigger above and how to insert it into the custom table.

Reply With Quote
  #2  
Old October 16th, 2017, 04:08 PM
Stix83 Stix83 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2017
Posts: 0 Stix83 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 39 m 47 sec
Reputation Power: 0
trigger update

what is the easiest way to create a table that increments the quote number starting from Q1500/10/2017.
10 being month and 2017 being year?

Last edited by Stix83 : October 17th, 2017 at 05:33 AM.

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > Generate number in specific format using insert trigger


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap