Results 1 to 3 of 3

Thread: SQL trigger

  1. #1
    Join Date
    May 2002
    Posts
    12

    Unanswered: SQL trigger

    I have the following trigger, this line:

    if (Select ins.Status from inserted ins) = 'V'

    will work only if the select returns one row. If the select return more than one row, it doesn't not work. Would you please help me rewrite this "if" statement so it can work with multiple rows. Thank you very much for your help.

    CREATE TRIGGER ApproverReview ON PER_CONTROL
    FOR INSERT, UPDATE
    AS

    if Update(status)
    if (select count(*) from inserted, deleted where inserted.Status = deleted.Status) = 0

    if (Select ins.Status from inserted ins) = 'V'

    BEGIN
    UPDATE PER_COMP_ANCHOR_COMMENTS
    set evaluator_code = 'B', UPDATE_ID = 'SYSTEM'
    where evaluator_code = 'R' and
    cntrl_number in (Select ins.per_cntrl_nbr from inserted ins)

    END

  2. #2
    Join Date
    Jan 2002
    Location
    Toronto
    Posts
    21
    CREATE TRIGGER ApproverReview ON PER_CONTROL
    FOR INSERT, UPDATE
    AS

    if Update(status)
    if (select count(*) from inserted, deleted where inserted.Status = deleted.Status) = 0 -- Why do you need this condition ?

    -- DELETED if (Select ins.Status from inserted ins) = 'V'

    BEGIN
    UPDATE PER_COMP_ANCHOR_COMMENTS
    set evaluator_code = 'B', UPDATE_ID = 'SYSTEM'
    where evaluator_code = 'R' and
    cntrl_number in (
    Select ins.per_cntrl_nbr from inserted ins
    where ins.Status = 'V'
    )

    END

  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb Use a cursor the process your datas

    Hello,

    I think you have to use a cursor to process your datas:
    Instead of using the select statement - use the cursor with open, fetch and close.

    Perhaps you can explain a little more detailed, what you would like to do
    with the trigger ?

    Regards
    Manfred
    (Alligator Company)
    http://www.alligatorsql.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
  •