Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001
    Location
    Canada
    Posts
    45

    Question Unanswered: Drop and create procedure

    I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
    I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

    Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...


    Thanks
    Franky
    FBoucheros@hotmail.com

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What is the trigger for?

    There's no way I know of to disable the trigger...but what's the big deal with

    DROP TRIGGER

    EXEC Sproc

    CREATE TRIGGER

    The only thing is, whatever the trigger is for, it's there for a reason, and wouldn't dropping cause you any data integrity issues?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, it can be done in SQL 2000. I have never tried it, but there is ALTER TABLE syntax for enabling/disabling a trigger.

    As Brett pointed out, though, you want to be sure that not only your process but any other process accessing the table will not be adversely affected by the sudden disabling of the trigger.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No Sheet!

    Still, once I've put a trigger in place, I've never had a need (or want) to remove it.

    You might want to consider some alternatives

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
    GO
    
    CREATE TRIGGER myTrigger99 ON myTable99
    FOR INSERT
    AS
      BEGIN
    	UPDATE t SET t.Col2 = t.Col2 * 2 
    	  FROM myTable99 t JOIN inserted i ON t.Col1 = i.Col1
      END
    GO
    
    INSERT INTO myTable99(Col2) SELECT 1
    
    SELECT * FROM myTable99
    GO
     
    ALTER TABLE myTable99
        DISABLE TRIGGER myTrigger99 
    GO
    
    INSERT INTO myTable99(Col2) SELECT 1
    
    SELECT * FROM myTable99
    GO
    
    ALTER TABLE myTable99
        ENABLE TRIGGER myTrigger99 
    GO
    INSERT INTO myTable99(Col2) SELECT 2
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TRIGGER myTrigger99
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •