我刚学习不久,请教各位师哥一个事务应该怎么写!我有一张表table1结构、内容如下:
id tname
1 aa
2 bb
3 cc我想写一个事务,就是有一批数据,检查条件是否匹配,条件匹配就更新数据,如果发现有一条不匹配就回滚,以前更新的内容都失效,不要更新。例如我有一些数据1 aa
2 bb
3 xx分别检查if(select count(*) from table1 where id=1 and tname='bb') 如果有记录
就更新 update table1 set tname='aa' where id=1大概就这个意思,不知道师哥们看懂没!小妹才疏学浅,请各位师哥赐教!
id tname
1 aa
2 bb
3 cc我想写一个事务,就是有一批数据,检查条件是否匹配,条件匹配就更新数据,如果发现有一条不匹配就回滚,以前更新的内容都失效,不要更新。例如我有一些数据1 aa
2 bb
3 xx分别检查if(select count(*) from table1 where id=1 and tname='bb') 如果有记录
就更新 update table1 set tname='aa' where id=1大概就这个意思,不知道师哥们看懂没!小妹才疏学浅,请各位师哥赐教!
begin tran
if(select count(*) from table1 where id=1 and tname='bb')
update table1 set tname='aa' where id=1
begin
commit tran
end
else
begin
rollback tran
end
go
if exists(select count(*) from table1 where id=1 and tname='bb')
update table1 set tname='aa' where id=1
begin
commit tran
end
else
begin
rollback tran
end
go
IF NOT EXISTS
(SELECT * FROM B WHERE NOT EXISTS
(SELECT 1 FROM A WHERE ID=B.ID AND TNAME=A.TNAME)
)更新表,否则不做
begin tran
if exists(select count(*) from table1 where id=1 and tname='bb')
update table1 set tname='aa' where id=1
begin
commit tran
end
else
begin
rollback tran
end
go这段代码我放查询分析器里执行告诉我错误啊!服务器: 消息 156,级别 15,状态 1,行 7
在关键字 'else' 附近有语法错误。
IF NOT EXISTS
(SELECT * FROM B WHERE NOT EXISTS
(SELECT 1 FROM A WHERE ID=B.ID AND TNAME=A.TNAME)
)
可是我只有一张表啊!
例如我有一些数据 1 aa
2 bb
3 xx 你这些数据不是可以作为一张表么?
if(select count(*) from table1 where id=1 and tname='bb')>0
begin
update table1 set tname='aa' where id=1
commit tran
end
else
begin
rollback tran
end
谢谢楼上的师哥了!我是实在没写过事务的代码!Create proc testtable
begin tran
IF NOT EXISTS
(SELECT * FROM table2 WHERE NOT EXISTS
(SELECT 1 FROM table1 WHERE ID=table2.ID AND TNAME=table1.TNAME)
) insert into table1 values('xx')
begin
commit tran
end
else
begin
rollback tran
end
go
AS
begin tran
IF NOT EXISTS
(SELECT * FROM table2 WHERE NOT EXISTS
(SELECT 1 FROM table1 WHERE ID=table2.ID AND TNAME=table1.TNAME)
) begin
insert into table1 values('xx') commit tran
end
else
begin
rollback tran
end
go要写也应该这样吧,不过没必要用事务Create proc testtable
AS
IF NOT EXISTS
(SELECT * FROM table2 WHERE NOT EXISTS
(SELECT 1 FROM table1 WHERE ID=table2.ID AND TNAME=table1.TNAME)
) insert into table1 values('xx')
UPDATE authors SET au_lname = upper(au_lname)
WHERE au_lname = 'White'
IF @@ROWCOUNT = 2
COMMIT TRANIF @@TRANCOUNT > 0
BEGIN
PRINT 'A transaction needs to be rolled back'
ROLLBACK TRAN
END参考下语法