这是触发器:CREATE TRIGGER [Trigger1] ON dbo.T_CHECKSTORAGEREC
INSTEAD OF INSERT
AS
DECLARE @NEXTVALUE VARCHAR(10)
EXEC GETSEQ 'S_CHECKSTORAGEREC',@NextID=@NEXTVALUE OUT
SELECT * INTO # FROM INSERTED
UPDATE # SET CHECKSTORAGERECID=@NEXTVALUE
EXEC GETSEQ 'S_CHECKSTORAGEREC',@NextID=@NEXTVALUE OUT
INSERT T_CHECKSTORAGEREC SELECT * FROM #下面是存储过程,读取ID值,然后将ID值加1。
CREATE PROCEDURE [dbo].[GetSeq] @TableName NVARCHAR(60),@NextID VARCHAR(10)
OUTPUT AS
SET NOCOUNT ON
BEGIN TRAN
UPDATE T_SEQUENCES SET NEXTVALUE=STR(CONVERT(BIGINT,NEXTVALUE)+1) WHERE TABLENAME=ltrim(rtrim(@TableName))
SELECT @NextID=ltrim(rtrim(NEXTVALUE)) FROM T_SEQUENCES WHERE TABLENAME=ltrim(rtrim(@TableName))
COMMIT TRAN
RETURN
GO如果一条INSERT INTO 语句同时插入多条数据的话就出现问题了。如何解决呢?
INSTEAD OF INSERT
AS
DECLARE @NEXTVALUE VARCHAR(10)
EXEC GETSEQ 'S_CHECKSTORAGEREC',@NextID=@NEXTVALUE OUT
SELECT * INTO # FROM INSERTED
UPDATE # SET CHECKSTORAGERECID=@NEXTVALUE
EXEC GETSEQ 'S_CHECKSTORAGEREC',@NextID=@NEXTVALUE OUT
INSERT T_CHECKSTORAGEREC SELECT * FROM #下面是存储过程,读取ID值,然后将ID值加1。
CREATE PROCEDURE [dbo].[GetSeq] @TableName NVARCHAR(60),@NextID VARCHAR(10)
OUTPUT AS
SET NOCOUNT ON
BEGIN TRAN
UPDATE T_SEQUENCES SET NEXTVALUE=STR(CONVERT(BIGINT,NEXTVALUE)+1) WHERE TABLENAME=ltrim(rtrim(@TableName))
SELECT @NextID=ltrim(rtrim(NEXTVALUE)) FROM T_SEQUENCES WHERE TABLENAME=ltrim(rtrim(@TableName))
COMMIT TRAN
RETURN
GO如果一条INSERT INTO 语句同时插入多条数据的话就出现问题了。如何解决呢?
解决方案 »
- 如何用一条语句实现这个功能,插入与张三的记录相同的工序和产量,但是名称变成李四,序号要递增.
- 关于select语句的简单问题,在线等待,马上给分!!
- 程序问题还是sql server问题?
- 一个尴尬的问题,系统数据库不显示
- SQL Server 2005中,如何防范数据库危险操作?
- 视图里group by字段有误,高分求解
- 求助一条SQL语句,教简单,本人没分,请大家原谅,谢谢大家
- 如何用SQL语句实现向表中的指定位置添加列?MSSQL,急~~~~~~~~~
- 请问 查询语句中如何自动加入一列流水号
- 事物@@trancount为什么由0到2
- 请教一下如何根据接收到的'2006-12'进行查询数据库日期段为'2006-12'月开头的数据
- 根据输入的日期 自动计算间隔天数的问题
INSTEAD OF INSERT
AS
DECLARE @NEXTVALUE VARCHAR(10)
EXEC GETSEQ 'S_CHECKSTORAGEREC',@NextID=@NEXTVALUE OUT
declare @t table(iddd int IDENTITY(0,1),id int)
insert @t(id) select id from inserted
SELECT *,IDENTITY(bigint,0,1) as idddd INTO # FROM INSERTED INSERT T_CHECKSTORAGEREC(CHECKSTORAGERECID,...) SELECT CHECKSTORAGERECID=@NEXTVALUE+iddd,... FROM #
INSTEAD OF INSERT
AS
DECLARE @NEXTVALUE VARCHAR(10)
EXEC GETSEQ 'S_CHECKSTORAGEREC',@NextID=@NEXTVALUE OUT SELECT *,IDENTITY(bigint,0,1) as idddd INTO # FROM INSERTED INSERT T_CHECKSTORAGEREC(CHECKSTORAGERECID,...) SELECT CHECKSTORAGERECID=@NEXTVALUE+iddd,... FROM #go--因为你用了临时表,所以没用表变量