Advertisement
Guest User

Untitled

a guest
Dec 12th, 2018
83
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.58 KB | None | 0 0
  1. drop table resource_for_module_topic cascade constraints;
  2.  
  3. create table "RESOURCE_FOR_MODULE_TOPIC"(
  4. "MODCODE" VARCHAR2(8),
  5. "MODTOPIC" VARCHAR2(50),
  6. "RESCODE" VARCHAR2(8),
  7. "PRIORITY" NUMBER(1,0),
  8. "RFT_COMMENT" VARCHAR2(400),
  9. "STUDENT" VARCHAR2(3),
  10. "SEQ" NUMBER(3,0)
  11. );
  12.  
  13. ALTER TABLE "RESOURCE_FOR_MODULE_TOPIC" ADD PRIMARY KEY("MODCODE", "MODTOPIC", "RESCODE");
  14.  
  15. drop trigger no_more_than_5
  16.  
  17.  
  18. //creating trigger
  19.  
  20. create or replace trigger no_more_than_5
  21. before
  22. insert or update of priority, modtopic
  23. on resource_for_module_topic
  24. for each row
  25. when(new.priority=1)
  26. declare
  27. tot number;
  28. begin
  29. select count(*) into tot
  30. from resource_for_module_topic
  31. where
  32. modcode=:new.modcode
  33. and modtopic=:new.modtopic
  34. and priority=1;
  35.  
  36. if(tot=5)
  37. then
  38. raise_application_error(-20601,'more than 5 module topics for topic' || :new.modtopic || 'in module:'|| :new.modcode);
  39. end if;
  40. end;
  41.  
  42. //
  43.  
  44. insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB1', 'MOD1', 'topic1', 1);
  45. insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB2', 'MOD1', 'topic1', 1);
  46. insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB3', 'MOD1', 'topic1', 1);
  47. insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB4', 'MOD1', 'topic1', 1);
  48. insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB5', 'MOD1', 'topic1', 1);
  49.  
  50. insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB6', 'MOD1', 'topic1', 1);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement