CODEWITHSUNDEEP
sqloracle-databasetriggersconstraints
CoolDude21
CoolDude21

Reputation: 53

How do I make a SQL constraint in one table affect a different table

I have Three tables

In Recording, I have a column called DateRecored. In bill, I have a column called duedate and in House, I have a column called Operational.

I need to make a constraint so that when the bill is 60 days overdue, the Operational column will change to deactivated.

Let me know if you need for info, but I feel this is all you ll need.

Upvotes: 1

Views: 131

Answers (2)

Aleksander Blomskøld
Aleksander Blomskøld

Reputation: 18552

As previously answered, what you are looking for is triggers, not constraint. Using DBMS_SCHEDULER in Oracle, the trigger will look something like this:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'update_operational_after',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN UPDATE house set operational=false where id in (select house.id from House join Bill on (bill.house_id=house.id) where duedate > sydate+60; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byminute=0;byhour=0;byminute=0;bysecond=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Updates all operational to false if they've passed the due date');
END;
/

This schedules the given update to be executed each day at midnight.

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

Constraints take effect only when you are inserting/updating/deleting records. In your case, the age of a given record in Bill table should trigger a change in a different table and you need a periodic job to perform this operation. DBMS doesn't facilitate this kind of auto-updates (either through triggers or constraints) without any user-triggered DML operation.

Upvotes: 1

Related Questions