If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > conditional statements in triggers

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,885
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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..
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,885
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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'
Reply With Quote
  #6 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On