Trigger to increment the salary by 10%

Question

Trigger to increment the salary by 10%

zim-496632

SSC Enthusiast

Points: 186
More actions
September 25, 2007 at 6:00 pm

#181208

Posted - 09/25/2007 : 04:13:20 Show Profile Email Poster Edit Topic Reply with Quote
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEvent(TestNo,EmployeeID,TestDate)

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

zim (9/25/2007)
Posted - 09/25/2007 : 04:13:20 Show Profile Email Poster Edit Topic Reply with Quote
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEvent(TestNo,EmployeeID,TestDate)

This should NOT be a trigger. a trigger fires every time the selected event happens...insert update or delete. that means it would be possible for a single person to have their salary bumped by 10 percent EVERY TIME an event occurs, even if it's not their record. 10 inserts....my salary is suddenly huge.

this is just a report /select type of situation to determine whether they should be changed, not a trigger.

this sounds a lot like a homework problem, if it is, don't read further, because you learn nothing from copying someone elses code.

SELECT EMPLOYEEID,NAME FROM EMPLOYEE

INNER JOIN (

SELECT EMPLOYEEID, COUNT(TESTNO) FROM TESTEVENT GROUP BY EMPLOYEEID, TESTDATE HAVING COUNT(TESTNO) >= 3 ) X ON EMPLOEE.EMP_LOYEEID = X.EMPLOYEEID

WHERE TYPe='technician'

This should be nothing more than a report/query to determine who

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ya this is definitly a reporting task. You might want to have a job run every X hours to be able to have a quick notification to HR that an employe needs a raise. But that's the closed that you should need to go to something that seems like event tied.

Also you need to clarify how this 10% rule is meant to work. Can the techs get their salary updated only once?, Once per x period, everytime they have 3 tests in a day?

The needs are not quite well defined in your post... but maybe you have more information than us on that matter.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

Heh... yeah... please, hire me before you install it as a trigger 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Why Jeff!!, don't like the challenge of the aftermath? :hehe:.

Lowell SSC Guru Points: 323518 More actions · Answer 5

yeah hire me too and i'll be a testing maniac! getting my pay to go up 10% per day of testing would be awesome.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Isn't it strange how QA just became that much more attractive somehow?

zim-496632 SSC Enthusiast Points: 186 More actions · Answer 7

hey the issue is question specifically says to use a DATABASE TRIGGER to solve it 🙁

zim-496632 SSC Enthusiast Points: 186 More actions · Answer 8

hey i changed ma table structure. now how should the querry be written.thank you in advance.

Employee (EmployeeID,Name,Salary,Tpye)

TestEmployee(TestNo,EmployeeID,Hrs)

Test(TestNo,TestDate,Result)

Lowell SSC Guru Points: 323518 More actions · Answer 9

ok think the business rule thru completely;

if a technician does his three tests in one day, he's qualified for a 10% raise(note you said raise and not a bonus, which makes more sense as an testing incentive)

since you only track his current salary, there's no way to know if he got the raise, or how many times the raise takes place. without some way to determine whether he qualified previously, a technician could get a 10% raise every time he did 3 tests in a single day.

It's obvious that that is not the desired effect.

if i were to test a lot for 5 days in a row, a base salary of 200 bucks a week(5 bucks an hour,40 hours) would grow to $292.82 per week. after a workmonth of 20 days, it's 1223.18 per week.

so structure wise, to prevent the tech from getting more than one raise, you need to keep track of base salary, and separately, whether he got a raise.

I keep thinking this is a homework problem, and maybe it's a trick question to make you think. What you are seeing here is people identifying the flaw in design.

the syntax for a trigger is in Books On Line. show us what you have so far, and we can offer suggestions. your new table structure still does not prevent multiple salary increases, adn the test for the increase is basically the same as what i posted earlier.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Also see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89993&whichpage=2

N 56°04'39.16"
E 12°55'05.25"

Bob Fazio SSChampion Points: 10794 More actions · Answer 11

Sounds to me like an exercise (Homework) to show a knowledge of how to write a trigger 😉

Sorry Lowell didn't catch you statement the first time.

Especially considering the OP complained that we weren't answering the question.