yeah fine but...
Sekhar, December 30, 2002 - 6:26 am UTC
if it is an " instead of update " trigger then when would this fire is it before update or after update ..?
December 30, 2002 - 8:38 am UTC
it is INSTEAD OF, neither before nor after -- but INSTEAD OF....
The update isn't happening -- your code is running INSTEAD OF the update.
Why the trigger disappears...
Kamal Kishore, May 28, 2003 - 7:33 pm UTC
Hi Tom,
After you re-create the view definition using CREATE OR REPLACE (maybe to change its condition), the trigger on the view disappears. Is this expected behaviour?
SQL> create or replace view emp_view
2 as
3 select * from emp
4
SQL> /
View created.
SQL> create or replace trigger trig_emp_view
2 instead of insert or update on emp_view
3 for each row
4 begin
5 Null ;
6 end ;
7 /
Trigger created.
SQL> show errors
No errors.
SQL> select ut.trigger_name, ut.table_owner, ut.table_name
2 from user_triggers ut where trigger_name = 'TRIG_EMP_VIEW'
3 /
TRIGGER_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRIG_EMP_VIEW KKISHORE EMP_VIEW
1 row selected.
SQL> create or replace view emp_view
2 as
3 select * from emp
4 /
View created.
SQL> select ut.trigger_name, ut.table_owner, ut.table_name
2 from user_triggers ut where trigger_name = 'TRIG_EMP_VIEW'
3 /
no rows selected
SQL>
SQL>
May 28, 2003 - 8:34 pm UTC
the "or replace" is replacing the view and all related things. the create or replace preserves grants -- not the triggers. it is a "new view"
Instead of trigger and returning clause doesn't work
Piotr Jarmuz, October 26, 2003 - 6:27 am UTC
The "returning into clause" in an insert statement on views which have instead of trigger doesn't work.
I get ORA-22816: unsupported feature with RETURNING clause
I want to provide a "pure SQL" interface to complex stored procedures via instead of triggers and I want to save on one network roundtrip by getting surogate primary key in returning clause. My stored procedure creates an object, maintains business rules, and returns object's surogate PK.
Is there any workaround to do that?
Thank you in advance.
October 26, 2003 - 10:18 am UTC
why? stored procedures are not evil.
levels and layers of obscurity can be.
"pure sql" is usually raised up for database independence but since that cannot be the goal here (you are doing too many things only Oracle can do) so thats not the reason.
why "pure sql" in this case? what is the benefit you percieve from that?
You are right
Piotr Jarmuz, October 26, 2003 - 12:24 pm UTC
I wanted this "pure sql" because there could be some ODBC programs that might need partial access to my system. And I am not sure that they can understand procedure call (never worked with ODBC). But this is not so crucial. It is supposed to be just-in-case-feature.
Stored procedures are the best. Without them polimorphism the way I implemented would not work, anyway. Only fraction of functionality would be avaialble for classes known at compile time.
Thank you very much for sharing your thoughts.
Regards,
Piotr
October 26, 2003 - 1:51 pm UTC
ODBC can do stored procedure calls, definitely
Thank you very much
Piotr Jarmuz, October 26, 2003 - 2:09 pm UTC
trigger
A reader, November 20, 2003 - 12:54 pm UTC
Hi
Let's say I have table A, B, C, I would like to write a trigger on A so when a row is inserted if that row's PK exists in B instead of inserting to A it inserts to C. Is this possible ?
Tried instead of trigger but it says only works on VIEWS!
November 21, 2003 - 4:31 pm UTC
correct, it only works on views.
it would be (in my opinion) a really utterly truly horrible idea to do what you ask for. "magical side effects happening by accident".
I would use a stored procedure that does your processing -- don't try to get magically fancy with a trigger like this.
(i could show you how to do it, but I feel it is so utterly bad in its concept that I'm not going to)....
Regarding DML Statement inside Insetad of Trigger
Sushanta, April 28, 2004 - 6:43 am UTC
Tom,
When we write DML Statement inside a trigger it is always
go on parsing (Soft). I also tested it and it's true and
it is better to place all the DML statement in a API.
But i find something different in case of "INSTEAD OF"
trigger.
I have try this
1) CREATE TABLE FF4_TAB (A NUMBER,B VARCHAR2(2000));
2) CREATE OR REPLACE VIEW FF4 AS SELECT A,B FROM FF4_TAB;
3)CREATE OR REPLACE TRIGGER FF4_1ST
INSTEAD OF INSERT
ON FF4
DECLARE
t_date DATE;
BEGIN
INSERT INTO FF4_TAB(A,B) VALUES(:new.A,:new.B);
SELECT SYSDATE INTO t_date FROM DUAL;
END;
4) BEGIN
FOR I IN 1..10
LOOP
INSERT INTO ff4 values (i,i||i);
END LOOP;
END;
From the Tkprof File i found this
1) BEGIN
FOR I IN 1..10
LOOP
INSERT INTO ff4 values (i,i||i);
END LOOP;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
2) INSERT INTO FF4
VALUES
( :b1,:b1 || :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 10 0.01 0.00 0 0 0 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.01 0.01 0 0 0 10
3) INSERT INTO FF4_TAB ( A,B )
VALUES
( :b1,:b2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.01 0 1 17 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.01 0 1 17 10
4) SELECT SYSDATE
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 10 40 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.00 0.00 0 10 40 10
From the (3) and (4) i found that the parse Count is 1
always , should this mean that when we write DML
statement in a "INSTEAD OF TRIGGER" it is always parse 1
time , and the behaviour of the "INSTEAD OF TRIGGER" is
different then "NORMAL TRIGGER".
Please Clarify
Regards
Sushanta
trigger the
Not Reply on the Question
sushanta, April 30, 2004 - 2:25 am UTC
Tom,
There is No Reply on this
Regrads
Sushanta
April 30, 2004 - 7:31 am UTC
i've said it over and over -- i quite simply *don't see them all*. this isn't the place for questions really, the UI i have doesn't facilitate me answering them all.
but you have a SINGLE statement there. do 4 inserts and you'll see. do this instead:
INSERT INTO ff4 values (1,11);
INSERT INTO ff4 values (1,11);
INSERT INTO ff4 values (1,11);
and you'll observe:
SELECT SYSDATE
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 9 0 3
Misses in library cache during parse: 1
3 parses. In a single statement, the sql is cached for the trigger. when the statement (your plsql block) ends, poof, it is not cached anymore.
Only update certain columns
A reader, June 25, 2004 - 12:10 am UTC
Can I get the INSTEAD OF trigger to fire only when specific columns of the view are updated? If other columns are updated, raise an error?
Thanks
June 25, 2004 - 7:47 am UTC
grant update on specific columns only - the security level will take care of that.
Weird error when attempting an update on a certain column
Flavio Casetta, July 23, 2005 - 6:21 pm UTC
Hello Tom,
I was working on an HTMLDB application and I created a view as follows:
create or replace view promo_users as
select user_id, user_name, last_name, nvl(to_number(attribute_01),0) as store_group_id from WWV_FLOW_USERS
where user_id in (
select user_id
from WWV_FLOW_GROUP_USERS
where group_name = 'PROMO')
As this is not an updatable view, I defined an INSTEAD OF trigger on it, as follows:
CREATE OR REPLACE Trigger BU_PROMO_USERS
instead of update on promo_users
for each row
begin
htmldb_util.set_attribute(:new.user_id, 1, to_char(:new.store_group_id));
end;
In order to test the trigger I executed the following from the SQL Processor page:
update PROMO_USERS
set STORE_GROUP_ID = 2
where USER_ID = 3645511100442249;
and the value was correctly updated.
When I tried to do the same from my app's page, using the built-in Multi-Row-Update routine, I got
Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01031: insufficient privileges, update "YOCOYA"."PROMO_USERS" set "USER_ID" = :b1, "STORE_GROUP_ID" = :b2 where "USER_ID" = :p_pk_col
The problem seems to be with the updating of the USER_ID column.
Note that I don't understand why the MRU routine wants to update that column, I tried to see if there is a way to avoid it, but I failed to find a solution.
I have also verified that the problem can be reproduced from SQL Processor on any column that is not manipulated in the view.
In other terms, if I take the column LAST_NAME and I try to update it, the update fails with the same error (ORA-01031), but if I apply a function like INITCAP on LAST_NAME inside the view, then the column becomes updatable.
This happens on oracle 10.1.0.2 running on Windows 2000.
Any clues?
Bye,
Flavio
PS: I just verified that wrapping the columns as follows, the problem goes away:
create or replace VIEW PROMO_USERS ( USER_ID, USER_NAME, LAST_NAME, STORE_GROUP_ID ) AS
select nvl(user_id,0) as user_id, initcap(user_name) as user_name, initcap(last_name) as last_name, nvl(to_number(attribute_01),0) as store_group_id
from WWV_FLOW_USERS
where user_id in (
select user_id
from WWV_FLOW_GROUP_USERS
where group_name = 'PROMO')
However I don't understand why I have to artificially alter the columns to achieve this.
July 23, 2005 - 6:44 pm UTC
what you tested in sqlplus bears little/no resemblance to what actually is updated.
did you try that?
not sure I got the point in your answer...
Flavio Casetta, July 24, 2005 - 11:31 am UTC
Tom,
do you mean that
update PROMO_USERS
set STORE_GROUP_ID = 2
where USER_ID = 3645511100442249;
and
update "YOCOYA"."PROMO_USERS" set "USER_ID" = :b1,
"STORE_GROUP_ID" = :b2 where "USER_ID" = :p_pk_col;
are too different from one another?
I also tried from SQL processor the following:
update "YOCOYA"."PROMO_USERS" set "USER_ID" = 3645511100442249,
"STORE_GROUP_ID" = 2
where "USER_ID" = 3645511100442249;
and I got the same ORA-01031.
If I remove the useless
"USER_ID" = 3645511100442249,
it works.
The problem is definitely linked to the column being updated.
The fact that a column becomes "updatable" only after manipulating its value in some way inside the view, puzzles me.
Thanks
Flavio
July 24, 2005 - 12:43 pm UTC
I always try to remove as many "non-relevant bits" from the picture in order to see whats up.
You should now be able to provide us a standalone script that has a couple of very small creates, and an example statement "not working", 100% self contained but very very small -- to reproduce with.
The answer to the question will probably jump off the screen at us when you do that.
You can turn on sql_trace=true as well to see the precise sql statement that is failing.
Almost there...
Flavio, July 24, 2005 - 6:33 pm UTC
Tom,
I removed the NVL function around user_id column in the view and enabled tracing.
The view source is
CREATE OR REPLACE VIEW PROMO_USERS ( USER_ID, USER_NAME, LAST_NAME, STORE_GROUP_ID ) AS
select user_id,
lower(user_name) as user_name,
lower(last_name) as last_name,
to_number(attribute_01) as store_group_id
from WWV_FLOW_USERS
where user_id in (
select user_id
from WWV_FLOW_GROUP_USERS
where group_name = 'PROMO')
Here is the dump:
---------------------------------------
Dump file e:\oracle\admin\dev10g\udump\dev10g_ora_2328.trc
Wed Mar 09 22:42:31 2005
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
skgdllInit: ctx [06148660] cbs [033ED0AC] cbsctx [05F6D450]
skgdllopen: abspath = FALSE, libpath = odm
skgdllAlloc: Allocating 0617E584 of size 20 for skgdllhdl
skgdllopen: Successfully opened lib handle = 0617E584 D:\ORACLE\ORA10G\BIN\ORAODM10.DLL
skgdllsym: Looking up sym odm_discover in hdl 0617E584
skgdllsym: Successfully loaded sym odm_discover from 0617E584 symptr = 60821000
skgdllsym: Looking up sym odm_init in hdl 0617E584
skgdllsym: Successfully loaded sym odm_init from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_fini in hdl 0617E584
skgdllsym: Successfully loaded sym odm_fini from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_error in hdl 0617E584
skgdllsym: Successfully loaded sym odm_error from 0617E584 symptr = 60821030
skgdllsym: Looking up sym odm_create in hdl 0617E584
skgdllsym: Successfully loaded sym odm_create from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_commit in hdl 0617E584
skgdllsym: Successfully loaded sym odm_commit from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_abort in hdl 0617E584
skgdllsym: Successfully loaded sym odm_abort from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_delete in hdl 0617E584
skgdllsym: Successfully loaded sym odm_delete from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_identify in hdl 0617E584
skgdllsym: Successfully loaded sym odm_identify from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_reidentify in hdl 0617E584
skgdllsym: Successfully loaded sym odm_reidentify from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_unidentify in hdl 0617E584
skgdllsym: Successfully loaded sym odm_unidentify from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_cleanup in hdl 0617E584
skgdllsym: Successfully loaded sym odm_cleanup from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_io in hdl 0617E584
skgdllsym: Successfully loaded sym odm_io from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_ioerror in hdl 0617E584
skgdllsym: Successfully loaded sym odm_ioerror from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_cancel in hdl 0617E584
skgdllsym: Successfully loaded sym odm_cancel from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_resize in hdl 0617E584
skgdllsym: Successfully loaded sym odm_resize from 0617E584 symptr = 60821050
skgdllsym: Looking up sym odm_mname in hdl 0617E584
skgdllsym: Successfully loaded sym odm_mname from 0617E584 symptr = 60821050
skgdllclose: Called close on 0617E584
skgdllclose: Successfully closed lib 0617E584
skgdllFree: Freeing 0617E584 of skgdllhdl
skgdllDestroy: ctxp = 06148660 Ctxpp = 06147E9C
Dump file e:\oracle\admin\dev10g\udump\dev10g_ora_2328.trc
Wed Mar 09 22:42:50 2005
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows 2000 Version V5.0 Service Pack 4
CPU : 1 - type 586
Process Affinity: 0x00000000
Memory (A/P) : PH:293M/639M, PG:882M/1389M, VA:1773M/2047M
Instance name: dev10g
Redo thread mounted by this instance: 1
Oracle process number: 13
Windows thread id: 2328, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2005-03-09 22:42:50.106
*** SESSION ID:(162.7) 2005-03-09 22:42:50.106
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Assigned RBS 4 to pool 0
Undo Segment 4 Onlined
Assigned RBS 5 to pool 1
Undo Segment 5 Onlined
Assigned RBS 6 to pool 2
Undo Segment 6 Onlined
Assigned RBS 7 to pool 3
Undo Segment 7 Onlined
Assigned RBS 8 to pool 4
Undo Segment 8 Onlined
Assigned RBS 9 to pool 5
Undo Segment 9 Onlined
Assigned RBS 10 to pool 6
Undo Segment 10 Onlined
adbdrv: lang flag = 0
Dump file e:\oracle\admin\dev10g\udump\dev10g_ora_2328.trc
Sun Jul 24 23:53:20 2005
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows 2000 Version V5.0 Service Pack 4
CPU : 1 - type 586
Process Affinity: 0x00000000
Memory (A/P) : PH:280M/639M, PG:842M/1389M, VA:1664M/2047M
Instance name: dev10g
Redo thread mounted by this instance: 1
Oracle process number: 29
Windows thread id: 2328, image: ORACLE.EXE (SHAD)
*** 2005-07-24 23:53:20.714
*** ACTION NAME:(application 4500, page 3, sessio) 2005-07-24 23:53:20.664
*** MODULE NAME:(HTML DB) 2005-07-24 23:53:20.664
*** SERVICE NAME:(dev10g.yocoya.com) 2005-07-24 23:53:20.664
*** SESSION ID:(149.2009) 2005-07-24 23:53:20.664
=====================
PARSING IN CURSOR #18 len=32 dep=3 uid=84 oct=42 lid=84 tim=27206973373 hv=2097336573 ad='1917eca4'
ALTER SESSION SET SQL_TRACE=TRUE
END OF STMT
EXEC #18:c=0,e=1618,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=1,tim=27206973348
=====================
PARSING IN CURSOR #1 len=37 dep=4 uid=0 oct=3 lid=0 tim=27207027782 hv=1398610540 ad='1bdf285c'
select text from view$ where rowid=:1
END OF STMT
PARSE #1:c=0,e=1781,p=0,cr=0,cu=0,mis=1,r=0,dep=4,og=4,tim=27207027761
=====================
PARSING IN CURSOR #58 len=210 dep=5 uid=0 oct=3 lid=0 tim=27207054533 hv=864012087 ad='1bc650bc'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #58:c=10014,e=9749,p=0,cr=0,cu=0,mis=1,r=0,dep=5,og=3,tim=27207054510
EXEC #58:c=20029,e=27644,p=0,cr=0,cu=0,mis=1,r=0,dep=5,og=3,tim=27207085094
FETCH #58:c=0,e=121,p=0,cr=2,cu=0,mis=0,r=0,dep=5,og=3,tim=27207086015
EXEC #1:c=50072,e=64226,p=0,cr=2,cu=0,mis=1,r=0,dep=4,og=4,tim=27207101163
FETCH #1:c=0,e=271,p=0,cr=2,cu=0,mis=0,r=1,dep=4,og=4,tim=27207102140
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #51 len=37 dep=4 uid=0 oct=3 lid=0 tim=27207105741 hv=1398610540 ad='1bdf285c'
select text from view$ where rowid=:1
END OF STMT
PARSE #51:c=0,e=189,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=4,tim=27207105717
EXEC #51:c=0,e=142,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=4,tim=27207107953
FETCH #51:c=0,e=129,p=0,cr=2,cu=0,mis=0,r=1,dep=4,og=4,tim=27207108577
STAT #51 id=1 cnt=0 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #47 len=37 dep=4 uid=0 oct=3 lid=0 tim=27207113963 hv=1398610540 ad='1bdf285c'
select text from view$ where rowid=:1
END OF STMT
PARSE #47:c=0,e=235,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=4,tim=27207113938
EXEC #47:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=4,tim=27207116183
FETCH #47:c=0,e=133,p=0,cr=2,cu=0,mis=0,r=1,dep=4,og=4,tim=27207116860
STAT #47 id=1 cnt=0 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSE ERROR #27:len=119 dep=3 uid=84 oct=6 lid=84 tim=27207126223 err=1031
UPDATE "YOCOYA"."PROMO_USERS" SET "USER_ID" = 3645511100442249, "STORE_GROUP_ID" = 2 WHERE "USER_ID" = 364551110044224
=====================
PARSING IN CURSOR #29 len=33 dep=3 uid=84 oct=42 lid=84 tim=27207134387 hv=3454464356 ad='191e8250'
ALTER SESSION SET SQL_TRACE=FALSE
END OF STMT
PARSE #29:c=0,e=1548,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=1,tim=27207134365
EXEC #29:c=0,e=1613,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=27207138508
-------------------------------------
I'd be very glad to create a simple script but unfortunately the view PROMO_USERS is based on HTMLDB's view WWV_FLOW_USERS, that works only while you are inside the HTMLDB environment. If I execute it from SQL plus I get zero rows, while I get many if execute it from inside the HTMLDB environment.
Here is the source of the htmldb view:
VIEW FLOWS_010600.WWV_FLOW_USERS ( USER_ID, USER_NAME, FIRST_NAME, LAST_NAME, KNOWN_AS, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, START_DATE, END_DATE, DESCRIPTION, PASSWORD_DATE, PASSWORD_ACCESSES_LEFT, PASSWORD_LIFESPAN_ACCESSES, PASSWORD_LIFESPAN_DAYS, EMPLOYEE_ID, PERSON_TYPE, EMAIL_ADDRESS, PERSON_ID, SECURITY_GROUP_ID, WEB_PASSWORD, WEB_PASSWORD_RAW, DEFAULT_SCHEMA, ALLOW_ACCESS_TO_SCHEMAS, ATTRIBUTE_01, ATTRIBUTE_02, ATTRIBUTE_03, ATTRIBUTE_04, ATTRIBUTE_05, ATTRIBUTE_06, ATTRIBUTE_07, ATTRIBUTE_08, ATTRIBUTE_09, ATTRIBUTE_10 ) AS
select "USER_ID","USER_NAME","FIRST_NAME","LAST_NAME","KNOWN_AS","LAST_UPDATE_DATE","LAST_UPDATED_BY","CREATION_DATE","CREATED_BY","START_DATE","END_DATE","DESCRIPTION","PASSWORD_DATE","PASSWORD_ACCESSES_LEFT","PASSWORD_LIFESPAN_ACCESSES","PASSWORD_LIFESPAN_DAYS","EMPLOYEE_ID","PERSON_TYPE","EMAIL_ADDRESS","PERSON_ID","SECURITY_GROUP_ID","WEB_PASSWORD","WEB_PASSWORD_RAW","DEFAULT_SCHEMA","ALLOW_ACCESS_TO_SCHEMAS","ATTRIBUTE_01","ATTRIBUTE_02","ATTRIBUTE_03","ATTRIBUTE_04","ATTRIBUTE_05","ATTRIBUTE_06","ATTRIBUTE_07","ATTRIBUTE_08","ATTRIBUTE_09","ATTRIBUTE_10" from wwv_flow_fnd_user
where security_group_id = (select nv('FLOW_SECURITY_GROUP_ID') s from dual)
It's not so easy to set up a stand alone example showing the problem, the best I could do is to show what makes the difference between a failure and a success and in my case it's a NVL function against the column holding the primary key.
Thanks!
Flavio
July 24, 2005 - 7:35 pm UTC
so, do you have the grant to update that userid column and should you be updating that userid column
question: why are you updating this table anyway?? why is not this just an API call or something you write to do something.
It's a long story
Flavio, July 25, 2005 - 6:15 am UTC
Tom,
I created the INSTEAD OF trigger as a means for bulk updating those "attribute_01 ... attribute_10" columns that are available to the developer.
I need a table or a view to do the process because HTMLDB gives me some powerful tools for mass updating columns on many rows at a time, like MRU routines attached to updatable queries.
I thought I could simply re-use the available view on the users and solve the problem by means of that INSTEAD-OF trigger.
The update of the user_id column is out of my control, it's the HTMLDB engine that for some reason wants to update the column as part of its work. I tried to see if there is any way of avoiding it, but could not find out how.
Alternatively I could create a table where I copy the contents of WWV_FLOW_USERS each time the form is loaded and invoke the API in an AFTER UPDATE trigger, but doesn't it sound like unnecessary work to you?
I mean, whatever I do, I'll have to call an API to update that value.
If I can do this inside a simple trigger attached to a view, why should I go for a solution involving a duplicate table + trigger + additional logic to populate/clear the table?
Bye,
Flavio
July 25, 2005 - 8:08 am UTC
I'm very suspicious of an instead of trigger on a view of dictionary information.
I suggest you try not to be "so fancy" here, code works good too sometimes.
Un Wrapping a wrapped procedure
A.N.V.Pavan Kumar, July 25, 2005 - 7:18 am UTC
Sir,
I have a stored procedure which is in wrapped format. Is there any way to get the actual sorce code for that?
Thanks And Regards,
A.N.V.Pavan Kumar.
July 25, 2005 - 8:16 am UTC
it is a one way operation (the goal is to make it so others cannot get your code and unwrap it and steal it basically)
All right, the message is clear now!
Flavio, July 25, 2005 - 9:52 am UTC
All right Tom,
so you're in favor of the table loaded upon opening the form.
Thanks for pointing this out.
Bye,
Flavio
ORA-01031 with INSTEAD OF Trigger
Rumburak, September 28, 2005 - 12:40 pm UTC
Hi Tom,
I have following problem: I created my own user-table where I want to store some attributes like user-foto etc... This table I join with the HTMLDB wwv_flow_fnd_user (I gave myself the select grant on it) in a view
On the view I created an INSTEAD-OF-Trigger for DELETE-Operations where I delete only my user-table. But when I execute it it gives me an
ORA-01031: insufficient privileges
I can not understand it.
===========================================================
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 27 05:28:13 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> DROP TABLE my_users
2 ;
Table dropped.
SQL> CREATE TABLE my_users AS
2 SELECT user_id
3 FROM flows_020000.wwv_flow_fnd_user
4 ;
Table created.
SQL> ALTER TABLE my_users ADD my_attr VARCHAR2(10);
Table altered.
SQL> ALTER TABLE my_users ADD CONSTRAINT pk_my_users PRIMARY KEY( user_id );
Table altered.
SQL> CREATE OR REPLACE VIEW v_my_users
2 AS
3 SELECT u2.user_id,
4 u.user_name,
5 u.first_name,
6 u.last_name,
7 u2.my_attr
8 FROM flows_020000.wwv_flow_fnd_user u,
9 my_users u2
10 WHERE u2.user_id = u.user_id
11 ;
View created.
SQL> CREATE OR REPLACE TRIGGER trg_del_v_my_users
2 INSTEAD OF DELETE ON v_my_users
3 FOR EACH ROW
4 BEGIN
5 DELETE my_users
6 WHERE user_id = :OLD.user_id
7 ;
8 END;
9 /
Trigger created.
SQL> DELETE v_my_users WHERE 1=2
2 ;
DELETE v_my_users WHERE 1=2
*
ERROR at line 1:
ORA-01031: insufficient privileges
===========================================================
And: If there is no PK on table my_users I got not error!
Can you explain it?
September 28, 2005 - 1:34 pm UTC
I can explain it.
without the primary key on your table (or unique key, or unique index in fact) it is "known" that
delete from v where .....
will delete ONLY from your table, your table is the only "key preserved table" in the join. It cannot delete from the htmldb table (which has a primary key) since the htmldb table is not key preserved.
When you join your table "A" to htmldb's table "B" and you don't have a key and they do -- we know
o every row in A will join to at MOST one row in B
o every row in B will join to some UNKNOWN number of rows in A
therefore, only A can safely be deleted from in that join view.
However, when you put the key on there, then:
o every row in A will join to at MOST one row in B
o every row in B will join to at MOST one row in A
and they both become key preserved and the security check jumps in (no trigger is looked at) and it says "sorry, you cannot delete from that table"
Here, I would question the need to delete from the view? You only need to delete from the table itself.
The following is a demonstration of this fact and a "hack" that may work - but if the optimizer ever gets smart enough - might not work:
ops$tkyte@ORA10GR2> create user a identified by a default tablespace users quota unlimited on users;
User created.
ops$tkyte@ORA10GR2> create user b identified by b default tablespace users quota unlimited on users;
User created.
ops$tkyte@ORA10GR2> grant create session, create table to a;
Grant succeeded.
ops$tkyte@ORA10GR2> grant create session, create table, create view, create trigger to b;
Grant succeeded.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> @connect a/a
ops$tkyte@ORA10GR2> set termout off
a@ORA10GR2> @login
a@ORA10GR2> set termout off
a@ORA10GR2>
a@ORA10GR2> set termout on
a@ORA10GR2> create table t1 ( user_id number primary key, data number );
Table created.
a@ORA10GR2> grant select on t1 to b;
Grant succeeded.
a@ORA10GR2>
a@ORA10GR2> @connect b/b
a@ORA10GR2> set termout off
b@ORA10GR2> @login
b@ORA10GR2> set termout off
b@ORA10GR2>
b@ORA10GR2> set termout on
b@ORA10GR2>
b@ORA10GR2> create table t2 ( user_id number, data number );
Table created.
b@ORA10GR2> CREATE OR REPLACE VIEW v
2 AS
3 select a.user_id, a.data data_a, b.data data_b
4 from a.t1 a, t2 b
5 where a.user_id = b.user_id
6 and a.rowid <> b.rowid;
View created.
b@ORA10GR2>
b@ORA10GR2> CREATE OR REPLACE TRIGGER v_trigger
2 INSTEAD OF DELETE ON v
3 FOR EACH ROW
4 BEGIN
5 DELETE from t2
6 WHERE user_id = :OLD.user_id;
7 END;
8 /
Trigger created.
b@ORA10GR2> DELETE v WHERE 1=2;
0 rows deleted.
b@ORA10GR2> alter table t2 add constraint t2_pk primary key(user_id);
Table altered.
b@ORA10GR2> DELETE v WHERE 1=2;
DELETE v WHERE 1=2
*
ERROR at line 1:
ORA-01031: insufficient privileges
b@ORA10GR2> alter table t2 drop constraint t2_pk;
Table altered.
b@ORA10GR2> create unique index t2_idx on t2(user_id+0);
Index created.
b@ORA10GR2> alter table t2 modify user_id not null;
Table altered.
b@ORA10GR2> DELETE v WHERE 1=2;
0 rows deleted.
<b>by using a function based index, we can get "uniqueness" (you can index t2(user_id) normally if you like as well) and the not null together achieves the goal of a primary key - without a primary key</b>
Great Tom!
Rumburak, September 28, 2005 - 2:04 pm UTC
Many thanks!
Bye,
Rumburak
create or replace view
VA, December 21, 2005 - 4:35 pm UTC
Argh...I had a view with a instead of trigger on it.
I did a CREATE OR REPLACE VIEW ...
And guess what? My instead-of trigger is gone, blown away!
This is crazy...Why can't it preserve the trigger, just like it preserves the GRANTs associated with the view?
Just wanted to share my pain...
Cascading INSTEAD OF triggers
VA, June 07, 2006 - 10:40 am UTC
I have a view (V1) with a INSTEAD OF trigger, works fine.
I created another view (V2) with different columns and business logic on the same base table(s).
So, as to avoid duplicating the code in the trigger on V1 in the trigger on V2, I wrote the instead of trigger on V2 to simply do the DML on the view V1 instead (hoping that it would simply cascade the DML).
Strangely enough, the DML on V2 says '1 row updated' but the base tables are not updated!!
It is as if Oracle is somehow silently ignoring the "cascading" instead of triggers and lying with the '1 row updated' message!
Any idea why?
Thanks
June 07, 2006 - 3:00 pm UTC
got test case?
ops$tkyte@ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace view t_v1
2 as
3 select * from t;
View created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace view t_v2
2 as
3 select * from t;
View created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace trigger t_v1_trigger
2 instead of update on t_v1
3 begin
4 update t set y = :new.y+1 where x = :new.x;
5 end;
6 /
Trigger created.
ops$tkyte@ORA10GR2> create or replace trigger t_v2_trigger
2 instead of update on t_v2
3 begin
4 update t_v1 set y = :new.y where x = :new.x;
5 end;
6 /
Trigger created.
ops$tkyte@ORA10GR2> insert into t values (1,null);
1 row created.
ops$tkyte@ORA10GR2> commit;
Commit complete.
ops$tkyte@ORA10GR2> select * from t;
X Y
---------- ----------
1
ops$tkyte@ORA10GR2> update t_v2 set y = 1;
1 row updated.
ops$tkyte@ORA10GR2> select * from t;
X Y
---------- ----------
1 2
Cascading INSTEAD OF triggers
A reader, June 07, 2006 - 10:57 am UTC
Ignore my last question, the inner view had some logic that resulted in no matching rows. So even though the outer view said '1 row updated', the inner update had a sql%rowcount=0.
Sorry for the false alarm.
Executing DDL via INSTEAD OF triggers
Srikanth Sathya, April 22, 2008 - 11:29 am UTC
Tom
In your e-book 'Expert Oracle' that came as a suppliment along with another e-book that I purchased, I found an example (Page 668) related to executing a DDL statement inside an INSTEAD OF INSERT trigger. There the goal is to insert a row into APPLICATION_USERS_TBL and then creating the USER account. If the account creation fails then you undo the insert by deleting it in the exceptions. My question to you is, why not execute the GRANT first and if that is successful then do the INSERT? That way, would it not save the effort of undoing the INSERT?
Thanks for your time and comments.
- Srikanth. S
table is not updated at the back
vaneeza Ahmad, June 17, 2008 - 8:16 pm UTC
Hi Tom,
I have used instead of update trigger on a view which is created on the top of two other views by using union command. I am using oracle 10g with Toad 9. I can't update the view using Toad. On the other hand, when i tried to update using update command. The table was not updated, though my command was executed sucessfully. I am sending you my codes your comments will be greatly appreciated.
create or replace VIEW v_temp as
SELECT IDX,NAME
FROM emp
WHERE IDX=1
create or replace VIEW v_temp1 as
SELECT IDX,NAME
FROM emp
WHERE IDX=2
create or replace VIEW v_temp2 as
SELECT * FROM V_TEMP
UNION
SELECT * FROM V_TEMP1
create or replace trigger trig_emp_view
instead of update on V_TEMP2
for each row
begin
Null ;
end ;
UPDATE V_TEMP2 SET NAME='ABC'
Thanks
June 18, 2008 - 12:54 pm UTC
well.........
let's see....
reading your trigger, it basically says
"Hey, when you update V_TEMP2, please do NOTHING, not a thing, null is the word of the day"
what did you expect to have happen here? I mean - really. You are so very clearly saying "instead of you Oracle attempting to update this view (which is not possible since a union is a DISTINCT operation, it tends to remove rows and we cannot tell what table data came from), please do NOTHING, null"
Instead Of Triggers
Bhushan, July 29, 2008 - 4:13 am UTC
Hi Thomas,
The below example 'might' not have any practicle application but it is what i came across when trying to understand Instead OF Triggers.
My Understanding:
If the Trigger is 'Instead of Insert' on v_test, say.Then any insert statement fired on v_test is "ignored" and the code of the trigger is executed.
If this is correct,could you kindly explain the below displayed behaviour.
create table TEST
(
PO_NO NUMBER,
AMOUNT NUMBER(10,2),
DESCRIPTION VARCHAR2(100),
DUMMY CHAR(1)
)
insert into TEST (PO_NO, AMOUNT, DESCRIPTION, DUMMY)
values (1, 1000, 'FOR Oracle1', null);
insert into TEST (PO_NO, AMOUNT, DESCRIPTION, DUMMY)
values (2, 1000, 'FOR ORacle2', null);
insert into TEST (PO_NO, AMOUNT, DESCRIPTION, DUMMY)
values (3, 1000, 'FOR Oracle3', null);
commit;
CREATE VIEW v_test AS SELECT * FROM test;
CREATE OR REPLACE TRIGGER ins_v_test
INSTEAD OF INSERT
ON v_test
BEGIN
INSERT INTO test (SELECT * FROM test);
END;
Now,there are Three rows in Test table.
Let us issue these INsert Statements
INSERT INTO v_test VALUES (4,4000,'TEST',NULL);
After firing this command..instead of this, the Trigger code is executed so we get SIX rows in the table as expected because of the trigger statement
'INSERT INTO test (SELECT * FROM test);'
Rollback;
Let us fire this statement
INSERT INTO v_test (SELECT * FROM v_test);
Total number of rows in table blows to 24.
IF same code is executed "instead of any insert" FIred why the difference in Result.
Also,
Was INstead OF Trigger a way provided to deal with Mutating Table Errors..? (I am sure it was not..just need your thoughts if possible somewhat a practical example)
Thanks,
Bhushan
July 29, 2008 - 3:51 pm UTC
ops$tkyte%ORA10GR2> create table TEST
2 (
3 PO_NO NUMBER,
4 AMOUNT NUMBER(10,2),
5 DESCRIPTION VARCHAR2(100),
6 DUMMY CHAR(1)
7 )
8 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into TEST (PO_NO, AMOUNT, DESCRIPTION, DUMMY)
2 values (1, 1000, 'FOR Oracle1', null);
1 row created.
ops$tkyte%ORA10GR2> insert into TEST (PO_NO, AMOUNT, DESCRIPTION, DUMMY)
2 values (2, 1000, 'FOR ORacle2', null);
1 row created.
ops$tkyte%ORA10GR2> insert into TEST (PO_NO, AMOUNT, DESCRIPTION, DUMMY)
2 values (3, 1000, 'FOR Oracle3', null);
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> CREATE or replace VIEW v_test AS SELECT * FROM test;
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE TRIGGER ins_v_test
2 INSTEAD OF INSERT
3 ON v_test
4 BEGIN
5 INSERT INTO test (SELECT * FROM test);
6 END;
7 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(*) from test;
COUNT(*)
----------
3
ops$tkyte%ORA10GR2> insert into v_test values (1,1,1,1);
1 row created.
ops$tkyte%ORA10GR2> select count(*) from test;
COUNT(*)
----------
6
ops$tkyte%ORA10GR2> rollback;
Rollback complete.
ops$tkyte%ORA10GR2> select count(*) from test;
COUNT(*)
----------
3
ops$tkyte%ORA10GR2> insert into v_test values (1,1,1,1);
1 row created.
ops$tkyte%ORA10GR2> select count(*) from test;
COUNT(*)
----------
6
I do not see what you say - you did something other than what you say. Post full examples demonstrating what you say....
The mutating table CONSTRAINT (let us not call it an error - the only error is the logic you are attempting when you get that) is there to tell you "hey, your transactional logic is so botched, we are not going to even consider permitting it"
no, an instead of trigger is not a way to avoid mutating table constraints
avoid all triggers, all of them, they are evil.
Did you miss out or am i missing anything...?
Bhushan, July 29, 2008 - 4:44 pm UTC
hi Thomas,
I don't see you ran this key statement anywhere..May be it got lost in the "essay" i wrote above :)
INSERT INTO v_test (SELECT * FROM v_test);
this gives you the rows blown up to 24 for some reason i cant understand.
Please do keep up the good work!!!
Cheers!!!
Bhushan
August 01, 2008 - 10:36 am UTC
ahh, ok, if you run:
INSERT INTO v_test (SELECT * FROM v_test);
that will select 3 rows from v_test
the trigger will fire three times
time1: 3 rows added (total = 6)
time2: 6 rows added (total = 12)
time3: 12 rows added (total = 24)
your instead of trigger fires three times due to three rows being inserted, since your trigger does a silly thing (insert into table select * from table) the table doubles 3 times, from 3, to 6 to 12 to 24
trigger fires thrice
Sokrates, July 29, 2008 - 6:06 pm UTC
becauseyou rows in v_test
after no 1 you have 6 rows
after no 2 you have 12 rows
after no 3 you have 24 rows
instead of is always row level, not statement
sorry
Sokrates, July 29, 2008 - 6:07 pm UTC
read
"becauseyou rows in v_test"
as
"because you have 3 rows in v_test"
DO not Understand
Bhushan, July 30, 2008 - 2:28 am UTC
Sokrates,
What do you mean it gets doubled after every insert..? Is it some kind of SPECIAL ROW LEVEL Trigger then...? try this on the same table test and tell me what you see..
create table TRG_LOG
(
LOG_PO_NO NUMBER
)
CREATE OR REPLACE TRIGGER trg_test
AFTER INSERT
ON test
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO trg_log VALUES (:NEW.po_no);
END;
So according to your logic how many rows should we see in trg_log once the following insert is fired
INSERT INTO test SELECT * FROM test
Tom,
Need yout inputs please!!!!!
August 02, 2008 - 5:05 pm UTC
you did
insert into view
(a query that returns three rows)
your trigger, your instead of insert trigger, on the view will therefore fire three times.
once each for each row you attempted to insert into the view
it is quite simple, if you
insert into your_view (a query that returns N records)
your instead of trigger will fire N times, each time the :new record will be set to the record fetched from the query. You decide what to do each of the N times. You are being called INSTEAD OF US INSERTING.
If we were to insert N records, it only makes sense that you would be called N times.
In Simple Words
Bhushan, July 30, 2008 - 2:54 am UTC
Hi,
Why 'Instead of Trigger' is Fired Thrice (3 Rows in a table) for one INsert where as Simple Row Level Tiggers on Tables are fired once??
Were they "designed" that way?
Regards,
Bhushan
August 02, 2008 - 5:08 pm UTC
there are 4 types of triggers on a table:
before statement - fires ONCE (or twice, yes, twice, it can fire twice) per statement.
before, for each row - fires ONCE (or twice, yes twice...) per row inserted. It can modify the :new record
after, for each row - just like the before, for each row but after the insert has actually happened into the table. It can only read the :new record
after statement, fires ONCE for each statement.
for a view there is one type of trigger:
the instead of trigger, fires once for each row that would have been inserted into the view, instead of inserting that row into the view, we execute your code. that is the design, in fact - any other "design" (eg: to fire only once) would not make sense - you need to see each row, you need to do something to each row that was going to be inserted, you are the instead of inserting logic.
Instead of
Oleksandr Alesinskyy, August 01, 2008 - 10:09 am UTC
Bushian, that's easy. As triggers fires with every inserted row insert-as-select inside trigger body ("intern insert-as-select") is fired as many times as were rows in table before "top-level" insert as select. And each consequent intern inser-as-select sees all rows inserted by its preceders. So number of rows will really explode.
Limiting of INSTEAD OF triggers to VIEWs only - why?
Jim, August 21, 2008 - 12:42 pm UTC
Hi Tom
Could you explain what was the rationale behind limiting applicability of "INSTEAD OF" triggers to views only?
I was recently asked to modify an existing system to use "soft" deletes i.e. marking rows as deleted instead of physically deleting them and this would have been easily done if I could substitute "DELETE..." with "UPDATE... SET DELETED=true" - a job for an "INSTEAD OF" trigger.
MS SQL server doesn't have this restriction, neither has PostgreSQL but why Oracle has?....
Many thanks
August 21, 2008 - 10:08 pm UTC
I wish we didn't have them at all - really bad idea they were.
And you provide the perfect example why. "Let us do a bit of magic and make things just automagically happen - by surprise, we'll trick this silly old application into doing something"
I'm a fan of fixing the applications, correctly. Not trying magic.
rename table to table_table;
create view table as select columns from table_table;
there you go. Now you can be a magician and no one will know.... You have triggers on real tables, you have instead of triggers on views. A table can be replaced by a view in an instant.
Or, just version enable the table and deletes won't delete anymore, they'll flag delete.
INSTEAD OF trigger
Jim, August 22, 2008 - 8:10 am UTC
Dear Tom
Thanks for your reply but I can't agree with you on doing what you call "magic". Why not take it further and say "ah, doing a 'select ...' in an app is like magic, we don't know what's happening and don't see where the data is coming from, so it's a bad idea to use a DBMS and one better do it 'properly' by hard-coding DB schema and access procs in the app itself"... Why complicate things when it can be easily avoided?
Cheers for the tip on versioning - that was exactly the functionality we needed :-) For those interested to know it's implemented in the built-in package DBMS_WM
And you see Tom, DBMS_WM uses exactly that - INSTEAD OF trigger to do the versioning magic and it wouldn't have been able to without it. So having them isn't that bad an idea after all...
Thanks again,
Jim
August 22, 2008 - 9:54 am UTC
your analogy doesn't hold.
A select is rather straightforward, you code it and it happens.
Now, magic is "I issue delete, row is deleted" - but no, no it isn't, it is updated instead. That is magic, when I ask to do something and something else entirely happens.
It is a bad idea, I hate (despise) triggers - they, along with when others then null, account for most developed bugs I see anymore.
Oracle' recommendation
Govind, August 22, 2008 - 7:58 pm UTC
Tom,
I read somewhere that Oracle recommends using Views in place of tables for their forms applications and some times instead of triggers are a good alternative.
What do you see as an appropriate use of instead of trigger?
Regards, Govind
orafind.blogspot.com - Oracle power
August 26, 2008 - 7:50 pm UTC
I've written over and over again - I do not like triggers at all. They cause infinitely more harm than good in the world. They are misused, they are abused, they are used to implement "magic", they cause side effects people don't think about, in a multi-user environment - many of them are just DEAD WRONG in their logic.
Instead of an instead of trigger I suggest:
<this space left intentionally BLANK AND EMPTY>
nothing, you don't need them.
OS event triggers
rahul, September 24, 2008 - 3:31 am UTC
Hi Tom,
I wanted to ask if we can have a OS event trigger.
Example If file locations to be stored in Db and each time the file is moved the location be updated in the database how do i do it.
Will i require to do this trough Java or can i have a trigger for the same created in oracle.
Thanks,
Rahul.
instead of triggers
CHIN, September 29, 2008 - 12:36 pm UTC
Hi thanks for your valuable inputs on the Trigger.
I have a view on a table,suppose 1st time view query returns 100 records and 2nd time it returns 101 records Now I have to insert that new record into another table .......
Can I have a DDL Triggers for this .
thanx in Advance
September 29, 2008 - 12:43 pm UTC
this doesn't make sense.
Not sure what you are attempting at all.
instead of triggers
CHIN, September 30, 2008 - 8:55 am UTC
Ok let me put this way;--
If a new record has been added to the Table ...how to Identify that record and insert in some other table dynamically.
thanx in Advance
September 30, 2008 - 12:53 pm UTC
you would not do that during a select, you would do that during the insertion into the table itself.
Really - put it in a way that is infinitely more verbose. Tell us your goal here, what are you at the highest level trying to do.
Don't ask "how do I do x" inasmuch as tell us "i am trying to accomplish Y, what is the right way"
duplicate rows
A reader, August 30, 2010 - 12:34 pm UTC
Hi Tom,
I am trying to find if instead of trigger is of good use for our case .
This is for a datawarehouse environment .We have a big table which is partitioned on date . Every day we load this table (daily partition) with different flat file.
We want to enforce the data duplicate check .
Here is the following approach I analyzed :-
Made a Local Unique constraint on table. and after loading the table enabled the constraint with exception table clause .
Problem here is that duplicate data is still lying in main fact table and constraint remains in disabled state.
We dont want to abort the whole loading operation (direct path loading) instead we want to capture the duplicate records in different table and normal loading to proceed successfully eleminating the duplicates.
Pasting the sample script which i tried for our approach:-
create table test_object
as
select *
from user_objects;
ALTER TABLE test_object
add CONSTRAINT idx_id UNIQUE (object_id);
alter table test_object disable constraint idx_id;
create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
insert into test_object
select *
from test_object
where rownum < 2
alter table test_object enable constraint IDX_ID exceptions into exceptions;
select *
from exceptions;
Thanks And Regards,
Anupam
September 09, 2010 - 9:33 am UTC
... We want to enforce the data duplicate check . ...
smells like a unique constraint to me (before reading any further)
no, an instead of trigger would not make sense, you would not be able to direct path load and it would become slow by slow inserts (as the instead of trigger would have to insert a row at a time)
either:
a) use non-direct path loads and DML error logging.
http://www.google.com/search?q=site%3Atkyte.blogspot.com+dml+error+logging b)
1) use direct path with a disabled constraint
2) enable constraint with exceptions into
3) remove duplicates (you have their rowids, easy to
a) create table tmp as select * from table where rowid in (....)
b) delete from table where rowid in (....)
c) insert into table select distinct * from tmp;
4) enable constraint
probably - (a) is right - in this case, use a non-direct path load and dml error logging.
duplicate rows
anupam pandey, August 31, 2010 - 12:10 am UTC
I tried to use the error log but it does not seem to be working ..
SQL> create table test_object
2 as
3 select *
4 from user_objects
5 where object_id is not null ;
Table created
SQL>
SQL>
SQL> BEGIN
2 DBMS_ERRLOG.CREATE_ERROR_LOG(
3 dml_table_name => 'test_object',
4 err_log_table_name => 'test_object_error'
5 );
6 END;
7 /
PL/SQL procedure successfully completed
SQL>
SQL> ALTER TABLE test_object
2 add CONSTRAINT idx_id primary key (object_id);
Table altered
SQL>
SQL> insert /*+append */into test_object
2 select *
3 from test_object
4 where rownum < 2
5 LOG ERRORS INTO test_object_error ('INSERT..SELECT..RL=UNLIMITED')
6 REJECT LIMIT UNLIMITED;
insert /*+append */into test_object
select *
from test_object
where rownum < 2
LOG ERRORS INTO test_object_error ('INSERT..SELECT..RL=UNLIMITED')
REJECT LIMIT UNLIMITED
ORA-00001: unique constraint (IDX_ID) violated
SQL>
September 09, 2010 - 11:37 am UTC
see above...
Instead of trigger confusion
supriya, November 09, 2010 - 11:17 pm UTC
Hi Tom,
I read and came to know that the instead of trigger is only used for view(may be simple view or non updatable views)
.
I did something like this.
create table a(a number,b number);
create table b(c number,d number);
insert into a values(1,2);
insert into a values(3,4);
insert into b values(5,6);
create view v1 as select * from a,b;
and then create a trigger
create or replace trigger instead of insert on v1
begin
insert into a values(:new.a,:new.b);
insert into b values(:new.c,:new.d);
end;
as you said in your earlier post that instead of trigger will execute instead of dml operation and that is correct.
Here while i run
insert into v1 values(5,6,7,8);
it works and the two underlying table get inserted.
but if i will only insert like this
insert into v1(a,d) values(6,9);
if it works,is my logic is correct to insert data only to few columns keeping other null.please suggest me
please send some link where i can get instead of trigger with example to kill my confusion.
long live Tom,
Supriya
November 11, 2010 - 10:23 am UTC
I don't know what your confusion is - you yourself gave a working example????
ops$tkyte%ORA10GR2> create or replace trigger v1 instead of insert on v1
2 begin
3 insert into a values(:new.a,:new.b);
4 insert into b values(:new.c,:new.d);
5 end;
6 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into v1 values(5,6,7,8);
1 row created.
ops$tkyte%ORA10GR2> insert into v1(a,d) values(6,9);
1 row created.
ops$tkyte%ORA10GR2> select * from v1;
A B C D
---------- ---------- ---------- ----------
1 2 5 6
3 4 5 6
5 6 5 6
6 5 6
1 2 7 8
3 4 7 8
5 6 7 8
6 7 8
1 2 9
3 4 9
5 6 9
6 9
12 rows selected.
Not sure what the issue I'm supposed to address is???
Instead of trigger confusion
supriya, November 12, 2010 - 5:01 am UTC
Thanks tom,
I like the way you approach to every issue and the issue facing people.
I am glad that I got little knowledge about instead of trigger.
one doubt, for the not modifiable view we can create the instead of trigger and which will do the work for it.I agree with that.
But instead of creating the trigger we can call a procedure
which will do the correct thing.(the value we will insert we can pass to a procedure and inside we will write the corresponding insert statement for the underlying table ).
Please clarify this.
thanks,
Supriya.
November 12, 2010 - 9:02 am UTC
clarify what?
I always prefer a straight forward stored procedure that performs a well formed transaction in an easy to understand manner over a trigger to do magic - if you were asking for clarification on my stance regarding triggers.
Instead of trigger confusion
supriya, November 12, 2010 - 12:35 pm UTC
thanks tom
instead of trigger ? better methods ?
A reader, April 25, 2011 - 9:06 am UTC
We have a table that is range partitioned based on date column.Need to be able to further list subpartition based on another column .
To minimizee related code changes , we will make the existing table say t as a complex view.So ,all inserts
and updates via packages/procedures will actually happen against the complex view.
We would need to create instead of triggers so that underlyign tables are inserted/updated.
Subpartitioning will allow us to gather better stats at partition (segnment level).
Do you think the above idea is good use of instead of triggers ? are there better alternatives to the above approach ?
April 25, 2011 - 11:50 am UTC
Do you think the above idea is good use of instead of triggers ? are there
better alternatives to the above approach ?
No, I don't. I'm not a fan of triggers in general - and especially not in this fashion. If performance is anything you care about, you'll avoid this as well.
A better alternative to me would be to move all affected code into stored procedures and have it access the base tables - the stored procedures will make it very easy to change approaches later and gives you that awesome dependency mechanism so that you know WHO is using WHAT and how.
trigger
sam, April 25, 2011 - 5:25 pm UTC
Tom:
1) IS there really a performance difference between running a sql statement in the well formaed procedure for auditing
insert into audit_table(col1,col2)...values(v_col1,v_col2,..)
versus having the same sql statement in AFTER INSERT trigger.
Is there some hidden overhead for triggers only or not?
2) Also, is not the biggest factor for User here "Elapsed Time" to see what impact auditing has.
so basically if Elapsed time is the same for a procedure with auditing trigger versus procedure without auditing trigger, then there is no overhead really.
Users do not see Logical and physical I/O so I am no sture i Would be concerned about it. Should i be worried about the DB some extra work in terms of lofical I/O and physical I/O.
April 26, 2011 - 7:14 am UTC
1) sam, you know how to find out don't you - you've seen me do it (demonstrating the performance impact of A over B) time and time again. Give it a go, take the initiative, you know the approach.
2) no, it is not, but only because you are going to measure an isolated test - not truly real life.
If elapsed time was larger for one, you would have to assume that one of them used a ton more cpu than the other (it is not plausible that they would do massively different IO's would it be? Not really - so the only other really big foreseeable wait would be for.... cpu and running on the cpu)
Users DO see logical and phyiscal IO, they see it as a cause of the long times they wait for things. The more logical IO you do - the less scalable you are, the more CPU you'll need as the number of users goes up in general (that is, if it uses 1 unit of cpu time in single user mode, it might need 5 units of cpu time with 10 users to do the same thing - the amount of cpu needed by each individual goes up and up as you add individuals).
Think this one through sam, how do you affect response time for users? You pay attention to the things that would otherwise increase response time.
but, do the work, just do the work.
trigger
sam, April 26, 2011 - 9:43 am UTC
Tom:
Can you point me to the page where you demonstrated that having a SQL statement in SP is faster than having it in a trigger.
I did a simple test with EMP table and I did not se a difference.
April 26, 2011 - 2:40 pm UTC
share your work sam.
and we are talking INSTEAD OF triggers here sam, not just your run of the mill boring but bad triggers - we are talking instead of triggers.
here is my quick and dirty look at the situation:
ops$tkyte%ORA11GR2> create table t ( x int, y varchar2(200) );
Table created.
ops$tkyte%ORA11GR2> create or replace view v as select * from t;
View created.
ops$tkyte%ORA11GR2> create or replace trigger v instead of insert on v
2 begin
3 insert into t (x,y) values (:new.x,:new.y);
4 end;
5 /
Trigger created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA11GR2> insert into t select rownum, rpad('*',200,'*') from stage;
72151 rows created.
ops$tkyte%ORA11GR2> truncate table t;
Table truncated.
ops$tkyte%ORA11GR2> insert into v select rownum, rpad('*',200,'*') from stage;
72151 rows created.
insert into t select rownum, rpad('*',200,'*') from stage
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.32 0.58 1028 5364 19633 72151
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.32 0.58 1028 5364 19633 72151
insert into v select rownum, rpad('*',200,'*') from stage
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 1 0
Execute 1 4.19 5.12 0 1032 0 72151
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.19 5.13 0 1035 1 72151
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 100000
3 loop
4 insert into t (x,y) values (i,rpad('*',200,'*') );
5 insert into v (x,y) values (i,rpad('*',200,'*') );
6 end loop;
7 end;
8 /
INSERT INTO T (X,Y)
VALUES
(:B1 ,RPAD('*',200,'*') )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 6.79 7.95 0 6395 139400 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 6.79 7.95 0 6395 139400 100000
INSERT INTO V (X,Y)
VALUES
(:B1 ,RPAD('*',200,'*') )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 11.50 14.01 0 0 0 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 11.51 14.01 0 0 0 100000
It certainly looked measurable in that case didn't it?
When building a test case sam, you are looking for ways to break things, to demonstrate a point. Sometimes, testing with EMP, especially when you are talking about performance, isn't the way to go
VJ
vj, April 26, 2011 - 9:47 am UTC
Tom,
Plese review the below given scenario and advice if Instead of triggers should be used in this case.
We are getting data updates for 40 columns spread over 7 tables from an upstream source throug MQ message. Would it be a good idea to create
a view combining the underlying tables and then update the view?
Other option would be group the columns for the tables using a mapping table and issue 7 seperate update statements.
Thanks
VJ
April 26, 2011 - 2:41 pm UTC
I do not think so, no. I'm not a fan of triggers.
see above, you'll use a tad bit more CPU to accomplish something very straightforward.
trigger
sam, April 27, 2011 - 11:11 pm UTC
Tom:
Your example is a true piece of art.
so it demonstrates there are 5-7 seconds extra overhead when you inserted 70,000-100,000 records.
in my transactinal systems that never happens. I have tens or hundreds of rows into each table per day.
so like you always say IT DEPENDS on the systems and number and size of trnsactions. but apparently there is some overhead inserting with SQL in a trigger instead of SP..
April 28, 2011 - 7:43 am UTC
Sam
another way of looking at it is
o there is apparently about a 1309% performance hit for bulk operations
o there is apparently about a 170% performance hit for slow by slow operations
Now, if you wish to design systems that may grow from tiny into something larger with built-in worst practices - utilizing techniques you know won't scale. Great.
Now, if you wish to design systems that utilize techniques that make the system harder to maintain, understand... Great.
Now, if you wish to teach people a method that you know won't scale - a method that they will probably attempt to use to solve every problem they encounter in the future - not knowing it is a bad idea... Great.
You've become the problem, not the solution.
Your example is a true piece of art.
No it is not, it is the most basic, simple, trivial example I could think of. It took all of a few seconds to put together (it ran longer than I took to think it up). It is something you see on this site day in, day out, over and over. It is something you could have easily done yourself.
so like you always say IT DEPENDS on the systems and number and size of
trnsactions.
I say "it depends", but in this case - I'm not agreeing with "on the systems and the number/size of transactions". You don't know what the 'system' will be doing a year from now. Why booby trap it? Why make it obscure? Why teach future developers that inherit your code a bad practice? Why?
If there is a way to accomplish the goal without using a trigger - do it that way. If a trigger is the ONLY solution - go for it - but make darn sure you are in the top 10% of database developers (I said *database* developers, not developers in general - there is a distinction and it is important, a database developer actually understands transactions, concurrency, the database set of features and so on) before you do it - as many trigger implementations I review are fundamentally flawed and lead to logical data corruption.
triggers
sam, April 28, 2011 - 10:25 am UTC
Tom:
Excellent knowledge, advice and wisdom.
Thanks,
trigger
sam, April 28, 2011 - 10:01 pm UTC
Tom:
I was trying to repeat your trigger tests above.
What is the stage table? how did you get 71,000 rows.
do you copy all_users table many times to it.
also, can i use sql*plus GUI for windows. can i get tkprof output like yours on the windows client or not?
April 29, 2011 - 8:14 am UTC
create table stage as select * from all_objects;
also, can i use sql*plus GUI for windows. can i get tkprof output like yours on
the windows client or not?
that is a bit like saying
I use microsoft word. Can I balance my checkbook or not?
sqlplus does not generate tkprof reports. TKPROF generates tkprof reports. You'll need to use tkprof (of which there is a command line version only)
Test case for ORA-01031 on view with INSTEAD OF trigger
Michael, November 29, 2011 - 8:53 am UTC
Hi Tom,
here's a small test case for the ORA-01031 problem mentioned above while doing DML on a view with INSTEAD OF triggers:
I executes this as a normal user (not SYS etc.):
create or replace view v_test as
select 'X' x from dual;
view V_TEST created.
create or replace trigger v_test_t
instead of insert or update or delete on v_test
for each row
begin null; end;
/
TRIGGER v_test_t compiled
So a very simple not updateable view with an even simplier INSTEAD OF trigger that does ... nothing!
I thought that i could now execute successfully very INSERT/UPDATE/DELETE on this view... but nothing would happen.
But:
update v_test set x = 6;
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-01031: insufficient privileges
Hmm, of course, i don't have the UPDATE privilege on DUAL, but should that matter here?!?
Btw: i'm in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.
November 29, 2011 - 11:47 am UTC
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.It does not say anything about "except in the case of an instead of trigger...". So, it looks like they are computing the security constraints based on the view definition itself.
Strange things with privileges and INSTEAD OF trigger
Michael, November 30, 2011 - 3:44 am UTC
Hi Tom,
> So, it looks like they are computing the security constraints based on the view definition itself.
Yeah, this would be a proper (and deterministic) explanation.
But, i experimented a little bit more and found this:
drop view v_test;
view V_TEST dropped.
create or replace view v_test as
select 'X' x from (select * from dual);
view V_TEST created.
create or replace trigger v_test_t
instead of insert or update or delete on v_test
for each row
begin null; end;
/
TRIGGER v_test_t compiled
Note that i simply
encapsulated dual in the view into an inline view. The privileges check should work as before, i.e. preventing any insert, update, delete.
Surprisingly:
update v_test set x = 6;
1 rows updated.
insert into v_test(x) values('W');
1 rows inserted.
Now, inserts and updates work. Why? I don't know.
But it gets more strange...
delete v_test;
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-01031: insufficient privileges
delete v_test
where x = 'X';
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-01031: insufficient privileges
For what ever reason deletes do
not work.
Could that be a bug?
November 30, 2011 - 7:22 am UTC
if you try the insert before the trigger is created - you would get:
insert into v_test values ( 'Y' )
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
same with update. so the issue changed from one of security - to a "that operation isn't allowed".
You are not really inserting into DUAL in that particular case, you are inserting in a view that only exposes "expressions". If you used a view that exposed the dummy column from dual - you'd be back to ora-1031 for the insert and update.
So, it is the exposing of the expression ONLY (no real columns) that makes the view itself non-insertable/non-updateble - so the security check is trumped. And the instead of trigger 'fixed' te non-insert/update-ability - allowing you to insert/update this (inline) view.
If we do something else to the view - we can get even a "delete" to work - we have to find something that would make it impossible to delete into the view:
ops$tkyte%ORA11GR2> create or replace view v_test as
2 select 'X' x from (select * from dual order by 1);
View created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into v_test values ( 'Y' );
insert into v_test values ( 'Y' )
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> update v_test set x = 'Y';
update v_test set x = 'Y'
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from v_test;
delete from v_test
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace trigger v_test_t
2 instead of insert or update or delete on v_test
3 for each row
4 begin null; end;
5 /
Trigger created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into v_test values ( 'Y' );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> update v_test set x = 'Y';
1 row updated.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from v_test;
1 row deleted.
An order by does that...
A reader, November 30, 2011 - 4:10 pm UTC
it is because you put order by in the view, optimizer cannnot unnest the query? and thats why DML apply to the view rather than the underline table- dual?
December 01, 2011 - 7:46 am UTC
the view becomes totally "up-updateable" with the order by.
so it never even bothers to do a security check to see if you are allowed to do something that cannot be done...
instead of trigger confusion
Boris, October 19, 2017 - 2:10 am UTC
What's the difference between the below two versions of instead of triggers. Both the triggers yields the same output. Please note the difference is second instead trigger has FOR EACH ROW option
create or replace trigger inf_t_test
instead of insert on t_vw
begin
insert into t select * from t;
end;
create or replace trigger inf_t_test
instead of insert on t_vw
FOR EACH ROW
begin
insert into t select * from t;
end;
/
Does this mean the instead of triggers can be statement and row level?
October 19, 2017 - 2:21 am UTC
They are always row level ,eg
SQL> create table t as select * from user_objects;
Table created.
SQL> create or replace view t_vw as select * from t;
View created.
SQL> select count(*) from t;
COUNT(*)
----------
433
1 row selected.
SQL>
SQL> create or replace trigger inf_t_test
2 instead of insert on t_vw
3 begin
4 insert into t (object_name) values ('blah' );
5 end;
6 /
Trigger created.
SQL>
SQL> insert into t_vw select * from t where rownum <=10;
10 rows created.
SQL> select count(*) from t;
COUNT(*)
----------
443
1 row selected.
Excellent
Boris, October 19, 2017 - 11:02 pm UTC
Thanks Connor for the simple and crisp explanation.
I have few questions :
How many triggers can be created on a table as of 11g r2 and 12c?
How many instead of triggers can be created on a table as of 11gr2 and 12c?
Thanks in advance for your help
October 20, 2017 - 12:58 am UTC
As many as you like...
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace trigger trg1
2 before insert on t
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> create or replace trigger trg2
2 before insert on t
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL>
SQL> create or replace trigger trg3
2 before insert on t
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL>
SQL> create or replace trigger trg4
2 before insert on t
3 begin
4 null;
5 end;
6 /
Trigger created.
Excellent
Boris, October 21, 2017 - 1:19 am UTC
Thanks Conner.Thanks a lot for your service to oracle community.
October 21, 2017 - 1:34 am UTC
Thanks for the feedback. It is our pleasure.
Is this answer still correct today
Rainer, July 04, 2018 - 4:57 pm UTC
Hi Connor,
i have just a short question concerning the first answer.
I think it was Tom, he wrote "In 8.1.6, instead of triggers are available for all releases."
Is this still right today?
I searched the Web but was unable to find an exact answer.
I would like to know if instead of triggers can be used in SE greater than 8.1.6, for example in 11g or 12c?
Thanks in advance,
Rainer
July 05, 2018 - 4:25 am UTC
SQL> select banner from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> create or replace view emp_view as select * from emp;
View created.
SQL>
SQL> create or replace trigger trig_emp_view
2 instead of insert or update on emp_view
3 for each row
4 begin
5 Null ;
6 end ;
7 /
Trigger created.
SQL>