Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: How to Block triggers while synchronisation?

    Any body knows how to block Trigger action while synchronisation?
    I am doing Audit Trailing by using Triggers and some History tables. These triggers will update each and every updation in the table to the related history table. I want to block these triggers while doing synchronisation.

    Thank you

  2. #2
    Join Date
    Sep 2003
    Location
    GENEVA
    Posts
    4

    Re: How to Block triggers while synchronisation?

    Hi,

    I don't thing you can block trigger action (as on Oracle)
    Just drop them during synchro and then recreate them

  3. #3
    Join Date
    Jun 2003
    Posts
    15
    You could modify the trigger to first check which userid is doing the dml which fires the trigger. If it is the userid running the synchronization then exit the trigger. Otherwise continue to execute the rest of the trigger code.

  4. #4
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Cool

    You can block all the triggers from one table with (ASE 12.0):

    ALTER TABLE mytable DISABLE TRIGGER
    go

    or just one trigger with:

    ALTER TABLE mytable DISABLE TRIGGER mytrigger
    go

    Then you enable again with:

    ALTER TABLE mytable ENABLE TRIGGER
    go
    ALTER TABLE mytable ENABLE TRIGGER mytrigger
    go

    If you want to know the state of all triggers in a databases, you can use this select:

    SELECT name Table_Name,
    object_name(sysobjects.instrig) TR_Insert,
    Ins_Status =
    CASE
    WHEN (sysstat2 & 1048576) = 1048576 THEN "Disable"
    ELSE "Enable"
    END,
    object_name(sysobjects.deltrig) TR_Delete,
    Del_Status =
    CASE
    WHEN (sysstat2 & 2097152) = 2097152 THEN "Disable"
    ELSE "Enable"
    END,
    object_name(sysobjects.updtrig) TR_Update,
    Upd_Status =
    CASE
    WHEN (sysstat2 & 4194304) = 4194304 THEN "Disable"
    ELSE "Enable"
    END
    FROM sysobjects
    WHERE type = "U"
    AND ( instrig <> 0
    OR updtrig <> 0
    OR deltrig <> 0 )


    Bye

    Sebastian

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    Thank you for your reply.

    But i can't disable any triggers. It will make concurrency problems. I want to disable triggers only when synchronisation happens. Is it possible to disable triggers only for that session.


    Thanks

  6. #6
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    Try to use the same user for synchronization, and then modify the trigger like this:

    IF suser_name() <> " synchronization "
    BEGIN
    ...

    END
    ELSE
    BEGIN
    ...
    or do nothing

    END

    Good luck

    Sebastian

  7. #7
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    Originally posted by svelasco
    Try to use the same user for synchronization, and then modify the trigger like this:

    IF suser_name() <> " synchronization "
    BEGIN
    ...

    END
    ELSE
    BEGIN
    ...
    or do nothing

    END

    Good luck

    Sebastian
    I agree svelasco.This is the better method solving ur problem.

  8. #8
    Join Date
    Feb 2004
    Posts
    5
    Hi,

    I think it will work. Thank you for your valuable time

    Thanks & Regards

    Surji..

Posting Permissions

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