That procedure might have multiple "reiterating" update while a row is added to the table.

Extremely bad but at the same time very popular approach.

Trigger is launched during every one of those updates, not the only "final" one.

Without seeing the procedure it's hard to suggest a proper workaround.

But for that specific error you posted above this should work:

create trigger [dbo].[insert_transcript_request]

on [dbo].[cosc_TRANSCRIPT_REQUESTs]

AFTER INSERT, UPDATE

AS

IF UPDATE(id_num)

begin

insert into TRANSCRIPT_REQUEST(ID_NUM,

SEQ_NUM_2,

TRANSCRIPT_PRT_DTE,

DIV_GRP_CDE,

NUM_OF_COPIES_NUM ,

ADDR_BLOCK_LINE_1,

ADDR_BLOCK_LINE_2,

ADDR_BLOCK_LINE_3,

ADDR_BLOCK_LINE_4,

JOB_NAME, JOB_TIME,

USER_NAME)

select i.id_num,

isnull(tr_seq.old_seq,0) + 1,

GETDATE(),

DIV_GRP_CDE,

i.NUM_OF_COPIES_NUM ,

i.ADDR_BLOCK_LINE_1,

i.ADDR_BLOCK_LINE_2,

i.ADDR_BLOCK_LINE_3,

i.ADDR_BLOCK_LINE_4,

'request from trigger', GETDATE(), 'SA_nag'

FROM inserted i

LEFT JOIN (

SELECT t.ID_NUM, MAX(SEQ_NUM_2) as old_seq

from TRANSCRIPT_REQUEST t

GROUP BY t.ID_NUM ) as tr_seq ON tr_seq.ID_NUM = i.id_num

where i.ID_NUM is not NULL

AND i.NUM_OF_COPIES_NUM IS NOT NULL

end