I've two tables the following:
department(alpha, college, etc.)
course(id, alpha, college, title, etc.)
College and alpha can be found both in tables by design. I made the decision to p-normalize just a little since the college and alpha will always be preferred when viewing a training course.
I've one trigger that executes following the
department table is up-to-date to ensure that it updates all rows within the
course table using the new
college values. I in addition have a trigger that executes before upgrading the
course table to make certain the
college pair the user posted in their edits is available within the
department table when the pair is not there it boosts and application error.
These triggers conflict. The 2nd one inspections the new values for that
department table have been in their, however they aren't yet therefore it fails enjoy it should.
Can you really disregard the second trigger when the first trigger is performed first? I truly don't wish to execute the 2nd trigger within this situation, since i have be aware of values have been in the very first table. If that is difficult, it is possible to better method of doing this without altering my schema?
Your next trigger seems like simply an overseas key. Drop it and make up a foreign key constraint on
course rather. That actually works during my tests.
However, it appears like unnecessary try to support a denormalization that delivers little benefit. If you want to write simple queries, produce a view that joins the 2 tables and employ that inside your queries. If you're worried about the join performance, I doubt greatly that it'll be considered a problem, unless of course you're missing apparent indexes around the tables.
I'd sincerely recommend getting rid of your trigger approach altogether becasue it is mired by dirty reads. Whenever I faced challenging similar to this I'd implement the DML using Saved Methods only. You receive all the benefits of triggers with no head aches if implemented correctly.
In case your fear is you need to make certain all updates towards the department table follow your logic just like alterations in course, remove update permissions to the user except who owns the saved procedure. This guarantees the only real caller who are able to modify that table may be the saved procedure you control and understand. By coincidence, it might be the only method to update the tables.
Like the majority of other cases implemented with triggers, you can observe the responsibility here since the data-model itself has defects.
You are able to implement exactly the same logic as below and keep all rules using PK and FK constraints.
---Department references College... Create table department( department_id number primary key, aplha varchar2(20) not null, college varchar2(20) not null ); ***--Course belongs to a department.. so should be a child of department. --If it's possible for different depts to give the same course (IT and CS), --you'll have --a dept_course_asc table*** Create table Course( course_id number primary key department_id number references department(department_id), course_name varchar2(100) not null );
for those who have students table, you'll connect it using the course table with another student_table association table.
It may look like these are much more tables than you intially demonstrated, but when you need to avoid data redundancies and don't wish to possess the burden of upgrading posts in most tables every time they alternation in parents table, the above mentioned model is the only method to go.