CODEWITHSUNDEEP
oracle-databaseplsql
user2241161
user2241161

Reputation: 25

Trying to create a trigger that prevents updates based on value from another table

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

Upvotes: 0

Views: 181

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

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

CREATE OR REPLACE TRIGGER trigger_name
  AFTER UPDATE ON a
  FOR EACH ROW
DECLARE
  l_dt_b b.dt_col%type;
BEGIN
  SELECT dt_col
    INTO l_dt_b
    FROM b
   WHERE b.b_key = :new.b_key;

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

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.

Upvotes: 0

Related Questions