Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>
My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
>

Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, Reuven
Thanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|

Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, ReuvenThanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|

EXCEPT Operator with UNION in SQL Server 2005

i was trying the new EXCEPT operator of sql server 2005 to get the rows from first table which are not there in the second table. its working fine. this is my scenario...suppose i have two table of identical schema and the data would look something like this :-

CREATE TABLE dbo.t1(col1 int, col2 int);
GO
CREATE TABLE dbo.t2(col1 int, col2 int);
GO

INSERT INTO dbo.t1 SELECT 1, 1;
INSERT INTO dbo.t1 SELECT 2, 2;
INSERT INTO dbo.t1 SELECT 3, 3;

INSERT INTO dbo.t2 SELECT 1, 1;
INSERT INTO dbo.t2 SELECT 2, 2;
INSERT INTO dbo.t2 SELECT 6, 7;
GO

SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2 -- THis statement will return 3rd row from table one which is quite obevious

SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1 -- This statment will return the 3rd row from Table 2 which is also quite normal

Now i want to get both rows (from both tables) and i am using union. But what i get is only the row from the table T2. is this a normal behaviour ?

--How can we interpret this behaviour
SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2
union
SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1
GO

Madhu

Madhu,

I think that has to do with the 'order of precedence'. UNION takes PRECEDENCE over EXCEPT, so the way the query is executing is really:

Code Snippet

SELECT * FROM dbo.t1
EXCEPT
SELECT * FROM dbo.t2 UNION SELECT * FROM dbo.t2
EXCEPT
SELECT * FROM dbo.t1

To accomplish your stated goal, use parenthesis to control the order of precedence. Such as:

Code Snippet

(SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2)
UNION
(SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1)

Just like using arithmetic, JOINS follow Rules of Precedence. It is a good idea to always use parentheses to state your JOIN intentions.

|||

thanks a lot Arnie... i got the point... i forgot the very basics... i was just trying all the new operators in sql server 2005.... i got struck in this... thanks a lot again....

Madhu

|||Not a problem. Experimenting is how we all 'keep up' with the rapidly changing 'sea of knowledge'.

EXCEPT Operator

Hello everyone,

I have two tables athat are structurally identical. I wish to obtain rows that are in the first table that are not in the second one (e.g. the EXCEPT operator). EXCEPT is not supported directly in TSQL...

Does anyone know how to do this?!?!?

Thank you for your time

Chris

SQL Server 2005 adds support for EXCEPT and INTERSECT set operators. So you can use it if you are running SQL Server 2005. Otherwise, you will have to use EXISTS like:

select *

from first_table as t1

where not exists(

select *

from second_table as t2

where t2.key_col1 = t1.key_col1

and t2.key_col2 = t1.key_col1

...

)

Note that the EXISTS approach is slightly different in that you match against the key columns. The EXCEPT operator works based on whatever columns you have in the SELECT list which can be just the key columns. If you want to match on the non-key columns then you can replace the key columns in the WHERE clause with those.

|||

In case there are duplicate rows in first_table (admittedly a bad idea), you need DISTINCT to match the behavior of EXCEPT: select DISTINCT * from first_table as t1 where not exists( select * from second_table as t2 where t2.key_col1 = t1.key_col1 and t2.key_col2 = t1.key_col1 ... ) -- Steve Kass -- Drew University -- http://www.stevekass.com Umachandar Jayachandran - MS@.discussions.microsoft.com wrote:
> SQL Server 2005 adds support for EXCEPT and INTERSECT set operators. So
> you can use it if you are running SQL Server 2005. Otherwise, you will
> have to use EXISTS like:
>
> select *
> from first_table as t1
> where not exists(
> select *
> from second_table as t2
> where t2.key_col1 = t1.key_col1
> and t2.key_col2 = t1.key_col1
> ...
> )
>
>