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
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] (
[Type_ID] = 11,
FROM inserted i