is trigger fire during DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

is trigger fire during DTS

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-06-29 : 04:16:38

hi all,
i have a question
is trigger fire during data transformation Services

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 05:17:18

It will fire only if you're using explicit insert/update statements in Execute SQL task or use the import data (transform data) task but without fast load option. Otherwise what it does is bulk copying of data which does cause triggers to be fired.

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-07-01 : 01:55:53

I HAVE A TRIGGER BUT IT IS NOT EXECUTING
HOW TO REMOVE THE fast load option
and my trigger

CREATE TRIGGER DBO.TRG_INSERT_STDATTRIBEXPORT ON [dbo].[TBL_STDATTRIBVALUE_CLIENT]
AFTER INSERT
AS
BEGIN
DECLARE
@ERRCODE INT,
@TRANSTARTED BIT,
@L_HIPLID VARCHAR(50),
@L_STDATTRIBUTE VARCHAR(50),
@L_CLIENTITEMNO VARCHAR(50),
@L_SQL VARCHAR(2000),
@L_NOUN VARCHAR(50),
@L_MODIFIER VARCHAR(50),
@L_SOURCEDESC VARCHAR(2000),
@L_FFT VARCHAR(2000),
@L_HNMID VARCHAR(50),
@L_VALUE VARCHAR(50),
@L_SQL2 VARCHAR(2000)
SET @ERRCODE=0
SET @TRANSTARTED=0
SELECT @L_HIPLID=HIPL_ID,@L_STDATTRIBUTE=STDATTRIBUTE,@L_VALUE=STDATTRIB_VALUE FROM INSERTED
SELECT @L_CLIENTITEMNO=CLIENT_ITEMNO,@L_HNMID=HNMID,@L_SOURCEDESC=SOURCEDESC,@L_FFT=FFT FROM TBL_NMPAIRS_SOURCEDESC_CLIENT WHERE HIPL_ID=@L_HIPLID
SELECT @L_NOUN=NOUN,@L_MODIFIER=MODIFIER FROM TBL_NMTABLE WHERE HNMID=@L_HNMID
SELECT @L_CLIENTITEMNO,@L_NOUN,@L_MODIFIER,@L_SOURCEDESC,@L_FFT
SET @L_SQL=''
IF(@@TRANCOUNT=0)
BEGIN
BEGIN TRANSACTION
SET @TRANSTARTED=1
END
IF NOT EXISTS(SELECT * FROM TBL_STDATTRIB_EXPORT WHERE HIPL_ID=@L_HIPLID)
BEGIN
SET @L_SQL='INSERT INTO TBL_STDATTRIB_EXPORT(HIPL_ID,CLIENT_ITEMNO,NOUN,MODIFIER,SOURCEDESC,FFT,['+@L_STDATTRIBUTE+']) VALUES('''+@L_HIPLID+''','''+@L_CLIENTITEMNO+''','''+@L_NOUN+''','''+@L_MODIFIER+''','''+@L_SOURCEDESC+''','''+@L_FFT+''','''+@L_VALUE+''')'
SELECT @L_SQL
EXECUTE(@L_SQL)
END
ELSE
BEGIN
SET @L_SQL2=''
SET @L_SQL2='UPDATE TBL_STDATTRIB_EXPORT SET ['+@L_STDATTRIBUTE+']='''+@L_VALUE+''' WHERE HIPL_ID='''+@L_HIPLID+''''
SELECT @L_SQL2
EXECUTE(@L_SQL2)
END
IF(@@ERROR<>0)
BEGIN
SET @ERRCODE=-1
GOTO CLEANUP
END
IF(@TRANSTARTED=1)
BEGIN
SET @TRANSTARTED=0
COMMIT TRANSACTION
END
CLEANUP:
IF(@TRANSTARTED=1)
BEGIN
SET @TRANSTARTED=0
ROLLBACK TRANSACTION
END

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 02:22:57

How are you inserting in DTS? what task you're using?

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-07-01 : 02:42:48