Convert Function in Default Constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Function in Default Constraint

Author

Topic

deep2006
Starting Member

3 Posts

Posted - 2006-08-23 : 12:58:23

I have two fields CourseID and Erpid in table.

CourseID has identity property with integer datatype.

I need to add a default value for Erpid column which will show a value like 'A' + CourseID column. Erpid is Varchar column.

How can I use Convert function in default constraint?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-23 : 13:06:24

If you want a default then probably a trigger is easiest.
If you want it to keep that value then a computed column - or don't persist the value.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

deep2006
Starting Member

3 Posts

Posted - 2006-08-23 : 13:59:49

Thanks for the reply.

I am getting a syntax error in the following code.

What will be a correct syntax?

Thanks again.

-----------------------------------------------------

create trigger trig_insert on dbo.J_Course for insert
as
begin
Declare @erpid varchar(40)
set @erpid = select 'A' + convert (varchar(50) , CourseID) from dbo.J_Course, inserted where J_Course.Course_ID = inserted.Course_ID
update dbo.J_Course
set Erpid = @erpid
from dbo.J_Course, inserted where J_Course.Course_ID = inserted. Course_ID
End

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 02:33:38

There were so many errors in the code and logic above, so I took the liberty to rewrite the whole shebang, instead of explaining. There are other people than me who are better in that.

create trigger trig_insert on dbo.J_Course for insert 
as 
begin

UPDATE		j
SET		j.Erpid = 'A' + j.Course_ID
FROM		dbo.J_Course j
INNER JOIN	inserted ON i.Course_ID = j.Course_ID

End

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-26 : 03:59:00

Why do you want to have seperate column?
In your select statement, cant you use Select 'A'+cast(Course_ID as varchar(10)) from yourTable?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources