最近写了一个 存储过程 触发器 事务综合小示例 同时对三张表进行更新，删除 插入 操作 供大家参考哦...

Create Table T
(
Id int,
[Name] varchar(20),
Address varchar(20),
Phone varchar(20),
Memo varchar(20)
)

create Table T1
(
Id int,
[Name] varchar(20),
Address varchar(20),
Phone varchar(20),
Memo varchar(20)
)

create Table T2
(
Id int,
[Name] varchar(20),
Address varchar(20),
Phone varchar(20),
Age int,
Fix varchar(20),
Sex int,
Memo varchar(20)
)

insert into T (id,name,address,Phone,Memo) values (1,2,'上海',15821555,'111')
insert into T (id,name,address,Phone,Memo) values (2,3,'南京',15821555,'111')
insert into T (id,name,address,Phone,Memo) values (3,4,'北京',15821555,'111')
insert into T (id,name,address,Phone,Memo) values (4,5,'深圳',15821555,'111')
insert into T (id,name,address,Phone,Memo) values (5,6,'安徽',15821555,'111')

insert into T1 (id,name,address,Phone,Memo) values (1,11,'安徽',1592222,'223')
insert into T1 (id,name,address,Phone,Memo) values (2,22,'浙江',1592223,'343')
insert into T1 (id,name,address,Phone,Memo) values (3,33,'江苏',1592224,'656')
insert into T1 (id,name,address,Phone,Memo) values (4,44,'四川',1592225,'777')
insert into T1 (id,name,address,Phone,Memo) values (5,55,'重庆',1592226,'888')

select * from T

select * from T1

select * from T2

delete from T

delete from T1

delete from T2

drop Table T

drop Table T1

drop Table T2

Create proc MyProc  
as
begin
declare @id int   ---定义变量ID---
declare @name varchar(20) ---定义变量name---
declare @address varchar(20) ---定义变量address---
declare @phone varchar(20) ---定义变量phone---
declare @Memo varchar(20) ---定义变量Memo---
declare  cur_cursor  cursor for  ---定义游标  cur_cursor---
select a.Id,a.[Name],a.Address,a.Phone,a.Memo  from T  a join T1 b on a.Id=b.Id   ---查询语句操作---
open cur_cursor --打开游标cur_cursor---
fetch next from cur_cursor into @id,@name,@address,@phone,@Memo  ---从游标中取下一行---
while(@@fetch_status=0) ---循环遍历  全局变量@@fetch_status 返回值是0时，说明Fetch语句成功---
begin
update T1 set [name]=@name,Address=@address,Phone=@phone,Memo=@Memo where Id=@id 
delete from T where Id=@Id
insert into T2(Id,[Name],Address,Phone,Memo) values (@id,@name,@address,@phone,@Memo)

fetch next from cur_cursor into @id,@name,@address,@phone,@Memo ---从游标中取下一行
end
deallocate cur_cursor  ---删除游标

end

exec MyProc  ---执行存储过程 MyProc

drop proc MyProc  ---删除存储过程MyProc

转载于:https://www.cnblogs.com/fly_Net/archive/2012/02/16/2354533.html