Home » SQL & PL/SQL » SQL & PL/SQL » Create DB Trigger (Oracle 11g,Win 7)
Create DB Trigger [message #604578] Wed, 01 January 2014 10:41 Go to next message
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 Go to previous messageGo to next message
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 #604580 is a reply to message #604578] Wed, 01 January 2014 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Take the time to read and understand this topic.

Re: Create DB Trigger [message #604581 is a reply to message #604579] Wed, 01 January 2014 11:27 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks for reply..
but if i want to create procedure with same rules then its possible or not....

Re: Create DB Trigger [message #604582 is a reply to message #604581] Wed, 01 January 2014 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 01 January 2014 18:23

Take the time to read and understand this topic.


Re: Create DB Trigger [message #604585 is a reply to message #604582] Wed, 01 January 2014 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>then its possible or not....
also it is possible to poke yourself in the eye with a sharp pencil; but both should be avoided by everyone who knows better.
Re: Create DB Trigger [message #604592 is a reply to message #604578] Wed, 01 January 2014 13:32 Go to previous message
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


Previous Topic: Removing empty space
Next Topic: Query with multiple parameters
Goto Forum:
  


Current Time: Fri Mar 29 04:43:33 CDT 2024