How to make sure in an insert statement that the vendor being inserted does not exist?

I am using INSERT into Vendors (....) VALUES (...) form of insert statement. How can I make sure that the insert fails if thie new vendor's vendorname exists?

I cannot create a unique index on 'VendorName' column since it is more than 900 bytes and SQL Server will not allow to create index on a column bigger than 900 bytes.

There was a readers challenge article in sqlmag that asked that question.
The winning solution was a trigger that easily be adapted for your table.

http://www.sqlmag.com/articles/index.cfm?articleid=93824

I did a quick test of their posted code and it appears to do exactly what you need.

let me know if you have any trouble adapting what they published.

|||

I have got bad vibes from DBA's about triggers and that has made me apprehensive about them.

Is there a non-trigger solution to this problem?

I was thinking of the following logic, but need help to implement it:

1. start a transaction that locks the entire Vendors table for updates like inserts, deletes and update statements so no one else can insert a new Vendor row.
2. then check count of vendors with vendorname = @.vendorname and if count >0 then exit the transaction
else
3. insert the new row for new vendor and commit the transaction in case of no errors.|||

bad vibes is no reason to avoid triggers. they are a very powerful feature of sql server.

if you read through the trigger i referenced, you'll see that it is basically doing what you are suggesting with the exception that it doesnt lock the entire table.

|||

Triggers are usually a maintenance headache. A better way to handle this would be from the stored proc where you are doing the insert.

IF NOT EXISTS (SELECT * FROm yourTable WHERE <condition>)

BEGIN

--do your insert here.

END

checking for records using EXISTS works fastest since SQL Server will only check for existance using indexes and not scan the entire table or return a huge result set. EXISTS just returns TRUE or FALSE.

|||

The downside of not using the trigger is that

a) every stored proc that can insert or update must be modified similarly.

b) there is still no guarantee that the table would contain unique data. date can be entered directly by a dba bypassing the checks in the procs.

also note that the size of the column precludes the use of an index, so a table scan will occur whichever way you choose.

|||

Dinakar,

There is one problem with your suggested solution.

Between the if (select...) and the actual insert statement, there will be a time gap (most likely of the order of milliseconds or less), and if in that time gap, another user inserts a vendor with same name, then the current stored procedure will end up inserting a duplicate vendor name.

Do you have a solution for the above described problem, because then your sp would be perfect?

Thanks

|||

mbanavige:

a) every stored proc that can insert or update must be modified similarly.

I agree. If having a unique value was a requirement it should have been coded that way from the beginning. Generally triggers are a big NO-NO. They may have their advantages but their disadvantages outweight the little benefit they bring.

mbanavige:

b) there is still no guarantee that the table would contain unique data. date can be entered directly by a dba bypassing the checks in the procs.

Generally, in a proper production environment, there should not be direct INSERTs into a table. ALL operations should be done through stored procs. Again its easier in the long run maintenance. If there is a schema change on the table a developer has to dig through his entire application to find where all the INSERTs were and modify.

mbanavige:

also note that the size of the column precludes the use of an index, so a table scan will occur whichever way you choose.

I agree, again. The enforcement can be made from the stored proc. To avoid the table scan, the additional column approach as mentioned in the article can still be used. Instead of querying for the combined columns from the trigger, it can be done in a stored proc.

|||

sun21170:

Between the if (select...) and the actual insert statement, there will be a time gap (most likely of the order of milliseconds or less), and if in that time gap, another user inserts a vendor with same name, then the current stored procedure will end up inserting a duplicate vendor name.

You could use TRANsactions to control the access. If you need total granularity on the locks, check out BOL for the different locking mechanisms and the different ISOLATION LEVELs.

sql