trigger for checking duplicate entry

Question

4.00/5 (1 vote)

See more:

Hi everyone, back again, this time with SQL question.

I have the following SQL trigger to prevent duplicated entry.

SQL

go
create trigger insAssset
on tblAssets
for insert
as

begin 
		
		if exists(
		
			select A.ARNumber
			from tblAssets as A
			inner join inserted as N
			on A.AssetID=N.AssetID
		where
			A.ARNumber=N.ARNumber
		)		
			begin
			print 'The asset already exist in the database'
			rollback transaction
			return
			end
		else 
			commit transaction 
			print 'Asset added to the database.'
		
end

However, this trigger prevent any kind of entry into the database, even if the record doesn't exists.
What am I doing wrong? any suggestion would be very much appreciated.

Posted 9-Apr-15 22:33pm

Awoldeselassie

Updated 11-Oct-19 1:55am

Add a Solution

Comments

Herman<T>.Instance 10-Apr-15 8:52am

what do you see in SSMS when only executing the SELECT statement and not the trigger?
How can it compare against the new value?

Awoldeselassie 10-Apr-15 9:44am

Here is what I was hoping to achieve
each asset have a unique AR number. when entering new record, the script should compare the new AR number to the database. if it exists then tell the user it exists and roll back. if not then commit.
However, even if the AR number doesn't exists, I get the message 'The asset already exist in the database' and the transaction ends in the trigger.
when only executing SELECT, it works fine as it doesn't involve the trigger.

Herman<T>.Instance 10-Apr-15 9:47am

And what if the result of your select is NULL?
Has N.AssetID already been set?
Do you really need a trigger to check this or can you fix this in a Stored Procedure or Function?
Is Inserted another table than tblAssets? --> Can the JOIN function properly?

Awoldeselassie 10-Apr-15 10:03am

if the SELECT result is NULL, then it returns NULL
AssetID identity (1,1) set on, it automatically assign a unique ID
I don't know how I can achieve the same result with proc. as it cant be triggered in response to an event. if you know another way of achieving the same result, I would appreciate your help.
table such as inserted, deleted are temporarily created by SSMS to manage when INSERT or DELETE is executed. you can temporarily refer to the tables to check for conditions.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2015-04-10T05:45:00

You're joining to the inserted table on AssetID which means you aren't just looking to see that ARNumber is unique but that there must be a match for AssetID.

The easiest thing to do is not use triggers and just put a unique constraint on the column. Then SQL handles it for you.

A good general rule is if you think you have to use a trigger, you may be missing a better design decision.

This is the wrong type of things for triggers because a trigger should support working with sets, meaning that your inserted table could have 20 records in it so checking for "if exists" will affect all 20 records in the inserted table.

Go with the unique constraint.