locked
Moving data from one table to another RRS feed

  • Question

  • I have a need to move data from one table to another where the key is an Identity column and there is an INSTEAD OF INSERT trigger on the table.  How can one temporarly disable the trigger? Or is this a place you drop the triggers and re-apply after the data is moved?

     

    Wednesday, September 14, 2011 7:04 PM

Answers

  • You can right click on trigger and disable it.

    What is inside that trigger?


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Naomi N Wednesday, September 14, 2011 7:09 PM
    • Marked as answer by KJian_ Wednesday, September 21, 2011 9:14 AM
    Wednesday, September 14, 2011 7:06 PM
  • Sure it can. Check DISABLE TRIGGER topic in BOL.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by KJian_ Wednesday, September 21, 2011 9:14 AM
    Wednesday, September 14, 2011 7:29 PM

All replies

  • You can right click on trigger and disable it.

    What is inside that trigger?


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Naomi N Wednesday, September 14, 2011 7:09 PM
    • Marked as answer by KJian_ Wednesday, September 21, 2011 9:14 AM
    Wednesday, September 14, 2011 7:06 PM
  • Can this trigger disable be scrippted?  I am moving 30+ tables and I need this as a scripted so I dont need to babysit it.

    The script was written to nest for nulls and will set some default field data.  The person that wrote this was not a DB developer.  I can't re-write at this time. 

    Thanks for the answer.

     

     

    Wednesday, September 14, 2011 7:27 PM
  • Sure it can. Check DISABLE TRIGGER topic in BOL.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by KJian_ Wednesday, September 21, 2011 9:14 AM
    Wednesday, September 14, 2011 7:29 PM
  • Declare @myTable Table (SchemaName sysName, TableName sysName, TriggerName sysName, RowNo Int)
    Declare @RowNo Int, @RowCount Int, @SchemaName sysName, @TableName sysName, @TriggerName sysName, @strSQL Nvarchar(Max) 
    Set @RowNo = 1 
    Set @RowCount = 0 
    Set @strSQL = ''
    Insert Into @myTable 
    Select 
    	SysSchem.name As SchemaName
    	,sysTbls.name As TableName 
    	,SysTrig.name As TriggerName  
    	,Row_Number() Over(Order By sysTbls.Object_Id) As RN 
    From sys.triggers As SysTrig
    	Inner Join sys.Tables As sysTbls On SysTrig.parent_id = sysTbls.object_id 
    	Inner Join sys.schemas As SysSchem On sysTbls.schema_id = SysSchem.schema_id 
    
    Set @RowCount = @@RowCount 
    
    While @RowNo <= @RowCount 
    Begin 
    	Select @SchemaName = SchemaName, @TableName = TableName, @TriggerName = TriggerName From @myTable Where RowNo = @RowNo 
    	Set @strSQL = 'Disable Trigger ' + @SchemaName + '.' + @TriggerName + ' On ' + @SchemaName + '.' + @TableName 
    	Exec sp_ExecuteSQL @strSQL
    	
    	Set @RowNo += 1 
    End
    

     

    This script will disable triggers which belong to tables that you have to mention (ie. Table1, Table2).


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Wednesday, September 14, 2011 9:24 PM
  • Of course if there is only one trigger on the table or you are happy to turn off all triggers then you can simply use this...

    ALTER TABLE <tablename> DISABLE TRIGGER ALL

    ...to turn them off

    And then...

    ALTER TABLE <tablename> ENABLE TRIGGER ALL

    ...to turn them back on again

     

    Tim

    Wednesday, September 14, 2011 9:32 PM