I am new to triggers and SQL in general. Sorry in advance if this forum is only for experts and this is a newbie question...
I need to create a trigger that fires when someone tries to submit a grade(evaluation) of "5" in bookinglog_table for grade column. This will then grab the values of 5 columns that gave that grade of 5, and put them in a new table (so a fourth table) that I already created.
But the 5 columns are from 3 different tables (for example, fname=si_table, lname=si_table, phone=contact_table, email=contact_table, and grade=bookinglog_table.)
So tables are si_table si, contact_table c, and bookinglog_table b
What they have in common is si.student_id=c.student_id, c.event_id=b.event_id, and si.test=b.test
I have a trigger that works only if I bring in columns from table 3 which is where the INSERT or UPDATE grade originally comes from.
So here is the new table, we'll call it grade_table in which I tested the trigger that WORKS.
CREATE TABLE grade_table ( grade NUMBER (1), test1 NUMBER(5), test2 NUMBER(8));
And the trigger that DOES work with the above:
CREATE OR REPLACE TRIGGER b_trigger1 AFTER INSERT OR UPDATE ON bookinglog_table FOR EACH ROW WHEN (NEW.eval = 5) DECLARE BEGIN INSERT INTO grade_table VALUES (:NEW.grade, :NEW.test1, :NEW.test2); DBMS_OUTPUT.PUT_LINE('Recorded student information into grade_table'); END; /
But I don't want test1 and test2 values from the bookinglog_table. I want fname, lname, phone, email from the other two tables, as well as the grade from the bookinglog_table.
Any tips, hints or suggestions? I've tried creating a SELECT statement within the trigger, but I'm not sure when I need aliases, if I need to put :NEW somewhere, or if I need an IF statement instead, etc..