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

Mgrmanu10

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...

Hans Steijntjes

You could better check:

old.fault_value < 4 AND new.fault_value >= 4

Also note that your triggger would fire even when the user decides to rollback the transaction.

Frank Kulash

Hi,

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as UPDATE, then the CREATE TABLE and INSERT statements you post should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.  In this case, you might post a CREATE TABLE statement, and then some INSERT statements, and maybe some UPDATE statements, too.  After every INSERT or UPDATE statement, say what  the trigger should do (if anything: for example, another table should get UPDATEd).
Always say which version of Oracle you're using (for example, 12.2.0.1.0).
See the forum FAQ:

Do you really want a trigger for this task?

In an actual business, you would probably use a query for this job, not a trigger.  (Of course, your teacher may assign you to do something that nobody would ever do outside of school.)

L. Fernigrini

Just in case you do not feel comfortable writing in English (although I believe your message is pretty clear ) you can always ask on the Spanish forum.

It does not have so much activity, but it may be easier to identify a generic solution and then you may ask specific details in the general forums.

EdStevens

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.

Gary_A

And just to add what Frank and Ed said about a trigger being the wrong method here, you said you want to be notified. Notified how? That would depend on the client software you are using, not necessarily the database. Triggers run in the database, so there is no method to report back to the client.

Additionally, you say you want notification when there are 4 faults. Again, how? If there is an update that makes fault number 4, will every session connected to the database, no matter what they are doing, get this message? you need to define context in this case.

Ryan O. Lee

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;

EdStevens

c4fbcf18-5d10-43df-874c-b7df74536096 wrote: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 where does that dbms_ouput go?

It goes to a buffer, to be processed by the application.

A buffer that may very well be ignored by the application.  DBMS_OUTPUT is probably the WORST way to try to send a message to the user.

Also your statement that it is the trigger logic itself that increases the "fault count" is making an assumption about the larger architecture of the application.  We need to wait for the OP to respond to Frank's request for a script to set up a test case before jumping to any conclusions. Besides that, your own example does NOT increase the count "inside the trigger logic."

====

Oh, by the way, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens? Maybe I just like a certain TV show (http://www.imdb.com/title/tt0247091/) But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is no better than posting as "Anonymous".

refer to https://community.oracle.com/thread/3650233 -how to change nickname-

All you ed0f625b-6857-4956-9b66-da280b7cf3a2's look alike . . .

Hans Steijntjes

You could better check:

old.fault_value < 4 AND new.fault_value >= 4

Also note that your triggger would fire even when the user decides to rollback the transaction.

jaramill

For future reference and FYI (For Your Information), please read the link on -->

Reading that will help you tremendously in participating on theses forums.

EdStevens

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.

Mgrmanu10

Sorry for the title and my petition, I am working with other partners and we are rookies on sql developer.

I have made this code

CREATE OR REPLACE TRIGGER FALTAS_EXPULSION

    AFTER INSERT ON FALTA

    FOR EACH ROW

BEGIN

SELECT numFaltasEmpleado INTO NSS FROM FALTA;

IF : ((NSS.SIZE) == 4)

THEN raise_application_error (-20599, 'The employee has commited 4 faults and must be fired'):

END IF;

END;

*numFaltasEmpleado is a function that counts the faults that an employee has made

* NSS (Security Social NUMBER) is a varchar2