Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop table resource_for_module_topic cascade constraints;
- create table "RESOURCE_FOR_MODULE_TOPIC"(
- "MODCODE" VARCHAR2(8),
- "MODTOPIC" VARCHAR2(50),
- "RESCODE" VARCHAR2(8),
- "PRIORITY" NUMBER(1,0),
- "RFT_COMMENT" VARCHAR2(400),
- "STUDENT" VARCHAR2(3),
- "SEQ" NUMBER(3,0)
- );
- ALTER TABLE "RESOURCE_FOR_MODULE_TOPIC" ADD PRIMARY KEY("MODCODE", "MODTOPIC", "RESCODE");
- drop trigger no_more_than_5
- //creating trigger
- create or replace trigger no_more_than_5
- before
- insert or update of priority, modtopic
- on resource_for_module_topic
- for each row
- when(new.priority=1)
- declare
- tot number;
- begin
- select count(*) into tot
- from resource_for_module_topic
- where
- modcode=:new.modcode
- and modtopic=:new.modtopic
- and priority=1;
- if(tot=5)
- then
- raise_application_error(-20601,'more than 5 module topics for topic' || :new.modtopic || 'in module:'|| :new.modcode);
- end if;
- end;
- //
- insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB1', 'MOD1', 'topic1', 1);
- insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB2', 'MOD1', 'topic1', 1);
- insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB3', 'MOD1', 'topic1', 1);
- insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB4', 'MOD1', 'topic1', 1);
- insert into resource_for_module_topic(rescode, modcode, modtopic, priority) values ('DB5', 'MOD1', 'topic1', 1);
- 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