| Clarification [message #9248] |
Wed, 29 October 2003 01:23  |
Trinadh
Messages: 2
Registered: October 2003
|
Junior Member |
|
|
Sir
Please give me solution for the below question.
We have two tables with same number of columns with same data type , we want to insert the data into corresponding table, based on a condition.
For that what we known is, we can use INSTEAD OF TRIGGERS, at the same time we have to create type object and view, while creating type object it is giving error i.e.
MESSAGE 154 NOT FOUND; PRODUCT = PLUS33, FACILITY=SP1.
What we need is, when we enter an employee details through form, it has to INSERT the data into corresponding table based REGULAR OR NON REGULAR condition.
Here we are giving the query what we have used.
Creation of first table is
CREATE TABLE EMP1 AS
SELECT EMPNO,ENAME FROM EMP;
Creation of second table is
CREATE TABLE EMP2AS
SELECT EMPNO,ENAME FROM EMP;
Creation of TYPE OBJECT is
CREATE TYPE EMP_TYPE AS OBJECT
(EMPNO NUMBER(4),
ENAME VARCHAR2(14),
REG VARCHAR2(10)
)
Creation of View is
CREATE OR REPLACE VIEW EMP_VIEW (EMPNO)
AS SELECT EMP_TYPE (EMPNO,ENAME,’NON REGULAR’)
FROM EMP1
UNION ALL
SELECT EMP_TYPE (EMPNO,ENAME,’REGULAR’)
FROM EMP2;
Creation of Trigger is
CREATE TRIGGER inserting INSTEAD OF INSERT ON EMP_VIEW
FOR EACH ROW
BEGIN
IF (:NEW.REG = ‘NON REGULAR’) THEN
INSERT INTO EMP1
VALUES (:NEW.EMPNO,:NEW.ENAME);
ELSE
INSERT INTO EMP2
VALUES (:NEW.EMPNO,:NEW.ENAME);
END IF;
END;
|
|
|
|
|
|
| Re: Clarification [message #9272 is a reply to message #9256] |
Thu, 30 October 2003 01:37  |
Trinadh
Messages: 2
Registered: October 2003
|
Junior Member |
|
|
Inserting values into corresponding tables through view, we used TYPE OBJECT required.
Please give me solution without using TYPE OBJECT.
I hope you understood the problem.
So please give me
|
|
|
|
| Re: Clarification [message #9283 is a reply to message #9272] |
Thu, 30 October 2003 10:21 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Why wouldn't you just use:
create or replace view emp_view
(empno, ename, reg)
as
select empno, ename, 'NON REGULAR' reg
from emp1
union all
select empno, ename, 'REGULAR' reg
from emp2;
create or replace trigger emp_view_inserting
instead of insert on emp_view
for each row
begin
if (:new.reg = 'NON REGULAR') then
insert into emp1 values (:new.empno, :new.ename);
else
insert into emp2 values (:new.empno, :new.ename);
end if;
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
