Results 1 to 2 of 2

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
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    something like this?

    if Update(Status)
    if not exists(select * from inserted i join deleted d on i.Status = d.Status)
    if exists(select * From inserted where Status = 'V') begin
    UPDATE pcac
    set pcac.evaluator_code = 'B'
    , pcac.UPDATE_ID = 'SYSTEM'
    from PER_COMP_ANCHOR_COMMENTS pcac
    join inserted i on pcac.cntrl_number = i.per_cntrl_nbr
    where pcac.evaluator_code = 'R'
    and i.Status = 'V'
    end
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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