Help me please! It is about a project for College| TRIGGER

Mgrmanu10 wrote:Hi everyone! I have to "make" a trigger which is about that if a employee has 4 faults (he didnt go to work for examples 4 times), the database notify us that the employee must be to be fired.Any idea about the code that i must to implement??*Sorry about my english, i am from Spain it was 5 years ago when i finished my english classes...

Welcome to the forum.

Your English is much better than my Spanish.

In addition to Frank's comments, please be aware of a few "cultural" items.  In no particular order:

- This is not a code writing service.  People will be glad to help, but you need to show some initiative by showing your own work and explaining where you are having difficulty.  The points Frank made will go a long way in this area.

- Also as Frank mentioned, what you pose is really (really, really) not a good use of triggers.  Knowing this is a school assignment will help deflect some of the criticism of that, as most of us recognize that a lot of assignments are given to teach a technique without regard to whether the technique is the best fit for the stated problem.  If this were an actual business requirement, people would spend more time explaining why triggers are not the way to solve the problem - both from a technical as well as a business standpoint.

- Some of the most knowledgeable people here are also the most likely to "not suffer fools gladly".   Just put on your "big boy pants", think twice and thrice before taking things personally.

If your teachers says you must use a trigger, then I would do a trigger on update of the employee record. Inside that trigger the logic would be increase the employee's faults by one then check if he is now at 4 faults. If he is at 4 faults maybe do a DBMS_OUTPUT.PUT_LINE(' This employee needs to be fired he has 4 faults');

It would look like

create or replace trigger TRIG_TABLE_NAME

before update

  on TABLE_NAME

   for each row

         begin

               if old.fault_value >= 3 then

                    dbms_output.put_line('this player needs to be fired')

               end if;

          end;

And since the discussion is already venturing into the wisdom of the requirement to use a trigger, let me add a few thoughts.

First, the use of a trigger suggests someone thinks the notification needs to be real-time.  But real-time to what?  Some clerk in HR entering an employee update, based on receiving an email (already lost time waiting for the email) ? 

And who is the trigger going to notify (once we work out HOW it will notify)?  The clerk entering the action, who has no authority to fire anyone?  In the best of circumstances, how long will the entire process take .. from the time the employee actually creates their fourth infraction (say, coming in 5 minutes late) until they are counseled, out processed, and walked out the door?  Remember, what you are getting is not real-time notification of the 4th infraction, but real-time notification that the employee record has been updated to reflect that infraction by whoever maintains the HR records.  So what makes this notification so critical that it be done with a trigger?  This is really very similar to the example I wrote about in "Solving the Non-Problem"

Second, assuming we figure out a way to send this "real-time" notification to someone who actually has the authority to act on in (fire the employee), how do you guarantee that individual will get the notification in anything resembling real-time?  Think about email.  Even discounting the latency already in the email system, what if the recipient is on vacation and will not see it for another week?  So why was the real-time notification from a trigger so important?  Even if you get the trigger to send an sms text message (more likely to arrive and be read "real-time") you still have the same issue of the recipient possibly not being at a place or time where he/she can deal with it ... or even wants to deal with it.

No, the better business solution is to simply have a daily batch job produce a report.  There it would make sense for that batch job to send an email to interested parties, who can deal with it in a reasonable time frame.

And even that says nothing about the wisdom of a business policy to (seemingly) automatically terminate an employee after any four 'infractions' across their entire term of employment.  Suppose Joe is late once every 5 years.  By this rule, after 20 years of otherwise good employment, we're wanting to get him out the door as quickly as possible.

I realize none of the above applies, as this is a very specific school assignment, but is representative of the kinds of things you need to think about in a real business setting.  And don't expect your manager to think about them.  Hardly a week goes by that we don't see someone who was given a very real requirement by a very real manager, and that requirement makes about as much sense as this school assignment.  And usually the person posting it is just rolling over instead of pushing back.