Create trigger on Insert

Question

Post reply

Create trigger on Insert

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
June 19, 2009 at 1:26 pm

#133250

Hi all,
I am trying to create a trigger such that when a case ID is inserted into the table it gets appended with a sequence number.
caseID
12345
23456
32145
65437
23455
Now when a new case ID is entered or inserted in a table with CaseID as a column then it should be entered as
caseID
12345
23456
32145
65437
23455
88345-101 -- new case ID inserted
99876-102 -- new case ID inserted
note: -101, -102 .... is the sequence which is used for uniqueness and for tracking of new added values.
Can I create an insert trigger on a table ?
If this column is referenced in some other tables like history or staging, can it be updated also.
Any hints how to perform this !!
Thanks,
\\K :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

check out the link on how to create triggers. http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

here is something to help you get started

create trigger trig_name

on table_name

after insert

as

declare @seq varchar(25)

set @seq = (select max(substring(caseid,charindex('-',caseid)+1,len(caseid)))+1 from trgi)

update table_name

set table_name.caseid = table_name.caseid + '-' + @seq

from table_name

inner join inserted

on table_name.caseid = inserted.caseid

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

Robert klimes (6/19/2009)
check out the link on how to create triggers. http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx
here is something to help you get started
create trigger trig_name
on table_name
after insert
as
declare @seq varchar(25)
set @seq = (select max(substring(caseid,charindex('-',caseid)+1,len(caseid)))+1 from trgi)
update table_name
set table_name.caseid = table_name.caseid + '-' + @seq
from table_name
inner join inserted
on table_name.caseid = inserted.caseid

Hi,

Thanks for your help. Let me try it out.

I dont understand this

set @seq = (select max(substring(caseid,charindex('-',caseid)+1,len(caseid)))+1 from trgi)

Will this append with a unique sequence ID everytime the record is inserted in the table.

i.e the caseID-UniqueSequenceID

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

what it does is sets the number to append to the caseid based on the highest number that was already appended plus one.

set @seq = (select max(substring(caseid,charindex('-',caseid)+1,len(caseid)))+1 from trgi)

charindex('-',caseid)+1 - this finds which position the '-' is in and adds 1 to it.

substring(caseid,{previous value},len(caseid)) - this will get all values past the '-'

select max({previous value}) - gets the max value from all rows of values past the '-'

+1 - adds one to the max value

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

Robert klimes (6/22/2009)
what it does is sets the number to append to the caseid based on the highest number that was already appended plus one.
set @seq = (select max(substring(caseid,charindex('-',caseid)+1,len(caseid)))+1 from trgi)
charindex('-',caseid)+1 - this finds which position the '-' is in and adds 1 to it.
substring(caseid,{previous value},len(caseid)) - this will get all values past the '-'
select max({previous value}) - gets the max value from all rows of values past the '-'
+1 - adds one to the max value

Hi,

Thanks a lot for your help and clarification !

This helped me a lot in implementing the logic.

If I want to learn TSQL programming, where should I start... I want to start gradually and master it ....

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

glad I could help. As for where to learn tsql just doing it seems to be the best for me. Books online is a great resource. I also used w3schools when I was starting out. Reading blog and forums will help in letting you see different solutions to problems.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]