CREATE PROCEDURE TEST_CHANGE_PR
@pV_OLDSIGMA_DEPCD VARCHAR(20), /*실적 이관전 부서코드*/
@pV_NEWSIGMA_DEPCD VARCHAR(20), /*실적 이관후 부서코드*/
@pV_UPDATE_USER VARCHAR(20)
AS
BEGIN
DECLARE @pV_SQLSTRING VARCHAR(4000)
/* TRIGGER DISABLE SQL 커서 시작 */
DECLARE C_TRIGGER_SQLSTRING INSENSITIVE CURSOR FOR
SELECT 'ALTER TABLE ' || B.name || ' DISABLE TRIGGER ' || D.name
FROM syscolumns A, sysobjects B, sysdepends C, sysobjects D
WHERE A.id = B.id
AND B.id = C.depid
AND B.type='U'
AND C.id = D.id
AND D.type='TR'
AND A.name = 'V_SIGMA_DEPTCD'
AND B.name IN ('SPR_PROSIGMADEPT')
OPEN C_TRIGGER_SQLSTRING
WHILE(@@FETCH_STATUS = 0)
BEGIN
FETCH C_TRIGGER_SQLSTRING into @pV_SQLSTRING
--EXEC(@pV_SQLSTRING)
insert into TEST_CHANGE values('1')
END
CLOSE C_TRIGGER_SQLSTRING
DEALLOCATE C_TRIGGER_SQLSTRING
/* TRIGGER DISABLE SQL 커서 시작 */
DECLARE C_DISABLE_TRIGGER_SQLSTRING INSENSITIVE CURSOR FOR
SELECT 'ALTER TABLE ' || B.name || ' DISABLE TRIGGER ' || D.name
FROM syscolumns A, sysobjects B, sysdepends C, sysobjects D
WHERE A.id = B.id
AND B.id = C.depid
AND B.type='U'
AND C.id = D.id
AND D.type='TR'
AND A.name = 'V_SIGMA_DEPTCD'
AND B.name IN ('SPR_PROSIGMADEPT')
--커서 OPEN
OPEN C_DISABLE_TRIGGER_SQLSTRING
WHILE(@@FETCH_STATUS = 0)
BEGIN
FETCH C_DISABLE_TRIGGER_SQLSTRING into @pV_SQLSTRING
--EXEC(@pV_SQLSTRING)
insert into TEST_CHANGE values('2')
END
CLOSE C_DISABLE_TRIGGER_SQLSTRING
DEALLOCATE C_DISABLE_TRIGGER_SQLSTRING
END
위와 같이 프로시저 내에서 cursor 수행시에 두번째 커서에서 수행하는 insert 문이 안됩니다.
여러 검색을 통해
도움 부탁드립니다... |