Trigger is not working

Transact-SQL
,
1

I have a trigger that is created on the opportunity table, when a new opportunity is inserted then the trigger fires and creates a custom number for a quote num value which starts from Q1500/10/2017 - 10 being month and 2017 being year. When another opportunity is create the 1500 must go to 1501 ans so forth.

Once the number is generated - it needs to be inserted into the User fields table into the alphanumericCol field where type_id = 11

here is my code for my trigger but it isnt generating the quote number and its not updating the user fields table.
For the life of me - i dont know why!!

indent preformatted text by 4 spaces

alter TRIGGER [dbo].[MyTrigger] ON [dbo].[amgr_opportunity_tbl] AFTER INSERT AS
BEGIN

SET NOCOUNT ON;

DECLARE @client_id VARCHAR(20);
declare @contact_number int;

SELECT @client_id = client_id FROM inserted
select @contact_number = contact_number from inserted

DECLARE @NextID INT, @NewQT VARCHAR(24)
UPDATE dbo.QT WITH (UPDLOCK) SET @NewQT = QT = QT + 1
WHERE [client_id] = @client_id
SET @NewQT = 'Q' + CAST(@NextID AS VARCHAR(4)) + '/' + RIGHT(CONVERT(VARCHAR(10),GETDATE(),103),7)

declare @alphanumericCol varchar (24);

INSERT INTO [dbo].[AMGR_User_Fields_Tbl] (
Client_Id,
Contact_Number,
Type_Id,
Code_Id,
AlphaNumericCol,
Creator_Id)
SELECT
@client_id,
0,
[Type_ID] = 11,
0,
@alphanumericCol,
creator_id
FROM inserted i

END

2

Your trigger needs to be coded to work with multiple rows - a trigger is NOT called once-per-row but once-per-batch. At the very least include a statement to check the number of rows and RAISERROR if that is <> 1 !!

Is there guaranteed to be a row in [QT] for every client? or (for example on the very first Opportunity would it be necessary to INSERT a new row?)

At the very least check that the UPDATE did indeed update exactly 1 row, and it not RAISERROR

@alphanumericCol has not had a value assigned, so will be NULL Presumably you are intending to use the @NewQT value?

Comment:

Looking at this code it looks like it is trying to insert a row into AMGR_User_Fields_Tbl with AlphaNumericCol. I think it would be better to move this code to the Procedure that inserts a row into amgr_opportunity_tbl, rather than have it in a Trigger - because of the need to provide an AlphaNumericCol for each row. However IF you have lots of places that Insert into amgr_opportunity_tbl that might not be straightforward - nonetheless I think it better this is in a Procedure rather than a Trigger.

In that scenario you could CHECK, in the Trigger, that all the rows being inserted into amgr_opportunity_tbl already ahve corresponding rows in AMGR_User_Fields_Tbl or, if they need to be done amgr_opportunity_tbl first then AMGR_User_Fields_Tbl, then have a "validation report" that runs frequently that checks for any missing AMGR_User_Fields_Tbl rows

3

I got the number to generate correctly in a custom table called Table one.
Now its a matter of putting the trigger against the opportunities table, update custom table to insert another number then insert into user fields table where type_id = 11