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 3 4919
[posted and mailed, please reply in public]
(ti**********@h otmail.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
> 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(SQLExcep tion e){
e.toString();
}
(ti**********@h otmail.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(SQLExcep tion 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 EMP_TITLE field to either Ms., Mr.,
or Mrs. I am using the following code:
CREATE trigger triTitleCheck
ON employee
FOR insert, update
AS
declare @v1 varchar
|
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, VARCHAR(10)
WO.PROBLEMCODE, VARCHAR(8)
|
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 lowercase of values inserted in the db. There is one
|
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"
+
"\\root\\CIMV2:Win32_Process");
|
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 other table
with that ID.
The trigger works fine with one affected row. But when there are more
then one rows affected, i get an error.
I found out that SQL-server does not support row-level triggers.
I should probable make my own cursor and...
| |
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 different ways and none of the
versions i've made works exactly as it should.
I have an array called $PageArray which contains a sorted list of all
pages in my application. Im trying to create a recursive function(It
dosn't need to be recursive if...
|
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 determine who needs
to receive the text message then send the message to the address. Only
problem is, the employee may receive up to 4 of the same messages because
each thread gets the recors then sends the message. I need somehow to
prevent...
|
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 'BATCH_ID' I need to either update or insert a row onto
'DESCRIPTION column of STATUS' table as verified for that particular
BATCH_ID.
Any help is greatly appreciated.
|
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' does not exist
drop table log_errors_tab;
|
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 code :
Create or replace trigger bef_ins_primer
before insert on dpsj_primer
for each row
begin
insert into dpsj_primer...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |