Click here to Skip to main content
15,883,796 members
Please Sign up or sign in to vote.
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
Updated 11-Oct-19 1:55am
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

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.
 
Share this answer
 
Comments
Awoldeselassie 13-Apr-15 4:38am    
Thanks for your response.
Like you said, it is much easier to use unique constraint. But I don't understand why a lot of people like to avoid triggers all together when triggers are more flexible.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900