表A 上建立一个触发器, IF OBJECT_ID ('MYTRIGGER ', 'TR') IS NOT NULL DROP TRIGGER MYTRIGGER ; GO CREATE TRIGGER MYTRIGGER ON 表A AFTER INSERT INSERT INTO B(ID) SELECT MAX(ID) FROM A GO
用触发器.CREATE TRIGGER my_trig ON A FOR INSERT AS insert into b(id) select id from inserted GO
CREATE TRIGGER my_trig ON A FOR INSERT AS insert into b(id) select id from inserted GO
create database dbTemp; use dbTemp; raiserror('正在创建表:A表....',0,1) create table A(AID int not null identity(1,1) constraint pk_aid primary key clustered, AName nvarchar(10)) GO raiserror('正在插入A表:三条记录....',0,1) INSERT A SELECT 'zjp' UNION ALL SELECT '8023' UNION ALL SELECT 'X8023Z'GO create table B(BID int constraint FK_Bid FOREIGN KEY (BID) REFERENCES A(AID), BName nvarchar(10)) GO raiserror('正在插入B表:三条记录....',0,1) INSERT B SELECT 1,'xhy' UNION ALL SELECT 2,'123' UNION ALL SELECT 2,'456'--创建插入两表的存储过程 --插入开始 create procedure SP_INSERT_AB @AName nvarchar(10), @BName nvarchar(10) WITH ENCRYPTION as begin tran--启动事务 declare @ERROR int set @ERROR=0 declare @CurrentID int begin INSERT INTO A VALUES(@AName) SET @ERROR =@ERROR +@@ERROR IF (@ERROR <>0) GOTO EXT set @CurrentID=@@IDENTITY insert into B values(@CurrentID,@BName) SET @ERROR =@ERROR +@@ERROR IF (@ERROR <>0) GOTO EXT end
--异常出口 EXT:
--判断执行状态 IF (@ERROR =0) BEGIN COMMIT END ELSE ROLLBACK go --插入结束 --执行存储过程 exec SP_Insert_AB '777','888' --select * from a --select * from b --删除数据库 go use master drop database dbTemp go
IF OBJECT_ID ('MYTRIGGER ', 'TR') IS NOT NULL
DROP TRIGGER MYTRIGGER ;
GO
CREATE TRIGGER MYTRIGGER
ON 表A
AFTER INSERT
INSERT INTO B(ID)
SELECT MAX(ID) FROM A
GO
as
insert t1(Col) values(@ID)
insert t2(Col) values(@ID)
FOR INSERT
AS
insert into b(id) select id from inserted
GO
FOR INSERT
AS
insert into b(id) select id from inserted
GO
use dbTemp;
raiserror('正在创建表:A表....',0,1)
create table A(AID int not null identity(1,1) constraint pk_aid primary key clustered, AName nvarchar(10))
GO
raiserror('正在插入A表:三条记录....',0,1)
INSERT A SELECT 'zjp'
UNION ALL SELECT '8023'
UNION ALL SELECT 'X8023Z'GO
create table B(BID int constraint FK_Bid FOREIGN KEY (BID) REFERENCES A(AID), BName nvarchar(10))
GO
raiserror('正在插入B表:三条记录....',0,1)
INSERT B SELECT 1,'xhy'
UNION ALL SELECT 2,'123'
UNION ALL SELECT 2,'456'--创建插入两表的存储过程
--插入开始
create procedure SP_INSERT_AB
@AName nvarchar(10),
@BName nvarchar(10)
WITH ENCRYPTION
as
begin tran--启动事务
declare @ERROR int
set @ERROR=0
declare @CurrentID int
begin
INSERT INTO A VALUES(@AName)
SET @ERROR =@ERROR +@@ERROR
IF (@ERROR <>0) GOTO EXT
set @CurrentID=@@IDENTITY
insert into B values(@CurrentID,@BName)
SET @ERROR =@ERROR +@@ERROR
IF (@ERROR <>0) GOTO EXT
end
--异常出口
EXT:
--判断执行状态
IF (@ERROR =0)
BEGIN
COMMIT
END
ELSE
ROLLBACK
go
--插入结束
--执行存储过程
exec SP_Insert_AB '777','888'
--select * from a
--select * from b
--删除数据库
go
use master
drop database dbTemp
go
http://www.cnblogs.com/tom-fu/archive/2008/06/25/1229314.html