-
Apr 17th, 2009, 03:06 PM
#1
Oracle - Using an INSTEAD OF TRIGGER
Two tables that store information about employees.
Code:
CREATE TABLE tblEmployee
(employee_no varchar2(10) NOT NULL, f_name varchar2(30), l_name varchar2(30), dob date, ssn varchar2(12));
CREATE TABLE tblAddress
(employee_no varchar2(10), address1 varchar2(20),
address2 varchar2(20), city varchar2(10),
state varchar2(2), zip varchar2(7),
effective_from date NOT NULL, effective_to date);
--Random Sample Data in the base tables
insert into tblEmployee values('JCASH', 'Johnny','Cash','12-Feb-1930', '184-12-3456');
insert into tblAddress values('JCASH','25 Fir St',NULL,'Memphis','TN','02345','01-Feb-1950','04-Apr-1970');
insert into tblAddress values('JCASH','25 Hollywood Ave',NULL,'Los Angel','CA','07345','04-Apr-1970',NULL);
View that allows users to see employee information.
Code:
CREATE OR REPLACE VIEW vw_EMPLOYEE_INFO
AS
SELECT tE.employee_no, tE.f_name, tE.l_name, to_char(tE.dob,'mm/dd') dob,
tA.address1, tA.address2, ta.city, tA.state, ta.zip, ta.effective_from,
ta.effective_to
FROM tblEmployee tE, tblAddress tA
WHERE tE.employee_no = tA.employee_no(+);
Code:
'selecting columns from the view.
select * from vw_Employee_INFO
Any attempts to update columns in this view will be met with this error message.
Code:
update vw_Employee_Info
set city = 'Sun City'
where employee_no = 'JCASH'
and city = 'Los Angel'
ORA-01779: cannot modify a column which maps to a non key-preserved table.
To avoid this predicament, you can create an instead of view.
Code:
CREATE OR REPLACE TRIGGER ioft_vw_emp
INSTEAD OF UPDATE
ON vw_EMPLOYEE_INFO
FOR EACH ROW
BEGIN
UPDATE tblAddress
SET address1 = :NEW.address1,
address2 = :NEW.address2,
city = :NEW.city,
state = :NEW.state,
zip = :NEW.zip,
effective_from = :NEW.effective_From,
effective_to = :NEW.effective_to
WHERE employee_no = :OLD.employee_no;
UPDATE tblEmployee
SET f_name = :NEW.F_NAME,
l_name = :NEW.l_name
WHERE employee_no = :OLD.employee_no;
END ioft_vw_emp;
The above update code will now work, thanks to this instead of trigger.
Code:
update vw_Employee_Info
set city = 'Sun City'
where employee_no = 'JCASH'
and city = 'Los Angel'
Tested on Oracle 10G for windows.
More later.
Last edited by abhijit; Apr 17th, 2009 at 03:33 PM.
Reason: Correct thread title.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Apr 17th, 2009, 03:46 PM
#2
Re: Oracle - Using an INSTEAD OF TRIGGER
You can also use an INSTEAD of trigger to insert new records from a view.
Using the same example in the above post, if you attempt to insert a record.
Code:
INSERT INTO vw_Employee_Info(employee_no, address1, address2, city, state, zip, effective_from)
values('JCASH','100 Swede Rd','Apt-101','Riverdale','CA','09851',trunc(sysdate));
ORA-01779: cannot modify a column which maps to a non key-preserved table.
To prevent this error from occurring, introduce the following code.
Code:
CREATE OR REPLACE TRIGGER ioft_vw_emp_ins
INSTEAD OF INSERT
ON vw_EMPLOYEE_INFO
FOR EACH ROW
BEGIN
INSERT INTO tblAddress values(:NEW.employee_no,
:NEW.address1,
:NEW.address2,
:NEW.city,
:NEW.state,
:NEW.zip,
:NEW.effective_From,
:NEW.effective_to);
END ioft_vw_emp_ins;
This will now allow you to insert records in the child table.
You could also have a trigger to insert records in the main table, but that will require adding some extra logic.
Tested on Oracle 10G running on windows.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|