November 3rd, 2003, 01:43 PM
Trigger insert, update, and delete
Can someone please help me. I am a newbie in Pl/sql.
I like to write a trigger to insert, update, and delete a row.
When row is inserted/updated/deleted in table1, the same action is also applied for table2.
November 3rd, 2003, 02:34 PM
According to Oracle dicumentation "A trigger associates with A table".
Say with me again "A trigger associates with A table"
November 6th, 2003, 07:58 PM
Trigger to insert/update/delete
Yes, a trigger associates with table.
Here is the suituation:
If I insert data into table1, same data will be inserted into table2.
If I update data in table1, row with same primary key in table2 is also updated. Same for deleting.
November 7th, 2003, 04:06 AM
Why a view is not enough?
November 7th, 2003, 10:19 AM
Here is an example of world popular table 'EMP', I make another table 'EMP_TEMP' identical to EMP and now i want to update, insert and delete record from EMP_TEMP whenever an update, insert, and delete occured in emp. For this purpse i am creating a trigger called 'Emp_Ins_Upd_Del_Trig', complete list as follows.
CREATE or REPLACE TRIGGER Emp_Ins_Upd_Del_Trig
BEFORE delete or insert or update on EMP
FOR EACH ROW
if UPDATING then
SET ename = :new.ename
, job = :new.job
, mgr = :new.mgr
, hiredate = :new.hiredate
, sal = :new.sal
, comm = :new.comm
, deptno = :new.deptno;
if INSERTING then
INSERT INTO emp_temp
if DELETING then
DELETE FROM emp_temp
WHERE empno = ld.empno;
You first must test it by inserting, deleting and updating rows on emp table, Once you satisfied then apply it in your program.
Another option that you have is CREATE MATERIALIZED VIEW for any base table. It is a different from normal view, in normal view oracle only saves the query of that view in dictionary so that whenever you run any select statement on the normal view oracle reads the query from data dictionary and extract the data from tables, views those are using in creation of view's query, But it is rverse in the Materialized view, oracle stores the data instead of storing query.
Oracle implicitly keeps Materialized view identical to the base table.
November 7th, 2003, 11:03 AM
Personally I don't think that a materialized view has advantages over a traditional view unless there are aggregations or complex calculations or many joins in the object's definition, so my advice is to use a traditional view if you just need a subset of a table or to join two/three tables.
November 24th, 2003, 05:33 PM
Thank you all.
Especially, thank to shafique. I got it now. It works as what I expected.