is rollback possible here?

hi friends,

is it possible to roll back a transaction here? i heard that it will be possible only if we wrote the code only in certain block.please tell me the block.

vinodcan you post the sample query

Roll back can happen
1. if you put your statements inside a 'BEGIN TRAN ...ROLLBACK TRAN ' statements

2. if you stop the running Query before it completes

Is possible of using one single statement for inserting two tables?

Hi,

I need to know whether it is possible of using one single SQL statemnet to implement inserting and updating operations on two tables.

The back ground information is that the two tables are related with the following structure:

user_group_A table
id int -- PK,
field1 varchar(20),
field2 varchar(20)

user_group_B table
id int -- PK reference user_group_B,
field3 varchar(20),
field4 varchar(20)

that is the group A has field1 and field2, while the B has field1, field2, field3, and field3.

Thanks for you input.

v.Originally posted by Vernon
Hi,

I need to know whether it is possible of using one single SQL statemnet to implement inserting and updating operations on two tables.

The back ground information is that the two tables are related with the following structure:

user_group_A table
id int -- PK,
field1 varchar(20),
field2 varchar(20)

user_group_B table
id int -- PK reference user_group_B,
field3 varchar(20),
field4 varchar(20)

that is the group A has field1 and field2, while the B has field1, field2, field3, and field3.

Thanks for you input.

v.
You don't say what DBMS. For Oracle, the short answer is "no".

However, there is a way in which this could be accomplished:

1) Create a view based on the join between A and B
2) Create an INSTEAD OF INSERT trigger on the view, and in the trigger code perform inserts into both A and B.|||Thanks Tony for your advice.

I prefer a genertc solution over the DB special one. I currently use PostgreSQL for this project.

I have a look at the View. As the name indicated, it is for view only. Only a selection query can be operated on it. I also find another mechanism called rule, which can be used to insertion and update queries. I don't know whether it is a standard or not.

Can you elaborate the second point you stated?

Thank again.

Vernon|||Originally posted by Vernon
Thanks Tony for your advice.

I prefer a genertc solution over the DB special one. I currently use PostgreSQL for this project.

I have a look at the View. As the name indicated, it is for view only. Only a selection query can be operated on it. I also find another mechanism called rule, which can be used to insertion and update queries. I don't know whether it is a standard or not.

Can you elaborate the second point you stated?

Thank again.

Vernon
My second point refers to an Oracle feature, the INSTEAD OF [INSERT/UPDATE/DELETE] trigger. It allows you to override the default action for an insert etc. on a view. For example, for a view V based on tables A and B you might define a trigger:

CREATE OR REPLACE TRIGGER v_trg1
INSTEAD OF INSERT ON V
FOR EACH ROW
BEGIN
INSERT INTO A( id, ... ) VALUES (:NEW.id, ... );
INSERT INTO B( id, ... ) VALUES (:NEW.id, ...);
END;

is my table hosed?

SELECT COUNT(*) FROM mytable
SELECT * FROM mytable
SELECT * FROM mytable ORDER BY mytableID
I'm getting back three different counts (using query analyzer) from
the above queries.
The first returns the value 228.
The second returns 160 rows of data.
The third returns 94 rows of data (all rows have a valid mytableID).
WTF? If I use Enterprise Manager and look at the table, I can see all
the rows (I think) and it looks OK. This query:
SELECT * FROM mytable WHERE mytableID = ID
returns the correct 2 rows I expect, although they are not listed in
either query above.
Any clues? Is the table index screwed up? Is that fixable?
It's possible that another process was deleting rows while you ran your
queries.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
SELECT COUNT(*) FROM mytable
SELECT * FROM mytable
SELECT * FROM mytable ORDER BY mytableID
I'm getting back three different counts (using query analyzer) from
the above queries.
The first returns the value 228.
The second returns 160 rows of data.
The third returns 94 rows of data (all rows have a valid mytableID).
WTF? If I use Enterprise Manager and look at the table, I can see all
the rows (I think) and it looks OK. This query:
SELECT * FROM mytable WHERE mytableID = ID
returns the correct 2 rows I expect, although they are not listed in
either query above.
Any clues? Is the table index screwed up? Is that fixable?
|||On Jan 17, 9:33 pm, "bryan...@.hotmail.com" <bryan...@.hotmail.com>
wrote:
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
How about Query analyser set to restrict the records returned - so
when you do a SELECT * FROM you get the reduce count. The will get the
correct count when you do SELECT COUNT(1).
Also, you could have the SET ROWCOUNT set to 160.. but that would just
weird.

> The rst returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?
|||On Jan 17, 1:33Xpm, "bryan...@.hotmail.com" <bryan...@.hotmail.com>
wrote:
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
Found the issue... Bad data in some numeric columns. Some #INF, etc.
values that shouldn't have been there. Saw the error message from
QueryAnalyzer when I finally bothered to click on the Messages tab.
You'd think they would have made this more obvious... :P
|||Are you in READ UNCOMMITTED isolation level, by any chance?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?

is my table hosed?

SELECT COUNT(*) FROM mytable
SELECT * FROM mytable
SELECT * FROM mytable ORDER BY mytableID
I'm getting back three different counts (using query analyzer) from
the above queries.
The first returns the value 228.
The second returns 160 rows of data.
The third returns 94 rows of data (all rows have a valid mytableID).
WTF? If I use Enterprise Manager and look at the table, I can see all
the rows (I think) and it looks OK. This query:
SELECT * FROM mytable WHERE mytableID = ID
returns the correct 2 rows I expect, although they are not listed in
either query above.
Any clues? Is the table index screwed up? Is that fixable?It's possible that another process was deleting rows while you ran your
queries.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
SELECT COUNT(*) FROM mytable
SELECT * FROM mytable
SELECT * FROM mytable ORDER BY mytableID
I'm getting back three different counts (using query analyzer) from
the above queries.
The first returns the value 228.
The second returns 160 rows of data.
The third returns 94 rows of data (all rows have a valid mytableID).
WTF? If I use Enterprise Manager and look at the table, I can see all
the rows (I think) and it looks OK. This query:
SELECT * FROM mytable WHERE mytableID = ID
returns the correct 2 rows I expect, although they are not listed in
either query above.
Any clues? Is the table index screwed up? Is that fixable?|||On Jan 17, 9:33 pm, "bryan...@.hotmail.com" <bryan...@.hotmail.com>
wrote:
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
How about Query analyser set to restrict the records returned - so
when you do a SELECT * FROM you get the reduce count. The will get the
correct count when you do SELECT COUNT(1).
Also, you could have the SET ROWCOUNT set to 160.. but that would just
weird.
> The rst returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?|||You can try to run a DBCC CHECKTABLE against your table to be sure that it
is OK. Does it work if you specify the columns in the count function?
(select count(columnname) from mytable)
--
Rubén Garrigós
Solid Quality Mentors
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?|||On Jan 17, 1:33=A0pm, "bryan...@.hotmail.com" <bryan...@.hotmail.com>
wrote:
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
Found the issue... Bad data in some numeric columns. Some #INF, etc.
values that shouldn't have been there. Saw the error message from
QueryAnalyzer when I finally bothered to click on the Messages tab.
You'd think they would have made this more obvious... :P|||Are you in READ UNCOMMITTED isolation level, by any chance?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?