Help with a trigger

[timmy_dale12@hotmail.com]

Hello
Im am a sql newbie who has a question concerning triggers.
Ive made this trigger:

create trigger check_if_alread y_exists on
users
for insert ,update
as
begin
begin transaction

Declare @Username varchar(20)
Declare @Username_exist s varchar(20)


select @Username = user_id
from inserted

set @Register = cursor scroll dynamic
for select user_id,
from users
order by user_id

open @Register

fetch next from @Register into @Username_exist s

while @@fetch_status = 0
begin

if(@Username = @Username_exist s)

begin
rollback transaction
print'Transacti on rollback'
end


fetch next from @Register into @Username_exist s

end

close @Register
deallocate @Register

commit transaction
end


which takes a variable from inserted and checks it with the existing one
in the database through a cursor.

My questions are
1)
to start and end the trigger should i use:
begin transaction ---- end transaction | commit transaction

or
begin ----- end

2)
Im using a cursor here , is this a good use of cursors

Mike

[Erland Sommarskog]

Re: Help with a trigger

[posted and mailed, please reply in public]

(timmy_dale12@h otmail.com) writes:[color=blue]
>
> select @Username = user_id
> from inserted[/color]

Note that since a trigger fires once per statement, the inserted table
can hold more than one row. Only getting one value to a variable is
not a good thing.
[color=blue]
> to start and end the trigger should i use:
> begin transaction ---- end transaction | commit transaction[/color]

When you detect an error situation, you should issue a ROLLBACK TRANSACTION.
You should not fiddle with BEGIN/COMMIT TRANSACTION in a trigger. A trigger
always executes in the context of a transaction, as it is part of a INSERT,
DELETE or UPDATE statement and such a statement always starts a transaction,
if there is no transaction already active.
[color=blue]
> Im using a cursor here , is this a good use of cursors[/color]

No, it is not.

There are actually a whole bunch of problems with your trigger, and which
leads to that this trigger should not exist at all.

Let's first look at the test you should make. This is a set-based version
of your cursor loop which covers all inserted rows:

IF EXISTS (SELECT *
FROM inserted i
JOIN users u ON i.user_id = u.user_id)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('One or more inserted users does already exist', 16, -1)
RETURN
END

Note here that I use RAISERROR rather than PRINT. This is because I
want the client to beware of that there was an error.

However, since all rows in inserted at this point also are in users,
this check is always going to be true, so you will always get an error
message. Thus, you cannot implement this check in a trigger at all.
Rather you should have a UNIQUE or PRIMARY KEY constraint on the user_id
column.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[timmy_dale12@hotmail.com]

Re: Help with a trigger

>[color=blue]
> IF EXISTS (SELECT *
> FROM inserted i
> JOIN users u ON i.user_id = u.user_id)
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('One or more inserted users does already exist', 16, -1)
> RETURN
> END
>
> Note here that I use RAISERROR rather than PRINT. This is because I
> want the client to beware of that there was an error.[/color]

How can i get the error message that the RAISERROR method generates,
is it returned in any way.
For example if i use a java application can i catch this in a try
block:

try {

.......
}catch(SQLExcep tion e){
e.toString();
}

[Erland Sommarskog]

Re: Help with a trigger

(timmy_dale12@h otmail.com) writes:[color=blue]
> How can i get the error message that the RAISERROR method generates,
> is it returned in any way.
> For example if i use a java application can i catch this in a try
> block:
>
> try {
>
> .......
> }catch(SQLExcep tion e){
> e.toString();
> }[/color]

That depends on the client library you are using. Since all I know about
Java is that it lies in the vicinity of Sumatra, I cannot say for sure
what happens, but I would expect an exception to be thrown, yes. (Provided
that you for the severity level specify 11 or higher.)

What can be puzzling is if the SQL code generates result sets, before
the RAISERROR statement, then you need to get past the result sets before
the exception is thrown. (Whether this applies to Java, I don't know,
but it happens with ADO.) Furthermore, there are more result sets you
may expect, because these "2 rows affected" you can see in Query Analyzer
from an INSERT, UPDATE or DELETE statement is also some sort of result
set.

SET NOCOUNT ON removes these "result sets", and is generally good for
performance, so use this.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp