1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Database trigger not working

Discussion in 'SQL PL/SQL' started by waheeb, Aug 27, 2016.

  1. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    hi,i have 4 tables which are:
    1. students table.columns(student_id,school_id,stage_id,class_id,name,birthdate,status)
    and student_id is primary key.
    2. study_plan table.columns(year_id,term_id,school_id,stage_id,class_id,course_id,status)
    (year_id,term_id,school_id,stage_id,class_id,course_id) as composite key( primary key)
    3. courses_exams table.columns(school_id,stage_id,class_id,course_id,exam_id,min_degree,max_degree,no_of_exams)
    (school_id,stage_id,class_id,course_id,exam_id) as composite key( primary key)
    4. marks table.cloumns(student_id,year_id,term_id,course_id,exam_id,exam_seq,mark
    ,school_id,stage_id,class_id,status)
    (student_id,year_id,term_id,course_id,exam_id,exam_seq)as composite key( primary key)


    so,whenever a new student is registered,the below database trigger should fire and works like this:
    It should go to the study_plan table and specify all courses and according to them it goes to the courses_exams table to specify all exams for each course and it MUST insert the value of column no_of_exams into exam_seq column in the marks table and it must start from value 1 to no_of_exams value.for example, if the no_of_exams=10 for the first exam then exam_seq must be inserted starting from 1 to 10.

    the trigger is created but it is NOT WORKING as it should:

    create or replace trigger students_trig after insert on students for each row
    begin
    insert into marks(student_id,year_id,term_id,school_id,stage_id,class_id,course_id,exam_id,exam_seq,status)
    select :new.student_id,sp.year_id,sp.term_id,:new.school_id,:new.stage_id,:new.class_id,sp.course_id,
    ce.exam_id,level,1
    from study_plan sp,courses_exams ce connect by level <= ce.no_of_exams and
    sp.school_id=:new.school_id and sp.stage_id=:new.stage_id and sp.class_id=:new.class_id and sp.status=1 and ce.school_id=sp.school_id and ce.stage_id=sp.stage_id and ce.class_id=sp.class_id and ce.course_id=sp.course_id;
    end;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    This task needs to be solved in the procedure or a packet . it is logic of filling of data.
    For example: You in the procedure can realize batch processing, and a line trigerr of it it is impossible to make.

    Two questions for specification:

    1) you studies the trigerrs? you want to solve the task using the trigger for row?
    2) can you provide here scripts of creation and filling of tables?
     
    Last edited: Aug 29, 2016
  3. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    hi there,for the first question, yes i want to solve the task using the trigger for row.
    the second question,I need you to explain what you meant by 'scripts of creation and filling of tables'.


    Moreover,I just hope that you understood where exactly my problem,I need the trigger to loop starting from value 1 until the value of no_of_exams column.
    I really appreciate your help.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    To receive the help quicker, it is necessary to provide sql here
    scripts:
    1) create table... for your tables
    2) insert... fro your tables
     
  5. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    Well,you should know that i have like 15 tables and writing the creation of tables,primary,and foreign keys code will take time,but take a look at this trigger which i wrote to fire whenever a new exam_id is created on the courses_exams table mentioned in my first post.you will see here a loop is easy to make for the new exam.All I'm asking you is to look at it,then if you decide that writing the create table script is necessary, then I will.

    create or replace trigger courses_exams_trig after insert on courses_exams for each row
    begin
    for i in 1..:new.no_of_exams
    loop
    insert into marks(student_id,year_id,term_id,school_id,stage_id,class_id,course_id,exam_id,exam_seq,status)
    select st.student_id,sp.year_id,sp.term_id,:new.school_id,:new.stage_id,:new.class_id,:new.course_id,:new.exam_id,i,1
    from students st,study_plan sp where
    st.school_id=:new.school_id and st.stage_id=:new.stage_id and st.class_id=:new.class_id and st.status=1 and
    sp.school_id=st.school_id and sp.stage_id=st.stage_id and sp.class_id=st.class_id and sp.course_id=:new.course_id and sp.status=1;
    end loop;
    end;

    One last thing,I'm using developer 6i to design the forms which I use to insert to tables.
    Thanks.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    if you need just to copy lines, then it is possible to make it a request:
    Code (SQL):
    CREATE OR REPLACE TRIGGER courses_exams_trig after INSERT ON courses_exams FOR each ROW
    BEGIN
    INSERT INTO marks(student_id,year_id,term_id,school_id,stage_id,class_id,course_id,exam_id,exam_seq,STATUS)
    WITH
        step1 AS
        (
            SELECT
                st.student_id,
                sp.year_id,
                sp.term_id,
                st.school_id,
                st.stage_id,
                st.class_id,
                st.STATUS,
                sp.course_id,
                :NEW.exam_id exam_id,
                st.STATUS
            FROM
                students st
                JOIN study_plan sp
                ON
                    sp.school_id=st.school_id
                    AND sp.stage_id=st.stage_id
                    AND sp.class_id=st.class_id
                    AND sp.course_id=:NEW.course_id
                    AND sp.STATUS=1      
            WHERE
                st.school_id=:NEW.school_id
                AND st.stage_id=:NEW.stage_id
                AND st.class_id=:NEW.class_id
                AND st.STATUS=1  
        )
        SELECT
            st1.student_id,
            st1.year_id,
            st1.term_id,
            st1.school_id,
            st1.stage_id,
            st1.class_id,
            st1.STATUS,
            st1.course_id,
            st1.exam_id,
            level,
            st1.STATUS      
        FROM
            step1 st1
        CONNECT BY level <= :NEW.no_of_exams          
        AND sys_op_guid () IS NOT NULL; -- or dbms_random.value is not null  -- for break cycle data
    END;

    p.s.
    it is better not to realize this logic in the trigger.
    this logic of formation of data and this logic it is necessary to implement in units: procedure or package.
     
  7. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    copy sometimes is good though ;).
    Anyways,I have already shown you the courses_exams trigger above and it was not what I'm looking for.What I really wanted is the students trigger .I wrote something and it worked actually, but I want your opinion if it's correct or not and why.Thanks a million.

    create or replace trigger students_trig after insert on students for each row
    declare
    cursor ex_cur is select sp.year_id,sp.term_id,sp.course_id,ce.exam_id,ce.no_of_exams from study_plan sp,courses_exams ce where
    sp.school_id=:new.school_id and sp.stage_id=:new.stage_id and sp.class_id=:new.class_id and sp.status=1 and
    ce.school_id=sp.school_id and ce.stage_id=sp.stage_id and ce.class_id=sp.class_id and ce.course_id=sp.course_id
    order by 1,2,3,4,5;
    i number:=1;
    begin
    for ex_rec in ex_cur
    loop
    loop
    if i <= ex_rec.no_of_exams then
    insert into marks(student_id,year_id,term_id,course_id,exam_id,exam_seq,school_id,stage_id,class_id,status)
    values new.student_id,ex_rec.year_id,ex_rec.term_id,ex_rec.course_id,ex_rec.exam_id,i
    ,:new.school_id,:new.stage_id,:new.class_id,1);
    i:=i+1;
    else
    i:=1;
    exit;
    end if;
    end loop;
    end loop;
    end;
     
    Last edited: Aug 31, 2016
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    This option of implementation isn't absolutely clear to me...
    For what to use the cursor and in a cycle to copy lines?
    I already said that this problem needs to be solved in a different way ))
     
    Last edited: Aug 31, 2016
  9. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    Hi again,I added a little thing to your trigger and it worked just fine.

    CREATE OR REPLACE TRIGGER courses_exams_trig after INSERT ON courses_exams FOR each ROW
    BEGIN
    INSERT INTO marks(student_id,year_id,term_id,school_id,stage_id,class_id,course_id,exam_id,exam_seq,STATUS)
    WITH
    step1 AS
    (
    SELECT
    st.student_id,
    sp.year_id,
    sp.term_id,
    st.school_id,
    st.stage_id,
    st.class_id,
    sp.course_id,
    :NEW.exam_id exam_id,
    st.STATUS
    FROM
    students st
    JOIN study_plan sp
    ON
    sp.school_id=st.school_id
    AND sp.stage_id=st.stage_id
    AND sp.class_id=st.class_id
    AND sp.course_id=:NEW.course_id
    AND sp.STATUS=1
    WHERE
    st.school_id=:NEW.school_id
    AND st.stage_id=:NEW.stage_id
    AND st.class_id=:NEW.class_id
    AND st.STATUS=1
    )
    SELECT distinct
    st1.student_id,
    st1.year_id,
    st1.term_id,
    st1.school_id,
    st1.stage_id,
    st1.class_id,
    st1.course_id,
    st1.exam_id,
    level,
    st1.status
    FROM
    step1 st1
    CONNECT BY level <= :NEW.NO_OF_EXAMS
    AND sys_op_guid () IS NOT NULL;
    end;



    Mr.krasnoslobodtsev_si thank you for your help.