2

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

2

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger.

You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

In the body of your trigger you first need to get the additional information like fname, lname, etc. With a query like this one:

SELECT si.fname into l_fname, si.lname into l_lname, c.phone into l_phone, c.email into l_email
  FROM si_table si, contact_table c, bookinglog_table b
 WHERE si.student_id=c.student_id AND c.event_id=b.event_id, AND si.test=b.test
   AND <add a clause to uniquely identify a single record>;

Remember to declare the cariables l_fname, l_lname, etc. in the DECLARE block of the trigger:

DECLARE
  l_fname varchar2;
  l_lname varchar2;
  l_phone varchar2;
  l_email varchar2;
BEGIN
...

Even though this solution might work, it is actually not correct. You better create the table grade_table as follows. According to the database normalization form 4 you should not store detailed data multiple times.

CREATE TABLE grade_table (
  student_id number,
  event_id number,
  test number,
  grade NUMBER
);

When you query the table grade_test and need the details on the student, etc. you simply join the detail tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.