help with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with trigger

Author

Topic

nellyihu
Starting Member

8 Posts

Posted - 2011-03-26 : 10:13:34

hello please i need help with this trigger, i have a trigger that stops a record in a table from being duplicated. each time i insert a record, it fires and raises an error not minding if the condition in the trigger was true or false.

pls find trigger below



alter trigger TRG_CHECK_PROGRAMME_COURSE_DUPLICATE
On dbo.Institution_Programme_Courses
for Insert
as

declare @ProgrammeId	int,
	@CourseId	int,
	@CourseTypeId	int,
	@CourseUnit	int,	
	@SemesterId	int,
	@LevelId	int


select	@ProgrammeId=ProgrammeId,
	@CourseId=CourseId,
	@CourseTypeId=CourseTypeId,
	@CourseUnit=CourseUnit,	
	@SemesterId=SemesterId,
	@LevelId=LevelId
from	Inserted


if exists(	select	ProgrammeCourseId
			from	dbo.Institution_Programme_Courses
			where	ProgrammeId=@ProgrammeId
			AND		CourseId=@CourseId
			AND		CourseTypeId=@CourseTypeId
			AND		CourseUnit=@CourseUnit	
			AND		SemesterId=@SemesterId
			AND		LevelId=@LevelId	
         )
		begin
			RAISERROR ('This Course SetUp Already Exist, Add New Operation Aborted', 16, 1)
			rollback tran
			return		
		end

from this trigger, i am trying to prevent the following record below from repeating twice. (i am avoiding duplicate records)

@ProgrammeId,
@CourseId,
@CourseTypeId,
@CourseUnit,
@SemesterId,
@LevelId

please what am i doing wrong?

nellysoft

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-26 : 11:16:08

A few issues:

- This is a normal (after) trigger, so the contents of the INSERTED pseudo-table will already be in Institution_Programme_Courses, and therefore will always fire the error and rollback.
- Using variables to capture data from INSERTED is an incomplete technique, since INSERTs can affect more than one row.
- You don't need a trigger to prevent duplicates, just add a unique constraint or unique index on the columns indicated.

The unique constraint is the better choice because it's part of the table definition, it's more obvious than a trigger, and will perform better (less overhead) than a trigger.

nellyihu
Starting Member

8 Posts

Posted - 2011-03-26 : 17:45:17

ok, thx. will heed your advice

nellysoft

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources