Take the 2-minute tour ×

I am trying to prevent users from updating one table based on a date value from another table.

Table A contains rows that I would like to make un editable if a date value in the Table B is older than sysdate.

I need to somehow tell the trigger to check the row and use a foreign key in Table A's row to query its corresponding rows in Table B and then do this:

raise_application_error(-20000, 'It is too late to change this record');

Thank You

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Assuming this is a homework assignment, you'd want something like this (I'm guessing at table structures and cardinalities since you don't specify).

  l_dt_b b.dt_col%type;
  SELECT dt_col
    INTO l_dt_b
    FROM b
   WHERE b.b_key = :new.b_key;

  IF( l_dt_b < sysdate )
    RAISE_APPLICATION_ERROR( -20001, 'Too late' );

If this is for a real system, however, trigger-based validation is problematic. It is not safe in a multi-user system, for example. In session 1, I may have modified the row in B but not yet committed the change. You can then query the row in session 2, see the old value, and allow the UPDATE. We can both commit our changes and nothing will detect that we have data in an invalid state.

share|improve this answer
Thank you for your reply! I receive these errors when I test the trigger ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "[dbo].TRG_CHECK_DATE", line 4 ORA-04088: error during execution of trigger '[dbo].TRG_CHECK_DATE' I also have to take the :new off of the :new.b_key as it tells me i am using a bad bind variable –  user2241161 Apr 14 '13 at 20:56
@user2241161 - As I said, I'm guessing at cardinalities. If B is the parent in the foreign key relationship, then the new row in A must point at exactly 1 row in B. The error you are getting indicates that the query is returning more than one row. That either means that you have not translated it to your data model correctly or that your data model doesn't actually have a proper foreign key relationship and there are multiple parent rows in B that relate to the new row in A. :new.b_key refers to the b_key column in the new row you are inserting into the A table. –  Justin Cave Apr 14 '13 at 21:05
@user2241161 - If you are getting a "bad bind variable" error, that likely means that you specified the name of the column in A that references B incorrectly. My guess was that the column would be named the same in both A and B. It is possible that your data model is not that consistent. –  Justin Cave Apr 14 '13 at 21:12
In the end this was the code that worked CREATE OR REPLACE TRIGGER trg_check_date AFTER UPDATE ON order_line FOR EACH ROW DECLARE v_delivery_date placed_order.delivery_date%type; CURSOR c1 IS SELECT delivery_date INTO v_delivery_date FROM placed_order WHERE placed_order.order_id = placed_order.order_id; BEGIN OPEN c1; LOOP FETCH c1 INTO v_delivery_date; EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; IF( v_delivery_date > (to_char(sysdate, 'DD/MM/YYYY'))) THEN RAISE_APPLICATION_ERROR( -20001, 'You cannot edit an order that is closed' ); END IF; END; Thank You! –  user2241161 Apr 14 '13 at 21:23
I have just read your comments, again thank you for sharing your knowledge!! –  user2241161 Apr 14 '13 at 21:24

Your Answer


