Results 1 to 3 of 3

Thread: Trigger

  1. #1
    Join Date
    Jan 2002
    Posts
    28

    Unanswered: Trigger

    Hi

    The trigger is giving problem in one database by firing as a infinite loop. but the same trigger is working fine in another database.

    Please let me know what will be the senario to check...? Note both database is available in the same server...!!!

    but if i update in one database then it fills my log and locking the tables..!


    CREATE TRIGGER dbo.tr_DVCQRUpdPYMT_DETAILS
    ON dbo.TL_PYMT_DETAILS
    FOR UPDATE AS

    BEGIN

    DECLARE @rows int, @currentrow int

    SELECT @rows = @@rowcount /* Always 1 */
    IF (@rows = 0) /* If no insertion, then return... */
    RETURN

    /* If inserted */
    IF (UPDATE(STATUS_CODE) AND (SELECT COUNT(*) FROM inserted) = @rows)
    BEGIN

    DECLARE
    @today_date datetime,
    @cust_id char(7),
    @pymt_ref char(8),
    @chq_no char(8),
    @status_code tinyint

    SELECT @today_date = CONVERT(datetime, GETDATE(), 112)
    SELECT @today_date = DATEADD(minute, TIME_ZONE_DIFF, @today_date)
    FROM TL_SYS_PARM

    SELECT @cust_id = CUST_ID FROM inserted
    SELECT @pymt_ref = PYMT_REF FROM inserted
    SELECT @status_code = STATUS_CODE FROM inserted
    SELECT @chq_no = CHQ_NO FROM inserted

    IF (@status_code=76 OR @status_code=77 OR @status_code=85 OR @status_code=97 OR @status_code=99) AND
    (@chq_no <> '' OR @chq_no IS NOT NULL)
    INSERT TL_CHQ_EXCEPTION
    (
    CUST_ID,
    PYMT_REF,
    CHQ_NO,
    REPRINT_CHQ_NO,
    STATUS_DATE,
    STATUS_CODE,
    STATUS_REMARK
    )
    VALUES
    (
    @cust_id,
    @pymt_ref,
    @chq_no,
    '',
    @today_date,
    @status_code,
    ''
    )
    END

    RETURN

    END
    go

  2. #2
    Join Date
    Mar 2002
    Posts
    3
    are u sure u don't have an insert trigger on the table(TL_CHQ_EXCEPTION) in which u are doing an insert within the trigger ?
    compare sp_depends for the object in both the databases...
    HTH

  3. #3
    Join Date
    Jan 2002
    Posts
    28
    No insert or any form of trigger for TL_CHQ_EXCEPTION

Posting Permissions

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