SQL server trigger question

I am by no means a sql programmer and I am trying to accomplish something that I am pretty sure has been done a million times before.

I am trying to auto generate a customer number in sql every time a new customer is inserted, but the trigger (or sp?) will only work if at least the first name, last name and another value called case number is entered. If any of these fields are missing, the system generates an error. If the criteria is met, the system generates and assigns a unique id to that customer that begins with letters GL- and then uses 5 digit number so a customer John Doe would be GL-00001 and Jane Doe would be GL-00002.

I am sorry if I am asking too much but I am basically a select insert update guy and nothing more so thanks in advan开发者_开发知识库ce for any help.

---

If I were in this situation, I would:

--Alter the table(s) so that first name, last name and case number are required (NOT NULL) columns. Handle your checks for required fields on the application side before submitting the record to the database.

--If it doesn't already exist, add an identity column to the customer table.

--Add a persisted computed column to the customer table that will format the identity column into the desired GL-00000 format.

/* Demo computed column for customer number */
create table #test (
    id int identity,
    customer_number as 'GL-' + left('00000', 5-len(cast(id as varchar(5)))) + cast(id as varchar(5)) persisted,
    name char(20)
)

insert into #test (name) values ('Joe')
insert into #test (name) values ('BobbyS')

select * from #test

drop table #test

This should satisfy your requirements without the need to introduce the overhead of a trigger.

---

So what do you want to do? generate a customer number even when these fields arn't populated?

Have you looked at the SQL for the trigger? You can do this in SSMS (SQL Server Managment Studio) by going to the table in question in the Object Explorer, expanding the table and then expanding triggers.

If you open up the trigger you'll see what it does to generate the customer number. If you are unsure on how this code works, then post the code for the trigger up.

If you are making changes to an existing system i'd advise you to find out any implications that changing the way data is inputted works.

For example, others parts of the application may depend on all of the initial values being populated, so after changing the trigger to allow incomplete data to be added, you may inturn break something else.

---

You have probably a unique constraint and/or NOT NULL constraints set on the table.

Remove/Disable these (for example with the SQL-Server Management Console in Design Mode) and then try again to insert the data. Keep in mind, that you will probably not be able to enable the constraints after your insert, since you are violating conditions after the insert. Only disable or reomve the constraints, if you are absolutely sure that they are unecessary.

Here's example syntax (you need to know the constraint names):

--disable
ALTER TABLE customer NOCHECK CONSTRAINT your_constraint_name

--enable
ALTER TABLE customer CHECK CONSTRAINT your_constraint_name

Caution: If I were you, I'd rather try to insert dummy values for the not null columns like this:

insert into customers select afield , 1 as dummyvalue, 2 as dummyvalue from your datasource

---

A very easy way to do this would be to create a table of this sort of structure:

CustomerID of type in that is a primary key and set it as identity CustomerIDPrfix of type varchar(3) which stores GL- as a default value. Then add your other fields and set them to NOT NULL.

If that way is not acceptable and you do need to write a trigger check out these two articles: http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

http://www.kodyaz.com/articles/sql-trigger-example-in-sql-server-2008.aspx

Basiclly it is all about getting the logic right to check if the fields are blank. Experiment with a test database on your local machine. This will help you get it right.