473,379 Members | 1,330 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

Help with a trigger

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

create trigger check_if_already_exists on
users
for insert ,update
as
begin
begin transaction

Declare @Username varchar(20)
Declare @Username_exists 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_exists

while @@fetch_status = 0
begin

if(@Username = @Username_exists)

begin
rollback transaction
print'Transaction rollback'
end
fetch next from @Register into @Username_exists

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
Jul 20 '05 #1
3 4904
[posted and mailed, please reply in public]

(ti**********@hotmail.com) writes:

select @Username = user_id
from inserted
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.
to start and end the trigger should i use:
begin transaction ---- end transaction | commit transaction
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.
Im using a cursor here , is this a good use of cursors


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
>
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.


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(SQLException e){
e.toString();
}
Jul 20 '05 #3
(ti**********@hotmail.com) writes:
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(SQLException e){
e.toString();
}


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Curtis Gilchrist | last post by:
I'm trying my hand at triggers and it doesn't seem to be working for me. I have a very simple database that consists of one table: Employees. I want to create a trigger that will limit the...
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
11
by: Jules Alberts | last post by:
Hello everybody, Someone helped me earlier with this TCL trigger function: create or replace function tlow() returns trigger as ' set NEW($1) return ' language 'pltcl'; I use it to force...
11
by: ricolee99 | last post by:
Hi everyone, I'm trying to invoke my .exe application from a remote server. Here is the code: ManagementClass processClass = new ManagementClass ("\\\\" +"RemoteServerName" +...
4
by: SUKRU | last post by:
Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a...
0
by: Michael L | last post by:
Hi Guys(I apologize for the lengty post - Im trying to explain it as best i can) I've been cracking my head on this one for the past 24+ hours and i have tried creating the function in ten...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
3
by: Sam Durai | last post by:
Need help to write a trigger according to the following business requirement. This on DB2 UDB V8.2 / AIX 5.3 Whenever a 100th record is inserted into my 'ACCOUNT' table with a particular...
11
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG'...
3
by: faathir88 | last post by:
i'd like to insert lots of data n its hard to determine which field would be the primary key, coz all of them almost similar. So, i decided to use sequence for its PK by using trigger here's the...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.