Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    28

    Unanswered: Trigger (Magic Table - inserted )

    Hi

    While selecting any value on the magic table (inserted), the process hangs.. in certain db (inside the trigger).

    select @cust_id=CUST_ID from inserted

    But same trigger works fine in other databases.

    Please let me know why it is happening only in this database... very urgent.

    regard

    raju

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Can you provide the whole SQL for the CREATE TRIGGER statement and the DDL for the table you have the trigger attached to? That would help since the SQL looks fine.
    Thanks,

    Matt

  3. #3
    Join Date
    Jan 2002
    Posts
    28
    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)
    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

    END

    RETURN

    END

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You can combine the many selects into one:
    Code:
    SELECT @cust_id     = CUST_ID,
           @pymt_ref    = PYMT_REF,
           @status_code = STATUS_CODE,
           @chq_no      = CHQ_NO 
      FROM inserted
    Try pasting your entire SQL (without the create trigger parts) into the isql shell to see if it works in the window. Put showplan on.
    Thanks,

    Matt

Posting Permissions

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