Create Stored Procedure that will execute from a Trigger

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RP - 27 Jul 2008 07:35 GMT

I want to create a stored procedure that creates a new ID for the Row
that is about to be inserted. The ID must eb in this format:

Current Year + Current Month + Max Record Number of this Year and
Month.

example: 2008 01 1

here, 2008 is the year, 01 is the month (to be store in two digits) ,
followed by 1 that is the first record of this Month-Year.

So the ID will look like this for few records:

2008011, 2008012, 2008013 ....

I have written this ID generation routine in .NET but I want it to be
stored as stored procedure in the database, and this stored procedure
must be fired with a BEFORE INSERT Trigger.
I am not following how to combine this all in a stored procedure.

Reply to this Message

Uri Dimant - 27 Jul 2008 08:18 GMT

Hi
create table #t (id int identity (1,1), calc as cast(year(getdate()) as
char(4))

+ right('0'+cast(month(getdate())as varchar(2)),2)

+right('0'+cast( id as varchar(2)),2))

insert into #t default values

select * from #t

Note, your trigger will have to deal with 'incorrect' days to build the
string. For example , there is no possible to get 31th day in June for
example

>I want to create a stored procedure that creates a new ID for the Row
> that is about to be inserted. The ID must eb in this format:
[quoted text clipped - 15 lines]
> must be fired with a BEFORE INSERT Trigger.
> I am not following how to combine this all in a stored procedure.

Reply to this Message

amish - 28 Jul 2008 16:00 GMT

> I want to create a stored procedure that creates a new ID for the Row
> that is about to be inserted. The ID must eb in this format:
[quoted text clipped - 15 lines]
> must be fired with a BEFORE INSERT Trigger.
> I am not following how to combine this all in a stored procedure.

Well you should use instead of trigger for this.

Thanks and Regards
Amish Shah
http://shahamishm.blogspot.com

Reply to this Message