Home » SQL & PL/SQL » SQL & PL/SQL » How to control the inserting the new records when it cross the limits (11g, Window 8)
How to control the inserting the new records when it cross the limits [message #629421] |
Tue, 09 December 2014 02:37 |
Bilal Khan
Messages: 128 Registered: April 2010 Location: Pakistan
|
Senior Member |
|
|
Dear All.
I have a table std
Create table std
(
std_id varchar2(4) primary key,
std_name varchar2(14),
address varchar2(20),
email varchar2(15)
)
and i insert fiew rows
insert into std values ('B001', 'zahid', 'pesh', 'abc');
insert into std values ('B002', 'jamil', 'pesh', 'jbc');
insert into std values ('B005', 'zahid', 'pesh', 'ksd');
.
i want to alter this table so that it may accept only 30 rows, it count the std_id values, if(count(std_id)>30) it display error message, that you cant enter the new records.
Is it possible?
Please help me in this records.
|
|
|
|
|
|
|
|
|
|
|
Re: How to control the inserting the new records when it cross the limits [message #629430 is a reply to message #629425] |
Tue, 09 December 2014 03:33 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Bilal Khan wrote on Tue, 09 December 2014 14:33thanks Lalit Kumar, But i want that it must count the values of records for std_id every time, if the count(std_id<=30) it allow to insert the new records otherwise it display error message..
Precisely. That is what the check constraint would also do. The id column on which the check constraint is to be implemented, will work like serial number. Once the value reaches 30, the check constraint won't allow any further inserts. You can catch this error, and have a fancy error message displayed to the user on your application.
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to control the inserting the new records when it cross the limits [message #631870 is a reply to message #629451] |
Wed, 21 January 2015 03:30 |
|
Michel Cadot
Messages: 68617 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I have just tried the third example of Rule Manager in the article John's mentioned (Requirement C) and it does not work in multi-session environment.
See the below execution with 2 sessions, time is set on to follow the sessions, actually the second session just come into play after "dbms_lock" call by the first session, so I stop its listing there post session 2 listing and afterwards go on with session 1 listing.
I just copied and pasted the example of the article but adding some indentation, did not include the rules for the 2 first requirements and changed the inserted empno and ename so the rows are inserted in the order of empno to ease the understanding of what I want to show.
In the end, session 1 first inserts empno 9990 to 9994 then session 2 inserts empno 9995 to 9999; ename are suffixed by 1 for session 1 and 2 for session 2.
I remind the requirement:
Quote:KING changes the business logic as follows (Requirement C):
1. Hire the third ANALYST to Department ACCOUNTING(10) under CLARK (7782), else go to Department RESEARCH(20) under JONES (7566).
2. Hire the fifth SALESMAN to Department RESEARCH(20) under JONES (7566), else go to Department SALES(30) under BLAKE (7698).
3. Rest go to Department ACCOUNTING(10) placed under CLARK (7782) waiting for manual sorting.
SQL> create user test identified by test default tablespace ts_d01 quota unlimited on ts_d01;
User created.
SQL> grant create session, dba to test;
Grant succeeded.
SQL> grant execute on sys.dbms_lock to test;
Grant succeeded.
SQL> -- session 1
SQL> connect test/test
Connected.
TEST> set time on
09:54:35 TEST> create table emp as select * from scott.emp;
Table created.
09:54:35 TEST> /******* Step (1) Create the Event Object ********/
09:55:37 TEST> CREATE OR REPLACE TYPE AddEMP AS OBJECT (
09:55:37 2 empno NUMBER(4),
09:55:37 3 ename VARCHAR2(10),
09:55:37 4 job VARCHAR2(9))
09:55:37 5 /
Type created.
09:55:38 TEST> /******** Step (2) Create the Rule Class *******/
09:55:38 TEST> BEGIN
09:55:38 2 dbms_rlmgr.create_rule_class (
09:55:38 3 rule_class =>'DeptAllocation',
09:55:38 4 event_struct =>'AddEMP',
09:55:38 5 action_cbk =>'DeptAlloc',
09:55:38 6 rlcls_prop => '<simple consumption="exclusive" ordering="rlm$rule.rlm$ruleid"/>',
09:55:38 7 actprf_spec => 'DEPTNO NUMBER(2), MGR NUMBER(4)');
09:55:38 8 END;
09:55:38 9 /
PL/SQL procedure successfully completed.
09:56:20 TEST> /**** Step (3) Modify the Callback Procedure ****/
09:56:20 TEST> CREATE OR REPLACE PROCEDURE DEPTALLOC (
09:56:20 2 rlm$event AddEmp,
09:56:20 3 rlm$rule DeptAllocation%ROWTYPE) is
09:56:20 4 BEGIN
09:56:20 5 INSERT INTO emp
09:56:20 6 (empno, ename, job, deptno, mgr, hiredate)
09:56:20 7 VALUES (
09:56:20 8 rlm$event.empno, rlm$event.ename,
09:56:20 9 rlm$event.job, rlm$rule.deptno,
09:56:20 10 rlm$rule.mgr, sysdate);
09:56:20 11 END;
09:56:20 12 /
Procedure created.
09:56:20 TEST> /**** Step (4) main procedure HIRE_EMP ****/
09:56:20 TEST> CREATE OR REPLACE PROCEDURE hire_emp
09:56:20 2 (ip_empno EMP.EMPNO%TYPE,
09:56:20 3 ip_ename EMP.ENAME%TYPE,
09:56:20 4 ip_job EMP.JOB%TYPE) AS
09:56:20 5 BEGIN
09:56:20 6 dbms_rlmgr.process_rules (
09:56:20 7 rule_class => 'DeptAllocation',
09:56:20 8 event_inst => AddEmp.getvarchar (ip_empno, ip_ename, ip_job));
09:56:20 9 END;
09:56:20 10 /
Procedure created.
09:56:21 TEST> /**** Function ****/
09:56:23 TEST> CREATE OR REPLACE FUNCTION cnt_emp (ip_job VARCHAR2)
09:56:23 2 RETURN NUMBER
09:56:23 3 AS
09:56:23 4 lv_cnt NUMBER;
09:56:23 5 BEGIN
09:56:23 6 SELECT COUNT(*) INTO lv_cnt
09:56:23 7 FROM emp
09:56:23 8 WHERE job=ip_job;
09:56:23 9 RETURN lv_cnt;
09:56:23 10 END;
09:56:23 11 /
Function created.
09:56:23 TEST> /**** Adding the function to Event Struct ****/
09:56:23 TEST> EXEC DBMS_RLMGR.ADD_FUNCTIONS('AddEmp','cnt_emp');
PL/SQL procedure successfully completed.
09:56:24 TEST> /**** Creating Rules ****/
09:56:31 TEST>
09:56:31 TEST> /*--5th Salesman to Research Department(20)--*/
09:56:31 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:31 2 VALUES ('Rule1', 20, 7566, 'job=''SALESMAN'' AND REMAINDER(cnt_emp(''SALESMAN'')+1,5)=0');
1 row created.
09:56:33 TEST> /*--3rd Analyst to Account Department(10)--*/
09:56:33 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33 2 VALUES ('Rule2', 10, 7782, 'job=''ANALYST'' AND REMAINDER(cnt_emp(''ANALYST'')+1,3)=0');
1 row created.
09:56:33 TEST> /*--Other Salesman to Operations Department(30)--*/
09:56:33 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33 2 VALUES ('Rule3', 30, 7698, 'job=''SALESMAN'' AND REMAINDER(cnt_emp(''SALESMAN'')+1,5)<>0');
1 row created.
09:56:33 TEST> /*--Other Analyst to Research Department(20)--*/
09:56:33 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33 2 VALUES ('Rule4', 20, 7566, 'job=''ANALYST'' AND REMAINDER(cnt_emp(''ANALYST'')+1,3)<>0');
1 row created.
09:56:33 TEST> /*--All others to Account Department(10)--*/
09:56:33 TEST> INSERT INTO DEPTALLOCATION (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33 2 VALUES ('Rule5', 10, 7782,'1=1');
1 row created.
09:56:33 TEST> COMMIT;
Commit complete.
09:56:37 TEST> /**** Sample Execution ****/
09:56:53 TEST>
09:56:53 TEST> EXEC hire_emp(9990, 'MANOJ1', 'SALESMAN');
PL/SQL procedure successfully completed.
09:56:54 TEST> EXEC hire_emp(9991, 'TAPAS1', 'ANALYST');
PL/SQL procedure successfully completed.
09:56:54 TEST> EXEC hire_emp(9992, 'ARUP1', 'SALESMAN');
PL/SQL procedure successfully completed.
09:56:54 TEST> EXEC hire_emp(9993, 'SUBRATA1','ANALYST')
PL/SQL procedure successfully completed.
09:56:54 TEST> EXEC hire_emp(9994, 'MADHU1','CLERK');
PL/SQL procedure successfully completed.
09:56:54 TEST> exec dbms_lock.sleep(60);
SQL> -- session 2
SQL> connect test/test
Connected.
TEST> set time on
09:57:03 TEST> EXEC hire_emp(9995, 'MANOJ2', 'SALESMAN');
PL/SQL procedure successfully completed.
09:57:04 TEST> EXEC hire_emp(9996, 'TAPAS2', 'ANALYST');
PL/SQL procedure successfully completed.
09:57:04 TEST> EXEC hire_emp(9997, 'ARUP2', 'SALESMAN');
PL/SQL procedure successfully completed.
09:57:04 TEST> EXEC hire_emp(9998, 'SUBRATA2','ANALYST')
PL/SQL procedure successfully completed.
09:57:04 TEST> EXEC hire_emp(9999, 'MADHU2','CLERK');
PL/SQL procedure successfully completed.
09:57:04 TEST> COMMIT;
Commit complete.
09:57:04 TEST>
09:57:54 TEST> -- session 1
09:57:54 TEST> COMMIT;
Commit complete.
09:57:54 TEST> SELECT empno, ename, deptno, mgr, job
09:57:54 2 FROM emp
09:57:54 3 order by empno;
EMPNO ENAME DEPTNO MGR JOB
---------- ---------- ---------- ---------- ---------
7369 SMITH 20 7902 CLERK
7499 ALLEN 30 7698 SALESMAN
7521 WARD 30 7698 SALESMAN
7566 JONES 20 7839 MANAGER
7654 MARTIN 30 7698 SALESMAN
7698 BLAKE 30 7839 MANAGER
7782 CLARK 10 7839 MANAGER
7788 SCOTT 20 7566 ANALYST
7839 KING 10 PRESIDENT
7844 TURNER 30 7698 SALESMAN
7876 ADAMS 20 7788 CLERK
7900 JAMES 30 7698 CLERK
7902 FORD 20 7566 ANALYST
7934 MILLER 10 7782 CLERK
9990 MANOJ1 20 7566 SALESMAN
9991 TAPAS1 10 7782 ANALYST
9992 ARUP1 30 7698 SALESMAN
9993 SUBRATA1 20 7566 ANALYST
9994 MADHU1 10 7782 CLERK
9995 MANOJ2 20 7566 SALESMAN
9996 TAPAS2 10 7782 ANALYST
9997 ARUP2 30 7698 SALESMAN
9998 SUBRATA2 20 7566 ANALYST
9999 MADHU2 10 7782 CLERK
24 rows selected.
If we look at SALEMAN only, we see:
09:57:54 TEST> SELECT empno, ename, deptno, mgr, job
09:59:32 2 FROM emp
09:59:32 3 where job='SALESMAN'
09:59:32 4 order by empno;
EMPNO ENAME DEPTNO MGR JOB
---------- ---------- ---------- ---------- ---------
7499 ALLEN 30 7698 SALESMAN
7521 WARD 30 7698 SALESMAN
7654 MARTIN 30 7698 SALESMAN
7844 TURNER 30 7698 SALESMAN
9990 MANOJ1 20 7566 SALESMAN
9992 ARUP1 30 7698 SALESMAN
9995 MANOJ2 20 7566 SALESMAN
9997 ARUP2 30 7698 SALESMAN
8 rows selected.
So the fifth row is indeed in department 20 but also the 7th one which should be in dept 30.
Same thing with ANALYST:
09:59:32 TEST> SELECT empno, ename, deptno, mgr, job
09:59:32 2 FROM emp
09:59:32 3 where job='ANALYST'
09:59:32 4 order by empno;
EMPNO ENAME DEPTNO MGR JOB
---------- ---------- ---------- ---------- ---------
7788 SCOTT 20 7566 ANALYST
7902 FORD 20 7566 ANALYST
9991 TAPAS1 10 7782 ANALYST
9993 SUBRATA1 20 7566 ANALYST
9996 TAPAS2 10 7782 ANALYST
9998 SUBRATA2 20 7566 ANALYST
The third one is in dept 10 as required but the fifth one too which should in dept 20 and the 6th one is in dept 20 when it should be in dept 10.
The reason is clear: the count(*) in the function executed by session 2 can't see the rows inserted by session 1 and so session 2 acts in exactly the same way than session 1.
I do not mean Rule Manager does not work for this case or others, actually I don't know, I mean its usage is not so simple the article wants to show it.
[Updated on: Wed, 21 January 2015 10:29] Report message to a moderator
|
|
|
Re: How to control the inserting the new records when it cross the limits [message #631909 is a reply to message #631870] |
Wed, 21 January 2015 10:12 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
why not an after trigger
custom@test>select count(*) from whb;
COUNT(*)
----------
30
custom@test>insert into whb values(1);
insert into whb values(1)
*
ERROR at line 1:
ORA-20000: too many rows
ORA-06512: at "CUSTOM.WHB_TEST", line 9
ORA-04088: error during execution of trigger 'CUSTOM.WHB_TEST'
CREATE OR REPLACE TRIGGER CUSTOM.WHB_TEST
AFTER INSERT
ON CUSTOM.WHB
REFERENCING NEW AS New OLD AS Old
DECLARE
tmpVar NUMBER;
BEGIN
select count(*)
into tmpvar
from whb;
if tmpvar > 30 then
raise_application_error(-20000, 'too many rows');
end if;
END whb_test;
/
|
|
|
|
Goto Forum:
Current Time: Tue Mar 19 02:15:09 CDT 2024
|