Create DB Trigger [message #604578] |
Wed, 01 January 2014 10:41 |
Xandot
Messages: 235 Registered: January 2014 Location: India
|
Senior Member |
|
|
Hello friends,
Please help me out in solving the below real time database situation....
----------script for table employee------------
create table EMPLOYEE
(
EMPLOYEE_NAME VARCHAR2(20) not null,
MANAGER_ID NUMBER(15) not null,
SUBORDINATE_COUNT NUMBER(15),
EMPLOYEE_ID NUMBER(15) not null
)
alter table EMPLOYEE
add constraint emp_fk foreign key (MANAGER_ID)
references employee (EMPLOYEE_ID);
--------------------------------------------------------------------------------------
I want to create a database trigger for employee table with the help of below rules.
and create a procedure for the same rules....
Rules:------------------
1) When the new employee is inserted into the table and manager id assign to employee then the subordinate count of manager is increment by 1.
2) When the manager is assign to another manager id then the parent manager sub ordinate count is increment by 1 and child manager id is decremented by 1.
3) If the manager_id is allocated to the employee and is same as employee id then raise an error message for cyclic relationship is occurred.
4) If the particular employee is going to deleted then the all parent nodes will be decremented by 1.
my db version-------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Thanks
|
|
|
Re: Create DB Trigger [message #604579 is a reply to message #604578] |
Wed, 01 January 2014 11:17 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You already got an answer on oracle.com. You can't do that with a trigger. And, in fact, keeping aggregate data on each row is usually bad design.
SY.
|
|
|
|
|
|
|
Re: Create DB Trigger [message #604592 is a reply to message #604578] |
Wed, 01 January 2014 13:32 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
How about taking a completely different approach? Use a view:orclz> create view v2 as select e.empno,e.ename,(select count(*) from emp f where f.mgr=e.empno) sub_count from emp e;
View created.
orclz> select * from v2;
EMPNO ENAME SUB_COUNT
---------- ---------- ----------
7369 SMITH 0
7499 ALLEN 0
7521 WARD 0
7566 JONES 2
7654 MARTIN 0
7698 BLAKE 5
7782 CLARK 1
7788 SCOTT 1
7839 KING 3
7844 TURNER 0
7876 ADAMS 0
7900 JAMES 0
7902 FORD 1
7934 MILLER 0
14 rows selected.
orclz>
And, just to do the moderator bit:
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
|
|
|