Board index » delphi » Records Affected

Records Affected


2005-07-04 09:54:30 AM
delphi142
Is there an easy way to determine if an UPDATE/WHERE clause executed from a
trigger, actually changed any records?
I'm trying to determine that if an update was unsuccessful, then perform an
insert - eg
update TABLE1
Set
qty_1 = old.qty_1
where........
if no matching records then, hence none were changed, then do an insert.
What I have reviously done, is issue a select first on the where clause to
find the records, but in this case, the where clause is complex, so I would like
to avoid running it twice.
Thanks in advance
Russell.
 
 

Re:Records Affected

Try the INSERT first. It will raise an exception if there is a key
violation. Trap the exception and do the UPDATE.
--
Bill Todd (TeamB)
 

Re:Records Affected

To answer your original question, no, unfortunately there is no way to
get rows affected in a trigger.
--
Bill Todd (TeamB)
 

Re:Records Affected

Another way would be to first do a SELECT COUNT(*) FROM tablename
WHERE... and if the COUNT = 1, do the update, if it is zero do the
insert. IMHO, you should be able to make this run faster and better
than throwing an exception. I believe the exception will be raised to
the client, which is probably not what you want.
Russell A writes:
Quote
Is there an easy way to determine if an UPDATE/WHERE clause executed from a
trigger, actually changed any records?

I'm trying to determine that if an update was unsuccessful, then perform an
insert - eg

update TABLE1
Set
qty_1 = old.qty_1
where........

if no matching records then, hence none were changed, then do an insert.

What I have reviously done, is issue a select first on the where clause to
find the records, but in this case, the where clause is complex, so I would like
to avoid running it twice.

Thanks in advance
Russell.


 

Re:Records Affected

Tom Wilk writes:
Quote
Another way would be to first do a SELECT COUNT(*) FROM tablename
WHERE... and if the COUNT = 1, do the update, if it is zero do the
insert. IMHO, you should be able to make this run faster and better
than throwing an exception. I believe the exception will be raised to
the client, which is probably not what you want.
Select count(*) is expensive. Inside triggers and procs, you can use Exists
much more effectively:
if (exists (select 1 from table where...)) then
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"The purpose of morality is to teach you, not to suffer and die, but to
enjoy yourself and live." - Ayn Rand
 

Re:Records Affected

Why would you let the exception flow back to the client instead of
handling it in the stored procedure with WHEN...DO?
Doing SELECT COUNT(*) is not a good choice if there may be multiple
records found. SELECT ... WHERE EXISTS ... will be faster since the
answer is known as soon as a single record is found.
--
Bill Todd (TeamB)
 

Re:Records Affected

I did not know that EXISTS could be used in this manner. Thank you if
the tip. I will look into this technique.
Wayne Niddery [TeamB] writes:
Quote
Tom Wilk writes:

>Another way would be to first do a SELECT COUNT(*) FROM tablename
>WHERE... and if the COUNT = 1, do the update, if it is zero do the
>insert. IMHO, you should be able to make this run faster and better
>than throwing an exception. I believe the exception will be raised to
>the client, which is probably not what you want.


Select count(*) is expensive. Inside triggers and procs, you can use Exists
much more effectively:

if (exists (select 1 from table where...)) then

 

Re:Records Affected

Well... once again I am humbled. I have never used a WHEN... DO
statement inside a stored proc or trigger to handle an exception
condition.
Is this technique documented with examples in the standard PDF
documentation?
Bill Todd writes:
Quote
Why would you let the exception flow back to the client instead of
handling it in the stored procedure with WHEN...DO?

Doing SELECT COUNT(*) is not a good choice if there may be multiple
records found. SELECT ... WHERE EXISTS ... will be faster since the
answer is known as soon as a single record is found.

 

Re:Records Affected

It is documented in the Language Reference. I do not recall if there is
an example or not. There may be an example in the section on stored
procedures and triggers in the Data Definition Guide.
--
Bill Todd (TeamB)
 

Re:Records Affected

Look at CREATE EXCEPTION in chapter 2 of the Language Reference and WHEN
DO in chapter 3 of Language Refence for simple examples.
Tom Wilk writes:
Quote
Well... once again I am humbled. I have never used a WHEN... DO
statement inside a stored proc or trigger to handle an exception condition.

Is this technique documented with examples in the standard PDF
documentation?


Bill Todd writes:

>Why would you let the exception flow back to the client instead of
>handling it in the stored procedure with WHEN...DO?
>
>Doing SELECT COUNT(*) is not a good choice if there may be multiple
>records found. SELECT ... WHERE EXISTS ... will be faster since the
>answer is known as soon as a single record is found.
>
 

Re:Records Affected

Thank you Bill. I will read it and pass this on to my coworkers!
Bill Todd writes:
Quote
It is documented in the Language Reference. I do not recall if there is
an example or not. There may be an example in the section on stored
procedures and triggers in the Data Definition Guide.

 

Re:Records Affected

Thank you Quinn. I will read it and pass this on to my coworkers!
Quinn Wildman writes:
Quote
Look at CREATE EXCEPTION in chapter 2 of the Language Reference and WHEN
DO in chapter 3 of Language Refence for simple examples.

Tom Wilk writes:

>Well... once again I am humbled. I have never used a WHEN... DO
>statement inside a stored proc or trigger to handle an exception
>condition.
>
>Is this technique documented with examples in the standard PDF
>documentation?
>
>
>Bill Todd writes:
>
>>Why would you let the exception flow back to the client instead of
>>handling it in the stored procedure with WHEN...DO?
>>
>>Doing SELECT COUNT(*) is not a good choice if there may be multiple
>>records found. SELECT ... WHERE EXISTS ... will be faster since the
>>answer is known as soon as a single record is found.
>>