Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Red face Unanswered: SQL Server Trigger

    Hi All,

    I am trying to bypass processing in an INSERT SQL Server Trigger when a row is added into a table by a specific stored procedure. Is there anyway before hand in SQL Server to skip the processing inside a trigger whenever it is called by a specific process or is there a way to get the stored procedure id that is inserting a row in the triggering table before processing anything.

    Need help badly.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you talking about modifying the TRIGGER?
    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
    Feb 2004
    Posts
    2
    No,

    I want to check inside the trigger (as a first step) to see if it is being activated by INSERT in specific stored procedure. Below is my trigger. I want to add a check right before IF (SELECT COUNT(*) statement to see who is activating this? If it is being activated by the stored procedure (let say A), then I want to skipp all the processing in the trigger.

    Any help in this matter will be deeply appreciated.

    Thanks.


    CREATE TRIGGER EPISODE_I
    ON EPISODE
    FOR INSERT
    AS
    SET NOCOUNT ON

    IF (SELECT COUNT(*)
    FROM EPISODE
    ,INSERTED
    WHERE EPISODE.id_epsd = INSERTED.id_epsd) > 0
    BEGIN

    DECLARE @id_case numeric(18,0)
    ,@id_prsn numeric(18,0)
    ,@id_epsd numeric(18,0)
    ,@id_cr numeric(18,0)
    ,@ts_cr datetime
    ,@id_online_trigger int
    ,@fl_exist char(1)
    ,@nm_tbl char(35)

    SELECT @nm_tbl = 'ONLINE_TRIGGER'

    SELECT @id_case = ID_CASE
    ,@id_prsn = ID_PRSN
    ,@id_epsd = ID_EPSD
    ,@id_cr = ID_CR
    ,@ts_cr = TS_CR
    FROM INSERTED

    SELECT @id_online_trigger = SEQ_VALUE
    FROM SQL_SERVER_KEY_MGR
    WHERE SEQ_NAME = @nm_tbl
    EXEC SP_GETNEXTKEY @nm_tbl

    INSERT INTO ONLINE_TRIGGER
    (CD_TXN
    ,ID_CR
    ,TS_CR
    ,CD_TRIGGER
    ,DT_BGN
    ,DT_END
    ,ID_CASE
    ,ID_PRSN
    ,ID_TRIGGER
    ,ID_CASE_TPR
    ,ID_PRSN_TPR
    ,TS_EFCT_FRM
    ,CD_LCTN_FRM
    ,CD_OFC_DIV_FRM
    ,CD_DEPT_UNIT_FRM
    ,CD_LCTN_TO
    ,CD_OFC_DIV_TO
    ,CD_DEPT_UNIT_TO
    ,ID_TRIGGER1
    ,ID_TRIGGER2
    ,ID_WRKR_FRM
    ,ID_WRKR_TO
    ,DT_BATCH_PRCS
    ,ID_ONLINE_TRIGGER)
    VALUES (+205
    ,@id_cr
    ,@ts_cr
    ,NULL
    ,NULL
    ,NULL
    ,@id_case
    ,@id_prsn
    ,@id_epsd
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,@id_online_trigger)

    SELECT @id_online_trigger = SEQ_VALUE
    FROM SQL_SERVER_KEY_MGR
    WHERE SEQ_NAME = @nm_tbl
    EXEC SP_GETNEXTKEY @nm_tbl

    INSERT INTO ONLINE_TRIGGER
    (CD_TXN
    ,ID_CR
    ,TS_CR
    ,CD_TRIGGER
    ,DT_BGN
    ,DT_END
    ,ID_CASE
    ,ID_PRSN
    ,ID_TRIGGER
    ,ID_CASE_TPR
    ,ID_PRSN_TPR
    ,TS_EFCT_FRM
    ,CD_LCTN_FRM
    ,CD_OFC_DIV_FRM
    ,CD_DEPT_UNIT_FRM
    ,CD_LCTN_TO
    ,CD_OFC_DIV_TO
    ,CD_DEPT_UNIT_TO
    ,ID_TRIGGER1
    ,ID_TRIGGER2
    ,ID_WRKR_FRM
    ,ID_WRKR_TO
    ,DT_BATCH_PRCS
    ,ID_ONLINE_TRIGGER)
    VALUES (+206
    ,@id_cr
    ,@ts_cr
    ,NULL
    ,NULL
    ,NULL
    ,@id_case
    ,@id_prsn
    ,@id_epsd
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,@id_online_trigger)

    SELECT @id_online_trigger = SEQ_VALUE
    FROM SQL_SERVER_KEY_MGR
    WHERE SEQ_NAME = @nm_tbl
    EXEC SP_GETNEXTKEY @nm_tbl

    INSERT INTO ONLINE_TRIGGER
    (CD_TXN
    ,ID_CR
    ,TS_CR
    ,CD_TRIGGER
    ,DT_BGN
    ,DT_END
    ,ID_CASE
    ,ID_PRSN
    ,ID_TRIGGER
    ,ID_CASE_TPR
    ,ID_PRSN_TPR
    ,TS_EFCT_FRM
    ,CD_LCTN_FRM
    ,CD_OFC_DIV_FRM
    ,CD_DEPT_UNIT_FRM
    ,CD_LCTN_TO
    ,CD_OFC_DIV_TO
    ,CD_DEPT_UNIT_TO
    ,ID_TRIGGER1
    ,ID_TRIGGER2
    ,ID_WRKR_FRM
    ,ID_WRKR_TO
    ,DT_BATCH_PRCS
    ,ID_ONLINE_TRIGGER)
    VALUES (+200
    ,@id_cr
    ,@ts_cr
    ,NULL
    ,NULL
    ,NULL
    ,@id_case
    ,@id_prsn
    ,@id_epsd
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,@id_online_trigger)
    END

    GO

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can you add an additional colum to your table, say a bit field called BypassTrig, which would default to 0 but which your procedure's insert statement would set to 1? Then your trigger can check the status of the column to decide what to do.


    IF (SELECT COUNT(*)
    FROM EPISODE
    ,INSERTED
    WHERE EPISODE.id_epsd = INSERTED.id_epsd
    and BypassTrig = 0) > 0
    BEGIN
    .
    .
    .
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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