0

fairly new to triggers so just trying to get my head round on how to create certain triggers.

i have to make a doctors practise with patients and the trigger has to count if a patient has made 4 or more appointments their details will be stored into a audit table

i go all the tables up and running i am just stuck on how to create this trigger, im wondering if it is possible to create a trigger that counts the amount of times a pat_no has been into the appointment table

This is what i have so far, its not much so any help would be must appreciated 

CREATE OR REPLACE TRIGGER TRG_AUDIT
AFTER INSERT ON APPOINTMENT
FOR EACH ROW
BEGIN

END

im guessing its a when clause to count the amount of the same pat_no is entered onto the appointment table

Adam Silenko
  • 3,025
  • 1
  • 14
  • 30
joshwaa
  • 21
  • Why do you want to use a trigger for this? Normally, that sort of business logic makes much more sense in the stored procedure (or other API) that creates the appointment. Having business rules like this scattered throughout a bunch of triggers generally makes it very difficult for someone to follow the logic of the application. – Justin Cave Apr 11 '16 at 20:51
  • as part of the assignment in university it states i need to use a trigger – joshwaa Apr 11 '16 at 20:54
  • Did the instructor tell you how he or she wants you to work around the mutating table exception? You cannot, in general, query the table on which a row-level trigger is defined. If you really want to do this correctly, you'd need a before statement trigger, a row-level trigger, an after statement trigger, a package, and a collection (a temporary table can be substituted for the package and the collection and, depending on the Oracle version, a compound trigger with three sections rather than three triggers). That's a lot of moving pieces though. – Justin Cave Apr 11 '16 at 20:58
  • the assignment states "create triggers that enforce the following business rules: if a patient make a greater than four appointments in one month (patient details must then be stored in an audit table) – joshwaa Apr 11 '16 at 21:01
  • In the coursework, has the instructor described the mutating table exception? There are hacks one can do to make the trigger simpler at the cost of correctness and by disallowing large amounts of valid DML statements. It is basically never acceptable to do this in real production code. But some instructors with poorly considered assignments might accept that sort of solution. – Justin Cave Apr 11 '16 at 21:05
  • Here is an example of the correct solution with multiple triggers. You'd do the actual check in the after statement trigger looping over the keys for the patients that had created appointments, doing the `count` and then inserting rows into the audit table. http://stackoverflow.com/questions/5805986/oracle-after-delete-trigger-how-to-avoid-mutating-table-ora-04091/5806193#5806193 – Justin Cave Apr 11 '16 at 21:08
  • The instructor hasn't said anything about mutating tables exception but will have a look, thank you – joshwaa Apr 11 '16 at 21:36
  • Look up compound triggers. From Oracle 11 onwards you can combine statement/row/before/after triggers in one trigger. A lot easier to maintain. – Rene Apr 12 '16 at 06:17

0 Answers0