Help with Trigger
-
I need to create a Trigger that creates a new quote no everytime a new opportunity is created.
The quote no must start with this number: Q1500/09/2017
The prefix is Q, the number needs to increase as each opportunity is created, the 09 is the month and 2017 the year.I have never done a trigger so i am looking for some assistance and some code examples if i can get these. Or can you point me in the right direction to get this done please!
- Stix83 - Friday, October 13, 2017 12:53 AM
We're a little light on details here. Once you've created a new string with this specified format, what are you intending to do with it?
"a new opportunity" - is this a table?“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
HI
There is an Opportunity table and Quoteno is a user defined field that is recorded in the UdF table. Quoteno is specified by a type_id - in this case type id is 127 in the UDF table.
When i create an Opportunity, the Quoteno value needs to populate to the first number which is Q1500/09/2017, when i create another opportunity then that quote number should read Q1501/09/2017 and so fort.
I know the trigger will need to be put against the Opportunity table however i need the formatting of the number as well as how to change the months and year.so basically - this is the process..
1. Create opportunity via the GUI
2. Trigger fires to create new Quote number each time a user creates a Opportunity. - Stix83 - Friday, October 13, 2017 2:05 AM
So, the Quoteno generated by the trigger would be stored in one row of the UDF table?
Or to put it another way, the UDF table has a row which holds the last generated Quoteno?Can you write a simple query to retrieve the last generated Quoteno from the UDF table?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Yes correct..
Each entry that a user enters has a unique client ID which is assigned to each client entry in the opportunity table. Each client that is entered gets its own client_id.
In the UDF table - the join between the two tables is the client_id.
So each entry has its own clientID and its own quoteno within the UDF Table.
what i normally do is a simple select statement as follows:
Select * from UDF where type_id = 127 order by QuoteNo. (this will show all the quotenumbers that have been used already, displayed in the Quoteno column)
This project however is new so there wont be any quoteno's in the UDF table at all. -
Can you post up the structures of the two tables? It would help to have some sample data too.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Apologies for the delayed response on this. I have been thinking to maybe do this..
Create the trigger against the opportunity table then create a new table called quoteno and somehow reference the type id of the field in the udf table to pull from the temp table quoteno.So Opportunities table has columns:
client_id
Contact number
Objective
Status
Forecast revenue
StageThe new quote no table will have one column called NextNo starting with the set value of Q1500/10/2017
The UDF table has the following columns:
Client_id
Contact number
Type id
AlphanumericColSo basicall pull the next quote no from the quoteno table but reference the type_id in the udf table in the trigger to update the field sitting in the udf table.
Is this possible?
-
HI There.
A colleage of mine has given me this advise but nothing too elaborate:
The auto increment number is through a Trigger that when a opportunity is created it adds one to the last number used, and populate the field with it. Typically i store the last number in a table i create and update that as well in the trigger each time a new number is created.So here is a sample of the Opportunity table with sample data - the whole table:
I then have a table called NextNo with one column in it called Nextno with a value of the starting quoteno: Q1500/10/2017
This will store the numbers of the quotes. however the confusion i am having is does the trigger go on the Opportunities table or the UDF table ? I was thinking the opportunity table as this is where the action is taking place.
So with the quote number stored in the Nextno table (once i created) but needs to update the field in the UDF table with type id 127 - how do i get this to work? I know i need to reference the type_id in order for the trigger to pick up where the quote number must be displayed.
I have a similar trigger that was for creating enquiry numbers in the address book module - the code is as below:_____________________________________________
/****** Object: Trigger [dbo].[EXPNumber] Script Date: 09/10/2017 10:17:50 AM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ONGO
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER [dbo].[EXPNumber] ON [dbo].[AMGR_Client_Tbl] AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;DECLARE @Client_Id varchar(24)
DECLARE @Contact_Number int
DECLARE @Contact_Number_Char varchar(10)
DECLARE @Name_Type varchar(2)
DECLARE @Style_Id int
DECLARE @Type_Id int
DECLARE @View_Name char(50)
DECLARE @Error varchar(250)
DECLARE @DBID int
DECLARE @DBName nvarchar(128)
DECLARE @NewNumber varchar(250)
DECLARE @NewNumChar varchar(250)
DECLARE @NextNo float
DECLARE @UDFName varchar(50)
DECLARE @GeneratorPrefix varchar(10)
DECLARE @GeneratorLength floatSET @UDFName = 'EXP Number' -- UDF to write the incremented number
SET @GeneratorPrefix = 'EXP' -- Prefix string
SET @GeneratorLength = 20 -- Length of number to generate
-- *****************************************************************************************************************IF @Style_Id = 0 AND @Type_Id = 0
BEGIN
SET @Error = @UDFName + ' does not exist'
SET @DBID = DB_ID()
SET @DBName = DB_NAME()
RAISERROR(@Error, 1, 1, @DBID, @DBName)
END
ELSE
BEGIN
SELECT TOP 1 @View_Name = [View_Name] FROM [dbo].[ADMN_DETAIL_VIEW_NAME] WHERE [Type_Id] = @Type_Id
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[' + @View_Name + ']'))
BEGIN
DECLARE _InsertList CURSOR FOR SELECT [Client_Id], [Contact_Number], [Name_Type] FROM inserted
OPEN _InsertList
FETCH NEXT FROM _InsertList INTO @Client_Id, @Contact_Number, @Name_Type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Contact_Number_Char = CAST(@Contact_Number AS varchar(10))
IF @Name_Type = 'C' OR (@Name_Type = 'I' AND @Contact_Number = 0)
BEGIN
BEGIN TRANSACTION
SELECT TOP 1 @NextNo = [NextNo] FROM [dbo].[NextEXP]
SET @NewNumChar = CAST(@NextNo AS VARCHAR(250))
-- SET @NewNumber = @GeneratorPrefix + REPLICATE('0', (@GeneratorLength - (LEN(@GeneratorPrefix) + LEN(@NewNumChar)))) + @NewNumChar
SET @NewNumber = @GeneratorPrefix + @NewNumChar
EXEC('INSERT INTO [' + @View_Name + '] ([Client_Id], [Contact_Number], [' + @View_Name + ']) VALUES (''' + @Client_Id + ''', ' + @Contact_Number_Char + ', ''' + @NewNumber + ''')')
UPDATE [dbo].[NextEXP] SET [NextNo] = [NextNo] + 1
COMMIT
END
FETCH NEXT FROM _InsertList INTO @Client_Id, @Contact_Number, @Name_Type
END
CLOSE _InsertList
DEALLOCATE _InsertList
END
ELSE
BEGIN
SET @Error = @View_Name + ' does not exist'
SET @DBID = DB_ID()
SET @DBName = DB_NAME()
RAISERROR(@Error, 1, 1, @DBID, @DBName)
END
END
ENDGO
GO
I have tried to modify the code of the above but the field doesnt update when inserting the opportunity with all the fields relating to opportunities but to no avail.
I was also thinking to then maybe the query is too complex for what i am trying to do.
Any help would be greatly appreciated as deadlines are staring me in the face - Stix83 - Monday, October 16, 2017 1:10 AM
Have you considered using an autoincrement (IDENTITY) column on yourOpportunities table instead of creating a whole new table to hold the lastvalue of Quoteno? Could Opp_ID in fact be that column? I can’t tell – a pictureof the table doesn’t provide as much information as the structures (CREATETABLE …). You would still have to update the correct row in the UDF table but that’sstraightforward.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
okay - here is what i need..
The trigger needs to be put on the Opportunities table, and then insert into the user fields table once the number has been generated in the Nextno table.
Could someone please assist me in the code for this? - Stix83 - Monday, October 16, 2017 2:08 AM
Havent considered using auto identity - would that be easier?
The OPP_ID and client_ID are already auto generated by the system so those fields cannot be edited - Stix83 - Monday, October 16, 2017 2:08 AM
Yes of course.
"insert into the user fields table" - does this replace "update the UDF table"?
Please provide as much detail as you can.“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - Stix83 - Monday, October 16, 2017 2:10 AM
The alternative - holding the last used value in a separate table - can be difficult to code up in order to prevent deadlocks. You'll be jumping through hoops.
Can you provide DDL (CREATE TABLE statements) for all of the tables involved, please? It will make this so much easier.EDIT: here's a link to a useful thread covering "last used value" tables, including a well-known resolution by Jeff Moden -
https://www.sqlservercentral.com/Forums/Topic817978-145-1.aspx
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Yes, i need to insert into the user fields table where the type_id is 127 - the field name is Quoteno.
So basically when the new opportunitiy is created and the next quote number (in this case will be Q1501/10/2017) is generated - the trigger inserts the quote number into the quoteno field in the user fields table.
I am new at this so i am on the forums to try get code as i dont know where to start with this trigger - i have a rough idea but the problem is putting it into syntax. - ChrisM@Work - Monday, October 16, 2017 2:30 AM
sure thing:
here is the Opportunity table:
CREATE TABLE [dbo].[AMGR_Opportunity_Tbl](
[Data_Machine_Id] [numeric](10, 0) NULL,
[Sequence_Number] [numeric](10, 0) NULL,
[Client_Id] [varchar](24) NULL,
[Contact_Number] [int] NULL,
[Opp_Id] [varchar](24) NULL,
[Opp_Number] [int] NULL,
[Comp_Client_Id] [varchar](24) NULL,
[Comp_Cont_No] [int] NULL,
[Owner_Id] [varchar](12) NULL,
[Strategy_Id] [numeric](10, 0) NULL,
[Team_Id] [int] NULL,
[Status] [int] NULL,
[Name] [varchar](80) NULL,
[Objective] [varchar](254) NULL,
[Reason_Id] [numeric](10, 0) NULL,
[Probability_Closing] [smallint] NULL,
[Cost] [float] NULL,
[Forecast_Revenue] [float] NULL,
[Start_Date] [datetime] NULL,
[Estimated_Close_Date] [datetime] NULL,
[Close_Date] [datetime] NULL,
[Last_Modify_Date] [datetime] NULL,
[Actual_Revenue] [float] NULL,
[Record_Id] [int] IDENTITY(1,1) NOT NULL,
[Creator_Id] [varchar](12) NULL,
[Create_Date] [datetime] NULL,
[Suspend_Date] [datetime] NULL,
[Days_Suspended] [int] NULL,
[Dynamic_Strat] [smallint] NULL,
[Dynamic_Team] [smallint] NULL,
[Opp_Type] [smallint] NULL,
[Comment] [varchar](508) NULL,
[Completion_Comment] [varchar](508) NULL,
[Last_Update_Date] [datetime] NULL,
[Updated_By_Id] [varchar](12) NULL,
[TestStatus] [int] NULL,
[Last_Opp_Id] [varchar](24) NULL,
[Cont_Client_Id] [varchar](24) NULL,
[Cont_Cont_No] [int] NULL,
[NextAction] [varchar](64) NULL,
[NextAction_Projected] [varchar](64) NULL,
[Revenue_Currency_Id] [varchar](5) NULL,
[Revenue_Rate_Type_Id] [smallint] NULL,
[Last_Cal_Date] [datetime] NULL,
[Calculated_Won_Revenue] [float] NULL,
[Calculated_InProgress_Revenue] [float] NULL,
[Notify_Flag] [int] NULL,
[Actual_Close_Date] AS (case when [Last_Modify_Date] IS NULL then [Close_Date] else [Last_Modify_Date] end),
[Opp_Campaign_Id] [varchar](24) NULL,
[Process_Id] [varchar](24) NULL,
[Process_Number] [int] NULL,
[Current_Stage_Id] [varchar](24) NULL,
[Current_Stage_Number] [int] NULL,
[Current_Stage_Change_Date] [datetime] NULL,
[Opportunity_Age] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The User fields table:CREATE TABLE [dbo].[AMGR_User_Fields_Tbl](
[Client_Id] [varchar](24) NULL,
[Contact_Number] [int] NULL,
[Type_Id] [int] NULL,
[Code_Id] [int] NULL,
[Last_Code_Id] [int] NULL,
[DateCol] [datetime] NULL,
[NumericCol] [float] NULL,
[AlphaNumericCol] [varchar](750) NULL,
[Record_Id] [int] IDENTITY(1,1) NOT NULL,
[Creator_Id] [varchar](12) NULL,
[Create_Date] [datetime] NULL,
[mmddDate] [varchar](4) NULL,
[Modified_By_Id] [varchar](12) NULL,
[Last_Modify_Date] [datetime] NULL
) ON [PRIMARY]
The Nextno table (custom table):
CREATE TABLE [dbo].[NextNo](
[NextNo] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The opportunity and user fields table is part of the system tables of the software
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply