asked
SQL Trigger to derive a custom number and then insert that derived number into another table.
My question is: How to derive a number one table1 and to insert it into another table. This number is also an auto increment number. My trigger is: create trigger insertQNO on NN after insert as begin update NN set QNumber ='Q150' + CAST(
inserted.ID AS VARCHAR(4)) + '/' + RIGHT(CONVERT(VARCHAR(10),GETDATE(),103),7) from NN join inserted on
inserted.ID =
nn.ID; -- go /* Commented out the line, go does not belong here */ end go My result puts the number into Table NN. if i include the second table as follows: alter trigger insertQNO on NN after insert as begin update NN set QNumber ='Q150' + CAST(
inserted.Id AS VARCHAR(4)) + '/' + RIGHT(CONVERT(VARCHAR(10),GETDATE(),103),7) from NN join inserted on
inserted.Id =
NN.ID; SET IDENTITY_INSERT dbo.table2 ON; insert into Table2 (ID,Qnumber) (select ID, Qnumber from NN) end; go It does the right thing by duplicating the value into the two tables. But now, i want the trigger to fetch the number from table two, and insert the value into Table 3 (this table has different columns) and update table 2 with the next derived no? I have no idea how to do the last step. Any help would be gladly appreciated. I am using SQL Server 2014
@JR03 There is no good reason to update one of the table columns while inserting rows into it. Please consider defining QNumber as a computed column and then deleting the update part of the trigger code. The altered trigger part which inserts rows into dbo.table2 is incorrect and needs to be modified. There are 2 problems with these 3 lines of script: 1. The practice to set identity insert ON and never revert it back explicitly is ill advised. Please add the line reverting the setting back to what it needs to be. 2. The way the insert statement is written will cause all rows from NN to be inserted into dbo.Table2. This cannot possibly be what needs to happen. If you have 100,000 rows in NN and then insert 1 row into it, say 100 times, then your dbo.Table2 will end up with 10 million rows in it. There has to be some condition in the WHERE clause of that insert. Please elaborate on the inserting into Table 3 providing more details. Generally speaking, this is a trivial exercise via output clause, but please provide details. The current script in the trigger will have to be replaced though because it has too many problems at this time. Thank you.
18 People are following this question.
