how to write trigger for autogenerated primary key of type varchar...

[Vasavi2003]

Hello techies,

I am a newbie doing a project using MS-SQL as backend and VC++ as frontend(ODBC)...
here I got some doubts....


1. I have to generate a autogenerated primary key of type varchar like this....
ADMIN-01
ADMIN-02
ADMIN-03

So here I cant use the IDENTITY(1,1) anyway since it is not an int type field. So i have written a trigger for insert as follows ... but it is not working perfectly..

please suggest the right one...the trigger is as follows..

CREATE TRIGGER vasadmin_i ON dbo.vasadminmaster
FOR INSERT
AS

DECLARE @i1 int,
@tpono varchar(200),
@test varchar(200),
@tempadminid varchar(200),
@tempid int,
@rc int

SELECT @rc=(SELECT COUNT(*) from vasadminmaster)
RAISERROR('the value of rc is %d',16,-1,@rc)
if (@rc<=1)

SELECT @i1=0
else
SELECT @i1=(SELECT MAX(CONVERT(int,SUBSTRING(adminid,4,10))) from vasadminmaster)
RAISERROR('the value of i1 is %d',16,-1,@i1)

SELECT @tpono=(SELECT i.pono FROM Inserted i)
SELECT @tempid=@i1+1
RAISERROR('the value of tempid is %d',16,-1,@tempid)

SELECT @tempadminid='ADM'+CONVERT(varchar(200),@tempid) --CONVERT(varchar(100),@i1+100)
RAISERROR('the tempadminid is %s',16,-1,@tempadminid)

--Insert inserted.adminid values(@tempadminid)

--Update vasadminmaster set inserted.adminid=@tempadminid

--UPDATE vasadminmaster set Inserted.adminid'ADM'+CONVERT(varchar(50),@tempid) --where pono=@tpono

UPDATE vasadminmaster set adminid='ADM'+CONVERT(varchar(50),@tempid) where pono=@tpono


BEGIN
PRINT 'running........'
END
GO

but i am getting error &quot;cannot update the inserted record&quot;

here as pono(field) is unique with help of this field i am able to enter the primary key.

But what if table has no other Unique field and have to insert the primary key automatically..


2. WITH DATE FORMAT..

actually the datetime filed stores both date and time.
But i want only time to be saved.Because from frontend VC++ i am able to store the date, but time is always 5:00:00 PM
and ofcourse I dont want it. I just want to store the date only as I have to retrieve the date from table again and display the record fields in the form and to generate reports in frontend.There in front end I am not gatting the date as datefield is stroing both time and date.

Can I truncate the datetime to date only to store the records with only date not time..



ANY HELP APPRECIATED IN ADVANCE...

HOPE I WILL GET ANSWERS FOR MY QUESTIONS...

&quot;A good scientist is a person with original ideas. A good engineer is a person who makes the design that works with as few original ideas as possible. There are no prima donnas in Enginneering&quot;-------Freeman Dyson.



With Regards,
VASAVI

 

[JohnDTampaBay]

1 - According to the trigger code, the primary key you want is a auto-increment number with a prefix of 'ADM'. How about creating an ID column to take care of the auto-increment number, then create a calculated column with a formula of

'ADM' + CAST(ID AS varchar)

. No trigger needed. I think this would work.

2 - SQL DateTime datatype will always store a time value. You can strip off the time portion when you SELECT from the table by using the CONVERT styles, see BOL.

HTH. Good luck!



--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.