Archive

Archive for February 5th, 2014

Turing Trigger program off, update some data, then turn Trigger back on

February 5th, 2014 No comments

Table trigger is important part of Syteline data integrity.  There are many validation logics safe guarded by trigger program.  But in some circumstances, you may want to turn the trigger program off, in order load certain data.  This is specially true during data conversion of your Syteline implementation, you may need to use program to load data from outside data sources.

Below sample program will turn the trigger off, update jobmatl table, then turn the trigger back on again.

declare @SavedState LongListType

, @Infobar InfobarType

EXEC dbo.SetTriggerStateSp

@SkipReplicating  = 1

, @SkipBase         = 1

, @ScopeProcess     = 1

, @PreviousState    = @SavedState OUTPUT

, @Infobar          = @Infobar  OUTPUT

, @SkipAllReplicate = 1

, @SkipAllUpdate    = 1

UPDATE jobmatl

SET pick_date = @Today

FROM #JobmatlForPickList as jm

WHERE jobmatl.RowPointer = jm.RowPointer

exec dbo.RestoreTriggerStateSp

@ScopeProcess    = 1

, @SavedState      =  @SavedState

, @Infobar         = @Infobar OUTPUT

Categories: Development, Implementation Tags: