Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

oracletrigger

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

Best Answer

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.

Related Solutions

MySQL Update Trigger that updates multiple columns from values in another table

If you want to get right down to it, that's not the best approach from a theoretical perspective, because you're copying data around in your database that you really should be deriving.

CREATE ALGORITHM=MERGE VIEW products_with_packaging_info AS
SELECT p.*, 
       pt.width as packaging_width, 
       pt.height as packaging_height,
       pt.weight as packaging_weight,
       pt.case_count AS packaging_case_count
  FROM PRODUCTS p
  JOIN PACK_TYPES pt ON pt.id = p.packaging_type;

Done. SELECT queries against this view work exactly the same as queries against either table individually, as long as every product has a pack type. Queries against this view can still take advantage of the indexes on the base tables, and there's no overhead involved with copying the attributes from one table to another, which always has the potential for update anomalies.

You might even be surprised to find that the columns in the view can actually be updated as if it were a table, with updates propagating down into the base tables.

I offer this suggestion because a well-designed database should be such that it is impossible to get two different answers to the same question. For example, if a PACK_TYPES row is changed because an error is found, how do its new values propagate backwards into products?

But if you really want to take the trigger approach, that looks something like this:

DELIMITER $$

DROP TRIGGER IF EXISTS PRODUCTS_bu $$
CREATE TRIGGER PRODUCTS_bu BEFORE UPDATE ON PRODUCTS FOR EACH ROW
BEGIN

  IF NOT (NEW.packaging_type <=> OLD.packaging_type) THEN
    BEGIN
      DECLARE my_width INT DEFAULT NULL;      -- using
      DECLARE my_height INT DEFAULT NULL;     -- the
      DECLARE my_weight INT DEFAULT NULL;     -- appropriate
      DECLARE my_case_count INT DEFAULT NULL; -- data types here
      SELECT width, height, weight, case_count
        FROM PACK_TYPES
       WHERE id = NEW.packaging_type
        INTO my_width, my_height, my_weight, my_case_count;
      SET NEW.width = my_width, NEW.height = my_height, NEW.weight = my_weight, NEW.case_count = my_case_count;
    END;
  END IF;

END $$
DELIMITER ;

The <=> "spaceship" is the "null-safe equality operator" which constrains "NOT [possibly null] = [possibly null]" to always be either TRUE or FALSE; this is needed because [possibly null] != [possibly null] will never be true if either expression is NULL. This is the case because, logically, "NOT (FALSE)" is "TRUE" while "NOT (NULL)" is "NULL."

I could have declared the variables at the beginning and avoided the inner BEGIN/END but it seems optimal to avoid that work until we know we actually need to execute the inner logic in the first place, which is avoided whenever 'packaging_type' hasn't actually changed on a row for a given update query. Within a block, declarations have to precede other statements, so delaying the declarations requires the addition of the inner BEGIN/END.

You would also want a similar trigger for BEFORE INSERT which would be identical except you'd remove the 4 lines starting with IF ... BEGIN ... END ... END IF from the body of the procedure, use a new trigger name, and change BEFORE UPDATE to BEFORE INSERT.

It's BEFORE -- not AFTER -- in both cases, because the trigger fires BEFORE the newly-inserted or newly-updated row is written to the database.

PostgreSQL trigger function that selects from %current% table

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want:

BEGIN
    EXECUTE format('SELECT colname FROM %I', TG_RELID)
END;

BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME)
END;

(Of course these won't work as-is, since the SELECT has no destination for the data. You have to use EXECUTE format(..) INTO ... to store the result into a DECLAREd variable), e.g.

DECLARE
    _colvar integer;
BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar;
    RAISE NOTICE 'colname value was %',_colvar;
END;

Best Answer

Related Solutions

MySQL Update Trigger that updates multiple columns from values in another table

PostgreSQL trigger function that selects from %current% table

Related Question