Trigger not rolling back transaction

[bertrandkis]

I have created the following trigger
CREATE TRIGGER Learner_ITrig ON dbo.Learner
FOR insert
AS
Begin

if (select count(LearnerID) from learner where Learnerid=(select LearnerID from Inserted))>1
begin
RAISERROR ('This ID already exists in the database', 16, 1)
rollback tran
end

If ( (select Surname from Inserted) is null and (select Firstnames from Inserted) is null )
BEGIN
rollback tran
END
end

The problem is that sometimes when I insert a new record it works fine but sometimes
I get this error:
Server: Msg 3903, Level 16, State 1, Procedure Learner_UTrig, Line 17
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The statement has been terminated.
When this trigger fires,
Can someone tell me what is wrong with this trigger?

 

[SQLDenis]

you need a begin tran statement and also a commit tran statement
lookup transactions in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

why don't you create a unique constraint on that table for the LearnerID column so that you don't have to do any of this work?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

also make the columns Surname and Firstnames NOT NULL
there is not need for this trigger at all all, all this stuff can be done with constraints

or else you would have to do stuff like this inside the trigger

declare @LearnerID int
select @LearnerID = LearnerID from inserted

if (select count(LearnerID) from learner where Learnerid=@LearnerID )>1
begin
delete learner where LearnerID = LearnerID
and -- and how will you know which record to delete, some ID perhaps??



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/