Trigger problem

  • I have littel problem in creating trigger

    i create table Employees which include four filed

    EmpName Sex Age Remark

    Amylee Female 45 Over Age Emp

    Jack Male 19 Young Age Emp

    i create trigger which automaticlly give Remark

    create trigger emp on employees

    for insert

    as

    if (select age from inserted)>20

    begin

    update employees

    set status=Over Age Emp’

    where age >=20

    end

    Else

    if (select age from inserted)<20

    begin

    update employees

    set status='Young Age Emp'

    where age <=20

    end

    This trigger not Work it update all data filed it give Remark

    "Over age Emp" for all emp which have under age 20 and over 20 it give same remark

  • Surely the update statements need to include a where clause condition referencing the inserted table. You only want to update the rows that triggered the trigger.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I answered this over on SQL Team also, so check back there for that response. Basically, your trigger is going to fail for multiple rows on this:

    IF (SELECT Age FROM inserted)

    Also, you don't need the IF statement - this can be rewritten to use a CASE expression to determine what values to update, as in:

    UPDATE emp

    SET Status = CASE WHEN Age > 20 THEN 'Over Age Emp'

    WHEN Age <= 20 THEN 'Young Age Emp'

    END

    FROM dbo.Employees emp

    INNER JOIN inserted i ON i.{pk column} = emp.{pk column}

    However, I would not recommend doing this - as it is not needed. You can create a computed column on the table and get the same results using the above CASE expression for the computed column.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply