TRIGGER일때 자동 증가값 조회

GO
/****** 개체: Trigger [dbo].[tr_del] 스크립트 날짜: 08/20/2011 19:52:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Create TRIGGER [dbo].[tr_del]
ON [dbo].[LicIssueInfo]
FOR INSERT
AS
DECLARE @idx int
DECLARE @strSql nvarchar(4000)
DECLARE @subSql nvarchar(4000)

SELECT @idx = [idx]
FROM inserted
SET @strSql = N'CREATE TABLE [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] (
[idx] [int] NOT NULL ,
[pckidx] [int] NULL ,
[userID] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
[userIP] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
[userMac] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
[userLoc] [char] (2) COLLATE Korean_Wansung_CI_AS NULL ,
[issueDate] [datetime] NULL
) ON [PRIMARY]'
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN
SET @subSql = N'INSERT INTO [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] SELECT * from LicIssueInfo WITH (NOLOCK) where idx=' + convert(char,@idx)
EXEC sp_executesql @subSql
delete from [dbo].[LicIssueInfo] where idx = @idx - 300000
END

ELSE

BEGIN
EXEC sp_executesql @strSql
SET @subSql = N'INSERT INTO [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] SELECT * from LicIssueInfo WITH (NOLOCK) where idx=' + convert(char,@idx)
EXEC sp_executesql @subSql
delete from [dbo].[LicIssueInfo] where idx = @idx - 300000
END
END

insert 자동 증가 값 조회

DECLARE @strSql nvarchar(4000)
DECLARE @strSql2 nvarchar(4000)

SET @strSql = N'CREATE TABLE [dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode +'] (
[Idx] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[uid] int
) ON [PRIMARY]'


BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
---테이블이 존재하면,
BEGIN
---수험번호 증가
--EXEC sp_executesql 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') + '_' + @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
--select @idx = @@Identity
set @strSql2 = 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
EXEC sp_executesql @strSql2
select @idx = @@Identity

END
ELSE
---테이블이 없다면,
BEGIN
---테이블 생성
EXEC sp_executesql @strSql
---수험 번호 증가
--EXEC sp_executesql 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
--select @idx = @@Identity
set @strSql2 = 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
EXEC sp_executesql @strSql2
select @idx = 1
END
END