I have written an SP to do the below:
i have a table called T1:
AS below:
CREATE TABLE [dbo].[T1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[QNo] [nvarchar](50) NULL DEFAULT ('0'),
PRIMARY KEY CLUSTERED
T1 stores a number value in the Qno Column. AS so:
ID: 1 Qno: 0
When i insert into table QUDF, the last number (in this case currently is '0'), needs to be inserted into the QUDF column in the QUDF table. (it needs to fetch last number from T1 when inserting in QUDF)
columns in QUDF are:
Client_id, Contact_number, Code_ID, Type_id, QUDF
Once inserted, the Qno column in T1 needs to update to 1 (increment by 1 each time)
I have been on the forums but still nothing has resolved this. Please take note, only 1 row is being inserted at a time through the GUI
I have then created a trigger on the Opportunities table as this all needs to happen when an Opportunity is created by the user via the GUI.
SP:
ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id varchar (24), @Opp_Number int
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Qno varchar (50)
Declare @NewQno varchar (50)
SET @Qno= '0';
SELECT @NewQno =@Qno + 1 FROM T1 where ID = 1;
Insert into O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
values(@Opp_Id,@Opp_Number,1,0,@Qno)
Update T1 set Qno = @NewQno where ID = 1
END
GO
Trigger:
Trigger must only fire when Opp_type = 0
create TRIGGER [dbo].[Trg] ON [dbo].[AMGR_Opportunity_Tbl] AFTER INSERT
AS
BEGIN
Declare @Opp_type varchar(24)
Declare @Opp_id int
Declare @Opp_number int
Select @Opp_type = Opp_Type from inserted
select @Opp_id = Opp_id from inserted
Select @Opp_number = Opp_Number from inserted
If @Opp_type = '0'
BEGIN
Execute dbo.SP1_Test @Opp_id,@Opp_number
END
If someone can assist me in getting my scenario right – i am very new at this logic. I would greatly appreciate any suggestions.
SOLUTION to my issue was to simply add the following:
select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
FROM T1 WHERE ID = 1;
To the code example below:
BEGIN
Declare @Opp_id varchar (24)
Declare @OppNo int
Declare @Opp_type int
DECLARE @NewQno VARCHAR(750)
declare @Qno varchar (50)
select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
FROM T1 WHERE ID = 1;
If @Opp_type = '0'
SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
FROM T1 WHERE ID = 1;
INSERT INTO O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
values(@Opp_id,@OppNo,1,0,@NewQno)
UPDATE T1 SET Qno = @NewQno WHERE ID = 1
End
Best Answer
Based on the limited information in your question, I made a few changes to your stored procedure. If I've misunderstood something, let me know. This code was not fully tested on my side.