CODEWITHSUNDEEP
sql-server-2005triggers
Jay Lee
Jay Lee

Reputation: 29

Trigger error, the error from if condition

In my application, data should be entered using a Web interface. If I enter the same section id, then error message should be displayed.

I have a meeting table, there is no value in the table. this is my trigger

Alter trigger fourone 
On MEETING
INSTEAD OF Insert
As

IF(EXISTS(
        SELECT 
          NULL 
        FROM 
          MEETING
        WHERE EXISTS
          (
             SELECT 
               NULL 
             FROM 
               inserted 
             WHERE 
               inserted.SECTION_ID=MEETING.SECTION_ID
          )
    )
)
BEGIN
  raiserror('ERRORRRRRRRR',15,1);
END

There is no error in the trigger. However, if I enter any value, then it's always displayed error message. Can you guys give me answer? thank you

Upvotes: 0

Views: 165

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

It should not be instead of, but after insert trigger. Instead of tells sql server that you are going to insert data yourself.

Your condition is also too complex:

IF EXISTS(SELECT NULL 
            FROM meetings inner join inserted 
              on inserted.SECTION_ID=MEETING.SECTION_ID
             and inserted.id <> meeting.id
          )

EDIT: correction. At the time trigger gets invoked you row is already inserted. You need to test for existance of section_id excluding rows in inserted. See revised code and replace ID with primary key of meeting table. You should add rollback after raiserror otherwise new rows will remain in meetings.

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

Maybe you shouldn't use a trigger for a job that can be handled by a UNIQUE constraint. Define such a constraint, then, after an insert attempt, catch the exception of violating the unique constraint and notify the user accordingly.

Upvotes: 2

Related Questions