.... 按楼主的想法可以用触发器,当然在程序里也可以,用SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY这三个中的某个来控制!create trigger t_in on Salary after insert as insert into SalaryDetail select a.SalaryId,b.EmployeeId,b.Salary from inserted a,[员工具体的工资表] b go
CREATE TABLE Employee( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL) SET IDENTITY_INSERT Employee ON INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立') INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵') INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强') INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三') INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四') SET IDENTITY_INSERT Employee OFF CREATE TABLE Salary( [SalaryId] [int] IDENTITY(1,1) NOT NULL, [SYear] [int] NULL, [SMonth] [int] NULL ) CREATE TABLE SalaryDetail( [SalaryId] [int] NOT NULL, [EmployeeId] [int] NOT NULL, [Salary] [money] NULL) -- 新增2011年4月工资记录 INSERT Salary(SYear,SMonth) VALUES(2011,4) -- 为每个员工增加相应工资记录 INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,2,3000) INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,3,2000) INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,4,1800) INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,5,2100) INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,6,5000) --SQL: CREATE PROC proc_test @year int, @month int AS BEGIN TRY BEGIN TRAN DECLARE @SalaryId INT INSERT Salary([SYear], [SMonth]) VALUES(@year, @month) SET @SalaryId = SCOPE_IDENTITY() INSERT SalaryDetail ( [SalaryId], [EmployeeId], [Salary] ) SELECT @SalaryId, A.[EmployeeID], 0.00 FROM Employee A COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH GO --test EXEC proc_test 2012, 4 GO SELECT * FROM SalaryDetail /* 1 2 3000.00 1 3 2000.00 1 4 1800.00 1 5 2100.00 1 6 5000.00 2 2 0.00 2 3 0.00 2 4 0.00 2 5 0.00 2 6 0.00 */
CREATE TABLE Employee(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL)INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立')
INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵')
INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强')
INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三')
INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四')CREATE TABLE Salary(
[SalaryId] [int] IDENTITY(1,1) NOT NULL,
[SYear] [int] NULL,
[SMonth] [int] NULL
)CREATE TABLE SalaryDetail(
[SalaryId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[Salary] [money] NULL)
上面是三个表的结构和部分数据,我现在要做的类似于下面的功能,至于存储过程的返回结果并不重要:-- 新增2011年4月工资记录
INSERT Salary(SYear,SMonth) VALUES(2011,4)-- 为每个员工增加相应工资记录
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,2,3000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,3,2000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,4,1800)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,5,2100)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(@SalaryId,6,5000)
....
按楼主的想法可以用触发器,当然在程序里也可以,用SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY这三个中的某个来控制!create trigger t_in on Salary
after insert
as
insert into SalaryDetail
select a.SalaryId,b.EmployeeId,b.Salary
from inserted a,[员工具体的工资表] b
go
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL)
SET IDENTITY_INSERT Employee ON
INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立')
INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵')
INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强')
INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三')
INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四')
SET IDENTITY_INSERT Employee OFF
CREATE TABLE Salary(
[SalaryId] [int] IDENTITY(1,1) NOT NULL,
[SYear] [int] NULL,
[SMonth] [int] NULL
)
CREATE TABLE SalaryDetail(
[SalaryId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[Salary] [money] NULL)
-- 新增2011年4月工资记录
INSERT Salary(SYear,SMonth) VALUES(2011,4)
-- 为每个员工增加相应工资记录
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,2,3000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,3,2000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,4,1800)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,5,2100)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,6,5000)
--SQL:
CREATE PROC proc_test
@year int,
@month int
AS
BEGIN TRY
BEGIN TRAN
DECLARE @SalaryId INT
INSERT Salary([SYear], [SMonth]) VALUES(@year, @month)
SET @SalaryId = SCOPE_IDENTITY()
INSERT SalaryDetail
(
[SalaryId],
[EmployeeId],
[Salary]
)
SELECT
@SalaryId,
A.[EmployeeID],
0.00
FROM Employee A
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO
--test
EXEC proc_test 2012, 4
GO
SELECT * FROM SalaryDetail
/*
1 2 3000.00
1 3 2000.00
1 4 1800.00
1 5 2100.00
1 6 5000.00
2 2 0.00
2 3 0.00
2 4 0.00
2 5 0.00
2 6 0.00
*/
挨个获取员工各项工资值(salarydetail表的相关字段)后插入salarydetail表;
记住用事务,这个涉及到money
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL)
SET IDENTITY_INSERT Employee ON
INSERT Employee ([EmployeeID], [Name]) VALUES (2, N'丁立')
INSERT Employee ([EmployeeID], [Name]) VALUES (3, N'李逵')
INSERT Employee ([EmployeeID], [Name]) VALUES (4, N'文强')
INSERT Employee ([EmployeeID], [Name]) VALUES (5, N'张三')
INSERT Employee ([EmployeeID], [Name]) VALUES (6, N'李四')
SET IDENTITY_INSERT Employee OFF
CREATE TABLE Salary(
[SalaryId] [int] IDENTITY(1,1) NOT NULL,
[SYear] [int] NULL,
[SMonth] [int] NULL
)
CREATE TABLE SalaryDetail(
[SalaryId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
[Salary] [money] NULL)
-- 新增2011年4月工资记录
INSERT Salary(SYear,SMonth) VALUES(2011,4)
-- 为每个员工增加相应工资记录
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,2,3000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,3,2000)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,4,1800)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,5,2100)
INSERT SalaryDetail(SalaryId,EmployeeId,Salary) VALUES(1,6,5000)
--SQL:
CREATE PROC proc_test
@year int,
@month int
AS
BEGIN TRY
BEGIN TRAN
DECLARE @SalaryId INT
INSERT Salary([SYear], [SMonth]) VALUES(@year, @month)
SET @SalaryId = SCOPE_IDENTITY()
INSERT SalaryDetail
(
[SalaryId],
[EmployeeId],
[Salary]
)
SELECT
@SalaryId,
A.[EmployeeID],
0.00
FROM Employee A
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO
--test
EXEC proc_test 2012, 4
GO
SELECT * FROM SalaryDetail
/*
1 2 3000.00
1 3 2000.00
1 4 1800.00
1 5 2100.00
1 6 5000.00
2 2 0.00
2 3 0.00
2 4 0.00
2 5 0.00
2 6 0.00
*/