question

JR03 avatar image
JR03 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
sqlservertriggers
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.