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:
create trigger oninsert on Amgr_opportunity_tbl
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)
Results of this trigger shows a NULL for the QT number:
If someone can please advise how i can generate the quotenumber in my trigger above and how to insert it into the custom table.