Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    20

    conditional statements in triggers

    how do i write conditional triggers in MS SQL Server 2008.

    Let me elaborate,

    if employement_status = employed and salary = 2500 then

    (i want to update the status in another table as "approved")

    elseif employment_status = unemployed then

    (i want to update the status in another table as "rejected") lastly

    if employment_status = employed and salary = 1000 then

    (i want to update the status in another table as "reviewing")

    can the above be achieved using triggers? kindly advise. thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,082
    Code:
    CREATE TRIGGER dbo.trigga
      ON dbo.tablez
      AFTER UPDATE, INSERT
    AS
      BEGIN
    
        UPDATE dbo.other_table
        SET    status = CASE i.employment_status
                          WHEN 'emloyed' THEN
                            CASE i.salary
                              WHEN 2500 THEN 'approved'
                              WHEN 1000 THEN 'reviewing'
                              ELSE 'some other status'
                            END
                          WHEN 'unemployed' THEN 'rejected'
                          ELSE 'some other status'
                        END
        FROM   dbo.other_table As a
         INNER
          JOIN inserted As i
            ON a.common_field = a.common_field
    
      END
    GO
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2009
    Posts
    20
    just checking the case statement will not allow for greater than and lesser than signs is it?

    for exampple,

    WHEN > 2500 THEN 'Approved'

    it gives me an error..

    i also tried

    WHEN i.GrossMthlyIncome > 2500 THEN 'Approved'

    it still gives me error..

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,082
    Just goes to show you need to provide all this information upfront, eh

    Code:
    CASE WHEN i.salary > 2500 THEN
      'Approved'
    ELSE
      CASE WHEN i.salary < 1000 THEN
        'Rejected'
      ELSE
        'Some other status'
      END
    END
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2009
    Posts
    20
    UPDATE dbo.registration
    SET StatusofAppln =
    CASE i.EmploymentStatus WHEN 'Employed' THEN

    CASE WHEN i.GrossMthlyIncome > 2500 THEN
    'Approved'
    ELSE
    CASE WHEN i.GrossMthlyIncome < 1000 THEN
    'Reviewing'
    END

    CASE i.EmploymentStatus WHEN 'Unemployed' THEN
    'Rejected'
    END
    END
    FROM Applicant As a
    INNER
    JOIN inserted As i
    ON a.NRIC = i.NRIC

    END

    am i doingsomething wrong, it keeps showing incorrect syntac near the keyword CASE and keeps pointing to the statement 'rejected'

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Remove "ELSE CASE".

    BTW - use code blocks when you post (like George) as it makes the code easier to read.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    Put these computed columns in a view. I don't see why you would use a trigger to do this.

Posting Permissions

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