SQL & PL/SQL

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

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.

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.

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.

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;

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

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

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

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

I have made, surely, syntax errors in the IF sentence@@@

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

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

I have made, surely, syntax errors in the IF sentence@@@

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

I agree with you, I would not use a trigger, but if that is a condition then probably a way to generate a notification is inserting a row in a new table, called PeopleToFire or something similar .

Then one responsible of firing people should just read that new table (without knowing why it has to be fired).

That's not the way I would do things in my business, but there may be some scenarios where the people that actually fires the employee does not have access to the data that causes the firing (there may be many causes, all of them generated in different ways, but all of them just generate a row in this new table), so creating a trigger could be a way to generate that firing "request".

Just imagining a business case where a trigger "may" be useful, rather than just querying the source data in a report.

Mgrmanu10 wrote:
Sorry for the title and my petition, I am working with other partners and we are rookies on sql developer.
I have made, surely, syntax errors in the IF sentence@@@
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

you are selecting "into" a variable named NSS, but never declared it.

The syntax of a SELECT requires a FROM to name the table you are selecting ... FROM.

Your construct of the IF indicates you are trying to use syntax from some other system.  Here, no colon, no parenthesis, and the 'equality' test is a single equal-sign.

https://docs.oracle.com/database/121/LNPLS/toc.htm

It would be more constructive if, instead of posting JUST the SQL, you also posted the error message that results from executing it.  Also, re-read Franks suggestion/request for you to supply the CREATE TABLE and INSERT statements needed to allow others to set up  a test environment.

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

Nothing at all wrong with your title or question. No need to apologize.

If you are a manager (possible, judging by the MGR in your name), you could use a function like this. Not sure if it will help in class though.

create or replace function what_to_do
    ( need_to_write_a_trigger          boolean
    , dont_know_how_to_write_a_trigger boolean    default true  -- no doubt!
    ) return varchar2
as 
    nothing varchar2(1000);
begin
    if need_to_write_a_trigger and dont_know_how_to_write_a_trigger
    then
        execute immediate 'Hire someone who does!';
    end if;
    return nothing;
end;
/

Some thoughts:

1) Usually, you cannot query the same table tha tis being modified by the trigger, since that produces an error. If the "numFaltasEmpleado" function reads from FALTA then the trigger will probably fail.

2) You are not filtering by employee ID or SSN, you seem to be counting all "faltas" from all employees, since there is not WHERE clause in the select.

3) ((NSS.SIZE) == 4) is not oracle syntax.

I do not know if there is an easy way to do this with an INSERT trigger on FALTA, another simpler way of doing something similar would be if the number of faults are already recorded in the employee profile. You may use something like this:

CREATE OR REPLACE TRIGGER tg_Empleado_U

    AFTER UPDATE ON Empleado

    FOR EACH ROW

BEGIN

    IF :NEW.NumeroFaltas >= 4

    THEN

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

    END IF;

END;

You may have an INSERT trigger on the Falta table to automatically increase the number of faults on the employee:

CREATE OR REPLACE TRIGGER tg_Falta_I

    AFTER UPDATE ON Falta

    FOR EACH ROW

BEGIN

    UPDATE Empleado e

    SET NumeroFaltas = NumeroFaltas + 1

    WHERE e.SSN = :NEW.SSN;

END;

Of course, the simplest way would be to have a single stored procedure that does the inserting on Falta, updating Empleados, and generating the error when appropriate.

Then.. How can i get how many times have a NSS has commited 4 faults?

actually NSS is a number, not a varchar2.

And i have to say that FALTA (fault in english) is a TABLE. This Table has ID_Falta(PRIMARY KEY), description(varchar2),fecha (date),NSS(number)

I am apologized for the disturbing @Ed Stevens

No need to apologize, this forums is to ask questions. You should try to follow the guidelines on order to make things easier for everyone here, since all of us do this for free (mostly in our free time).

Counting on the same table you are inserting will probably raise a mutating table error (here is a post entry in Spanish that explains the problem):

https://blog.avanttic.com/2010/11/02/evitar-errores-de-tabla-mutante-en-oracle-database/

Take a look at the solutions that were proposed above and you can try to write your version of the trigger and test it, come back if it generates any error (post the error, that helps a lot!)

Did the teacher or the textbook, etc. mention anything about ROW trigger as opposed to STATEMENT trigger? Did you learn about the two types?

Statement triggers are often MUCH slower than row triggers (and far less common in practice), but they do exist, and at least they are not subject to the "mutating table" issue. In real life you would very likely not use such a solution (but then, you wouldn't use a trigger AT ALL). For class though, especially if statement triggers are (or are supposed to be) "known to the students" and no specific mention was made that the homework assignment must use ROW triggers, that may be an easy way to get the homework done.

(Then some teachers may object that "that wasn't what they had in mind" and still put the blame on the student - perhaps giving no credit for the hw - even though it is the teacher's fault; I have had that happen to me more than once and I was mad as hell. They never taught us in class how to read the teacher's mind.)

Even if the problem didn't ask for this... are you considering what may happen when a row is updated or deleted in the "faults" table? Or is it guaranteed that rows are only inserted, and then they are never modified (in particular, no one ever makes a mistake, or if a mistake is made, it is never corrected)?

Mgrmanu10 wrote:
Then.. How can i get how many times have a NSS has commited 4 faults?
actually NSS is a number, not a varchar2. 
And i have to say that FALTA (fault in english) is a TABLE. This Table has ID_Falta(PRIMARY KEY), description(varchar2),fecha (date),NSS(number)
I am apologized for the disturbing @Ed Stevens

Again, no need to apologize.  I appreciate your desire to be polite, and the fact that you feel your are struggling a bit with English, but you are doing fine.

The one thing we really need is the DDL for the tables involved, and the INSERT statements to create some sample data.  I know this has been mentioned multiple times, with at least two messages asking you to review 'how to ask a question'.