SQL Server Trigger

[irusul]

I need help on creating a trigger for insert and update. My table looks like this: tblCandidates canID smallint PK identity ID int FK eleID smallint FK What I need to do is: During insert or update, if there are two records with the same ID and eleID, operation should be aborted and to return some string as output (ex: "operation impossible") Thank you

[Giorgi Dalakishvili]

Why not include eleID in primary key?

Giorgi Dalakishvili #region signature my articles #endregion

[Mycroft Holmes]

I don't think a trigger works like that. As I never use triggers this is conjecture Insert/Update causes trigger to execute, the trigger is a seperate stored procedure and cannot affect the outcome of whatever causes the insert/update. However: You really should put this check in the initial method, otherwise you are programming by error. IE try something if there is an error then it is wrong ahh do something else.

Never underestimate the power of human stupidity RAH

[TheFM234]

Easiest way to do this is by creating an unique non-clustered index (it could be clustered, but since there is a PK on the table a clustered index should have been created by default as the PK columns): Create unique nonclustered index IX_Id_eleID on ( Id ,eleId ) When duplicate rows are inserted, it will give the following error: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.cTest' with unique index 'IX_Id_eleID'. Just put the name of the table in place of , and name the index how you want.

[Mycroft Holmes]

Scott While this works and is probably the correct solution it is still "programming by error" and you have an additional constraint on the table to support sloppy development. I would do the check BEFORE attempting to insert/update the record, this may entail an additional index so it may nullify the constraint argument.

Never underestimate the power of human stupidity RAH