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

    Unanswered: Problem with codes..

    the following code is working, but there seems to be some problem with the flow. when I insert the first row onto applicant table, update it and check on registration table, the status updated is correct. it will update according to the conditions given below. but somehow when i insert the 2nd row onto applicant table, update it and go back to registration table the status for the 2nd row is correct, but the status for the 1st row seems to wrong. the 1st row's status will be the same as the 2nd rows status even though the conditions are different. can anyone advise if am doing it wrong... and i hope i have placed the codes onto the code block accordingly..

    Code:
    CREATE TRIGGER dbo.registrationprocess
      ON dbo.Applicant
      	AFTER UPDATE, INSERT
    AS
     	 BEGIN
    
    UPDATE dbo.registration
    SET StatusofAppln = 
    
    -- Updating the column StatusofAppln in the registration table to Approved --
    
    	CASE i.EmploymentStatus WHEN 'Employed' THEN
    		CASE WHEN i.GrossMthlyIncome > 2500 THEN
    			'Approved'
    
    -- Updating the column StatusofAppln in the registration table to Reviewing--
    
    			WHEN i.GrossMthlyIncome < 1000 THEN 
    			'Reviewing'
    		END
    
    -- Updating the column StatusofAppln in the registration table to Rejected--
    
    		ELSE CASE i.EmploymentStatus WHEN 'Unemployed' THEN
    			'Rejected'
    		END
    	END
    
    -- Join statement for the Applicant and Registration table--
    
    		FROM Applicant As a
    		INNER
    		JOIN inserted As i
    			ON a.NRIC = i.NRIC
    
    	END
    	
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've been battling with this for a while. I think someone has already suggested that you should just calculate this when you need to view it (i.e. don't store it). Is there a good reason you aren't doing this? Triggers are tricky to write and debug.

    Also, you say you join Applicant and Registration table, but you actually join applicant and inserted for
    registrationprocess.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    20
    I have been stuck with this coz, i need to also update this status field automatically when the conditions are met. i can only think of triggers which do auto update to the tables...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Take me back one step - why do you need to update status column in the table? When that column is updated, what do you do with it? For example, is it just so that users can see the status?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Posts
    20
    Based on the status update, i am inserting columns onto another table. so when this column is approved, that row will be inserted onto the table.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE VIEW dbo.have_you_even_considered_this
      AS
    SELECT the_columns
         , from_the
         , applicant_table
         , CASE i.EmploymentStatus WHEN 'Employed' THEN
          		CASE WHEN GrossMthlyIncome > 2500 THEN
          			'Approved'
          			WHEN GrossMthlyIncome < 1000 THEN 
          			'Reviewing'
          		END
          		ELSE CASE EmploymentStatus WHEN 'Unemployed' THEN
          			'Rejected'
          		END
          	END As computed_column
    FROM   dbo.applicant
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rather than three case statements:
    Code:
    CREATE VIEW dbo.have_you_even_considered_this
      AS
    SELECT the_columns
         , from_the
         , applicant_table
         , CASE i.EmploymentStatus WHEN 'Employed' AND GrossMthlyIncome > 2500 THEN
                      'Approved'
                      WHEN i.EmploymentStatus = 'Employed' AND GrossMthlyIncome < 1000 THEN 
                      'Reviewing'
                  WHEN i.EmploymentStatus = 'Unemployed' THEN
                      'Rejected'
              END As computed_column
    FROM   dbo.applicant
    What happens if they are employed but earning between 2500 and 1000?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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