Forum Stats

  • 3,840,358 Users
  • 2,262,592 Discussions
  • 7,901,239 Comments

Discussions

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

2»

Answers

  • Mgrmanu10
    Mgrmanu10 Member Posts: 5
    edited May 31, 2018 3:18PM

    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

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited May 31, 2018 3:20PM

    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.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited May 31, 2018 3:27PM
    @I have made this code CREATE OR REPLACE TRIGGER FALTAS_EXPULSION AFTER INSERT ON FALTA FOR EACH ROWBEGINSELECT 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.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited May 31, 2018 3:29PM
    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.

  • mathguy
    mathguy Member Posts: 10,606 Blue Diamond
    edited May 31, 2018 3:40PM

    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 varchar2as     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;/
    Hans Steijntjes
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited May 31, 2018 3:37PM

    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.

  • Mgrmanu10
    Mgrmanu10 Member Posts: 5
    edited May 31, 2018 3:45PM

    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

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited May 31, 2018 3:53PM

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

  • mathguy
    mathguy Member Posts: 10,606 Blue Diamond
    edited May 31, 2018 4:17PM

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

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jun 1, 2018 7:56AM
    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'.

    Frank Kulash
This discussion has been closed.