Tom,

Another related question, you said:
"That entire referenced thread was the proof that you cannot be sure the trigger
is fired only once for each row! So, confused by your request there."

What if we have a trigger for each row and write rowid along with some unique transaction id, by using a sequence, to a logging table and have after trigger for statement update that logging table rows where the for each row trigger was fired more than once. For example, if the triggeer for each row is being used to send out e-mail, by using the above approach we can make sure that actual e-mail was sent out only once. Does this work, or are there any better options?

Thanks.

Hi Tom,

How to find the trigger order? Is there any way we can order trigger execution OR Oracle has a predefined order which we can predict? Thanks a lot for your help.(something sp_settriggerorder in SQL Server)

CREATE TABLE dtemp
(A Varchar2(50));

CREATE TABLE dtemp2
(A Number,
B Varchar2(50));

CREATE OR REPLACE PAKAGE ABC IS
A INTEGER :=1;
END

set serveroutput on
begin
 abc.a:=1;
end;
/

CREATE OR REPLACE TRIGGER scott.before_row_insert1
BEFORE INSERT
ON scott.dtemp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
 insert into dtemp2 values(abc.a,'before row insert1');
 abc.a := abc.a + 1;
End;
/

CREATE OR REPLACE TRIGGER scott.before_row_insert2
BEFORE INSERT
ON scott.dtemp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
 insert into dtemp2 values(abc.a,'before row insert2');
 abc.a := abc.a + 1;
End;
/

CREATE OR REPLACE TRIGGER scott.after_row_insert1
AFTER INSERT
ON scott.dtemp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
 insert into dtemp2 values(abc.a,'after row insert1');
 abc.a := abc.a + 1;
End;
/

CREATE OR REPLACE TRIGGER scott.after_row_insert2
AFTER INSERT
ON scott.dtemp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
 insert into dtemp2 values(abc.a,'after row insert2');
 abc.a := abc.a + 1;
End;
/

SQL>insert into dtemp values('mygod');




 

Can we modify the old values in a row using before triggers?
I mean :old.col1=old.col2;
I want to update my old column values with old column value from another column in the same table?

Sorry It was new value. Sorry for the trouble.

I have 2 'before insert' triggers. They need to be run in certain order. I read this link and you suggested to use some package do it. Do you have a small example of how to do it ? I will appreciate it.

Thanks

We have a requirement to populate history tables.

The parent table already has a Before Insert or Update trigger to insert the PK sequence value and the create date,last update date columns.

Do I create an AFTER INSERT OR UPDATE TRIGGER to load data
into History table
or combine the logic in the before Insert or Update trigger.
I was thinking technically creating history records should be done after the parent table is populated so its best to do that in an AFTER trigger.But this will make 2 triggers firing.

How much is it of a performance gain if any to have one Before Insert update trigger rather than having
2 triggers one Before I U and another After I U.

What are your thoughts ?

Thanx for your response.
I consider old as well as new values.

:New values for Inserts/Updates

:OLD values for Deletes.

This way the history table is complete , I dont need to query the parent table for the latest record.

So what Trigger would be in this case. ?
Still put it in along with B IU trg or put the history in A IU.

Thanx Understood your point. One Question
"since someone can come along next year, add a new
BEFORE FOR EACH ROW trigger"

I would add any new Trigger logic to the existing BEFORE Trigger.
What would be a situation when we would need more than one
BEFORE I U Trigger.

Thanx

Tom,
I need to write a trigger which will update a sysdate on value column in a row whenever there is an update
e.g

table1 (id number, date date, value varchar(10))

I am just trying to track whenever value column changes I should update the date column to sysdate and only that row should update 'id' column is unique

create or replace triggers abc
after update or insert on table1
begin
date = sysdate;
end abc;

I know the above is wrong but please advise

Thankyou very much.

It does not mean any values changed just because the update trigger fires. ie 'update table1 set col1 = col1' - check that the old and new values are different - preferably in the when clause.

ORA-04082: NEW or OLD references not allowed in table level triggers

Oracle 8.1.7.4

SQL> create table t(i varchar2(10));

Table created.

SQL> drop sequence s;                                                       

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> create or replace trigger trg before insert or update on t for each row
  2  begin
  3  select cast(s.nextval as varchar2(10)) into :new.i from dual;
  4  end;
  5  /

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER TRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/34     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         . ) @ %

SQL> select  cast(s.nextval as varchar2(10)) from dual;

CAST(S.NEX
----------
1

Why is the CAST function not working in the trigger?

Thanks 

Tom, question on Before insert or update or delete trigger.

I am trying to write a trigger on "dept" table,whenever there is a successful DML on dept,
it has to write a record in Log table.

Problem i am facing is, trigger is getting fired even if that DML is a failure.

eg:- I am trying to insert a varchar value into a deptno [number], just to see if trigger fires ,
and it does, even after going to when others section in exception

Why is that so and can I force trigger to fire only on success?

CREATE TABLE DEPT
(
DEPTNO NUMBER(5),
DNAME VARCHAR2(20 BYTE),
LOC VARCHAR2(20 BYTE)
)

CREATE TABLE LOG_TABLE
(
MESSAGE VARCHAR2(2000 BYTE),
TIME_STMP DATE DEFAULT sysdate
)

--Trigger on dept table:

CREATE OR REPLACE TRIGGER DEPT_TRG
BEFORE DELETE OR INSERT OR UPDATE
ON DEPT
FOR EACH ROW
begin
insert into log_table(message) values('DML on DEPT Table ');
end;
/

--function for DML on dept
create or replace function dml_dept
return integer
is
begin

insert into dept(deptno) values('testing trigger behaviour');
exception
when others then
return -1;
end;

Doesn't Oracle know it's gone to exception and should not fire any triggers ?

Can you please explain your reply:
<<Quote>>
but don't forget, the statement can still rollback later - eg: it can still "fail" later on.
<</Quote>>

Thanks,

Tom, if i want to use only Before trigger [to capture :old values], and do not want trigger to fire in case of exception, Is there any way to do it?

Thanks,

I thought
Here is some sample which shows that after row statement fires event though the integrity constraint check should have failed first.
Me and my colleagues are confused with the definition found at
</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#sthref3288 <code>

drop table emp;
drop table dept;

-- Create table
create table DEPT
(
ID NUMBER not null,
name VARCHAR2(100)
);

alter table DEPT add primary key (ID);
;


create table EMP
(
ID NUMBER not null,
NAME VARCHAR2(100),
DEPT_ID NUMBER
);

alter table EMP
add primary key (ID);

alter table EMP
add constraint EMP_DEPT_FK foreign key (DEPT_ID)
references DEPT (ID);

create or replace trigger EMP_AFT_INS_ROW_TRG after insert on emp for each row
declare
n number;
begin
select 1 into n from dept where dept.id = :new.dept_id;
exception
when no_data_found then
raise_application_error(-20001, 'No such department');
end;
/

insert into dept(id, name) values (1,'First department');
commit;
insert into emp (id, name, dept_id) values (101, 'First employee', 1);
commit;

rem here I expect a foreign key violation message instead of 'No such department' application error
insert into emp (id, name, dept_id) values (102, 'Second employee', 2);
commit;

> because tables are allowed to violate their
> constraints during the DML, think about this:
>
> update emp set empno = empno+1;

That means that the server process has to maintain a list of the modified blocks, to validate them when the stmt ends [and for deferred constraints - when the tx ends]. Correct?

If yes, keeping the list in memory doesn't look very scalable, so ... will the process revisit the rollback segment perhaps ?

TIA (as always)

see the link "oracle redo generation", that'll give you some insight 

Here is the exact link to the pk=pk+1 topic.

</code> http://esemrick.blogspot.com/2006/02/ora-000015-unique-constraint-not.html <code>

Hi Tom
Kindly help me out

1) Trigger T1 is created by user U1 against a table T1 and also the user U1 has granted full permission to the user U2. Now, when the user U2 try to insert/update/delete from table T1, Should Trigger fire or not?

2)vise versa of the above, User U2 (has all permission for the table t1 belongs to user U1) has created trigger T2.
Now,When user u1 try to insert/update/delete from table T1, Should Trigger fire or not?

Regards
R.Krishna Kumar

Hi Tom
Kindly help me out

1) Trigger T1 is created by user U1 against a table T1 and also the user U1 has granted full permission to the user U2. Now, when the user U2 try to insert/update/delete from table T1, Should Trigger fire or not?

2)vise versa of the above, User U2 (has all permission for the table t1 belongs to user U1) has created trigger T2.
Now,When user u1 try to insert/update/delete from table T1, Should Trigger fire or not?

Regards
R.Krishna Kumar

Of course triggers will fire. They are associated to the table not to the user:

SQL> create user u1 identified by u1;

User created.

SQL> grant create session, create table, create trigger to u1;

Grant succeeded.

SQL>  alter user u1 quota unlimited on ts_d01;

User altered.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session, create any trigger to u2;

Grant succeeded.

SQL> connect u1/u1
Connected.
U1> create table t1 (col number);

Table created.

U1> create trigger trg1 before insert on t1 
  2  begin
  3    dbms_output.put_line('TRG1');
  4  end;
  5  /

Trigger created.

U1> grant all on t1 to u2;

Grant succeeded.

SQL> connect u2/u2
Connected.
U2> create trigger trg2 before insert on u1.t1
  2  begin
  3    dbms_output.put_line('TRG2');
  4  end;
  5  /

Trigger created.

U2> insert into u1.t1 values (0);
TRG2
TRG1

1 row created.

U2> connect u1/u1
Connected.
U1> insert into t1 values (1);
TRG2
TRG1

1 row created.

Now some issues for you:
1/ To be able to create a trigger in another schema you gave to U2 the CREATE ANY TRIGGER privilege which a VERY BIG security hole
2/ When you export U2 schema, what happens to U2.TRG2?
3/ When you export U1 schema, what happens to U2.TRG2?
4/ When you reimport the previous, what happens to your application logic?

Regards
Michel
 

<quote>
constraints technically are validated AFTER the statement executes - it is not
really defined whether a trigger will or will not fire (and if you have logic
that depends on such intricate ordering, we have a really big logic problem)
</quote>

So the documentation which states that for each row first
before row triggers fire, then constraints are checked and the after row triggers fire is incorrect? Is this the conclustion?

</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#sthref3289 <code>

Thanks,
Mihail

Self-correction:

>> because tables are allowed to violate their
>> constraints during the DML, think about this:
>>
>> update emp set empno = empno+1;
>
> That means that the server process has to maintain a list of the modified
> blocks, to validate them when the stmt ends [and for deferred constraints -
> when the tx ends]. Correct?

I no longer think that a list of modified blocks is necessary; all it takes it's just a counter of violations (index collisions) maintained as the DML executes.

Thanks to Tom for the two links to the Eric S. Emrick blog (very interesting) and thank to "A Reader" for the "exact link to the pk=pk+1 topic", also to Eric's blog :)

Tom,
it's very helpful for me. But I have one more related Question on triggers. I am updating one table with after I/U/D for each row... But that is being done from the front end. So I need to track which columns are being updated. As I am fetching the columns from USER_TAB_COLUMNS. I am able to get the columns with:
declare
cursor c_cur is select column_name from user_tab_columns where table_name = 'EMP_MAS';
begin
for i in c_cur loop
variable := i.column_name;
end;
--- But here I want the column value also...?
How can I get those values ..
if I use
variable := ':new.'||i.column_name;
means I am getting ':NEW.COL_NAME' not the value.
and I tried with
execute immediate ('select :new.'||i.column_name||' from dual) into variable2;
But it is giving error like 'ORA-01008 not all variables bound'.

Hi,

Too good.

Hi Tom,
I need your suggestion in following scenario.
There are two databases( Say 'A' and 'B'). In database 'A' There is a table 'T'. I'm having a trigger on T which is firing on UPDATE+INSERT+DELETE events. This trigger is inserting the records in a table 'T2' in database 'A' as well as table 'T4' & 'T5' . These 'T4' & 'T5' tables are in database 'B' therefore a db_link is used in for inserting the data in T4 & T5 tables.
As a DB_LINK is used, it is delaying the process. My manager wants me to improove the process. I suggested him to go MATERIALIZED VIEW. but according to him it is not a realtime solution for this process. Is there any means by which we can improove the performance. ( Even I used AUTONOMOUS TRANSAXTION in this trigger but performance has not improoved as desired)

[quote]not really, it is just that "it is more complex than can be simply stated as a sequence of
operations"

a simple check constraint - sure

but a constraint that needs to look "across rows" like a primary key or foreign key - they are
conceptually done AFTERWARDS.

let me just say:

if you are dependent on the ordering of firing of same type triggers, whether a constraint is fired
before or after a specific trigger - we have a problem, there is something wrong with the logic
there. [/quote]

Hi Tom. First of all, excuse me if I don't write correctly in english language, but I'm from Spain and my English is far from being perfect.

a) So, you are saying that doc is wrong. I'm disappointed with this. It was very easy to write just your paragraph ("but a constraint that needs to look "across rows" [...]) in the documentation.

b) You say "there is something wrong with the logic
there". But how can I add a complex check involving cross-table checking statements if there is a foreing key between those two tables?
I mean: I have table A (with column "type"), and tables B, C and D with foreign key to A table. A row in A table can be referenced by multiple rows in B, C or D tables. But I want to check that all values in B, C and D table are referencing values in A table that fulfills a certain condition (in B table, that corresponding rows in A have type = 1, in C table, that corresponding rows in A have type = 2, and so on...).
Do you mean that there is a model design fault in that scenario if I have that requeriments?

Thank you very much for your help.

Hi Tim

Suppose, I have a table having country currency combinations with a primary currency.
The below is a sample data.

Country Currency Primary_Currency
US USD Y
US USN N
US USS N

And, I have a before update trigger on the above table for each row (using autonomous transaction to avoid mutating error) to check whether the country has any primary currency.

I am giving the below update.

Update Country_Currency_Ref
set is_primary_currency = 'Y'
where (country_code = 'US' and currency_code = 'USN');

And the trigger is working fine and giving the correct message that it cannot be updated as there is already one primary currency against that country

Now I update the data as below (No primary currency)

Country Currency Primary_Currency
US USD N
US USN N
US USS N

and try to update the data with the below condition

Update Country_Currency_Ref
set is_primary_currency = 'Y'
where (country_code = 'US' and currency_code <> 'USD');

In this case, the trigger is failing and updating other two records.

Why is this behaviour? And how can we over come the same?

Thanks and Regards
Raghav

create unique index only_one_can_be_primary on country_currency_ref
( case when is_primary_currency = 'Y' then country_code end );

Hi Tom,

Thank you very much for your feedback and valuable suggestion. It worked like a miracle. Till now, we dont know that an index can be created depending on some conditions like case structure you have shown. It is really a great thing that can be used avoiding triggers. Thank you once again for your timely help.

Thanks and Regards
Raghav

I also didn't realize either that you could use a function based index like to enforce a business rule. I always thought that was where nasty triggers stepped in.

Fantastic. Now I am going to spam all our other dba's to make sure they understand as well.

Joe

I have daily_log table and structure is as under.
Name Null? Type
------------------------------- -------- ----
CO_CODE VARCHAR2(2)
CARD_NO VARCHAR2(6)
INOUT VARCHAR2(3)
SYS_DATE DATE
SYS_TIME DATE

and my data are
CARD_no INOu SYS_DATE TIME
------ --- --------- -----
000378 OUT 10-MAY-08 08:40
000378 IN 10-MAY-08 08:41
000501 OUT 12-MAY-08 14:30
000501 IN 12-MAY-08 16:30
000333 OUT 14-MAY-08 17:19
000333 IN 14-MAY-08 17:35
000607 OUT 14-MAY-08 10:01
000607 IN 14-MAY-08 10:35
000542 OUT 13-MAY-08 10:44
000542 IN 13-MAY-08 10:50

My result should be as under.

CARD_no INOu SYS_DATE TIME
------ --- --------- -----
000378 OUT 10-MAY-08 08:40
000378 IN 10-MAY-08 08:41 ----> 00.01
000501 OUT 12-MAY-08 14:30
000501 IN 12-MAY-08 16:30 ----> 02.00
000333 OUT 14-MAY-08 17:19
000333 IN 14-MAY-08 17:35 ----> 00.16
000607 OUT 14-MAY-08 10:01
000607 IN 14-MAY-08 10:35 ----> 00.34
000542 OUT 13-MAY-08 10:44
000542 IN 13-MAY-08 10:50 ----> 00.06

please send me in detail about to solve my problem.
i shall be highly appreciated you.

Thanks

Abid

hi tom,

what is kyd factor & where did it use?

How about "Know Your Data"? Sounds like someone that runs this site might say... :-)

Hi Tom

Here is another question in avoiding triggers. If suppose, we want to enforce data integrity in a column which refers to the same table, is that possible to have a conditional index (function based index) to achieve the result? Here is my example.

agency
code code equivalent_orr
ORR 1
ORR 2
ORR 3
ORR 4
ORR 5
CBS 2+ 3
CBS 2- 1
CBS 3+ 4
CBS 3- 1
CBS 4+ 5

I need to map equivalent values of ORR to CBS rows.

I have tried using exists clause as I need to check from the list of values of ORR as below.

CREATE INDEX REF1
ON CODE_REFERENCE
(Case When EXISTS (SELECT CODE FROM CODE_REFERENCE
WHERE AGENCY_CODE = 'ORR')
Then EQUIVALENT_ORR End
)

The index is created. But when I try to access the table, the session is getting disconnected. How can we achieve this situation without using triggers. Please help me in this regard.

Thanks & regards
Raghav

Hi Tom,

Thanks for your reply. I was trying (may be wrong trying same medicine for different deceases) to avoid trigger / autonomous transaction in similar manner as to my previous posting of country currency ref.

Our requirement is to enforce a conditional RI referring to the same table. What ever the value being updated in equivalent_orr has to exist in the same table where the agency_code is ORR. In this case we are accessing the same table for query and update so there is a chance of mutating error.

How can we achieve this without using triggers? Your help is well appreciated.

Thanks and Regards
Raghav

Just an FYI,
In oracle 11G the create trigger command may now include a FOLLOWS option to force the order of the triggers firing at the same timing point (BEFORE STATEMENT, BEFORE ROW, AFTER ROW, AFTER STATEMENT);

Hi Tom,

Thanks for your reply once again. I understand from your reply that we can still enforce RI on the table without using triggers. I still need a clarification - as the table is having a primary key on agency code and code (combinational primary key), can we enforce RI on a part of primary key i.e, code? Can you please show us how can we achieve the result in the above example?

Thanks and Regards
Raghav

Hi Tom

As you said it correctly, if we have two sets of columns - one set for primary key and another set for reference we can enforce RI. But, we have a set of columns agency code and code (2 columns) as primary key, and only one column 'ORR_Equivalent' in which we want to accept the values of code where the agency code is 'ORR'.

2. Suppose if we have another column 'ORR' the values in that column will 'ORR' throughout the table which is redundant, as we know we are going to map only ORR equivalent values in the third column.

Also, you have mentioned that there is no concept of conditional RI. So, in such scenerio how can we get the result by avoiding triggers.

Thanks and Regards
Raghav

Tom

Sorry for the confusion happened through my previous posting. I am re pharasing my query. As per the below example we want either NULL or the values (code) equal to ORR (agency_code) in the equivalent_orr.

Primary key(2 columns) new column
----------- --------- -----------------
agency code code equivalent_orr
----------- --------- -----------------
ORR 1
ORR 2
ORR 3
ORR 4
ORR 5
CBS 1 3
CBS 2 1
CBS 3 4
CBS 4 1
CBS 5 5

As you mentioned -
"there is no such thing as "conditional RI", you either
a) have it
b) or not "

In this scenerio, other than the model changes (i.e., adding one more column to make it a set of two columns to refer to foreign key of same table) is there any possibility of obtaining the result without using triggers.

Thanks and Regards
Raghav

Hi Tom,

Thank you once again for your reply. I understood from your response that, in the given scenario, without model changes / without a trigger (may be a clumsy process) it is not possible to enforce RI.

I have another scenario, where again I want to avoid the use of a trigger. There is a Transaction table in which one of the column values are to be part of any one of the ten other tables. I took some (similar) raw example below.

Table A
---------
apples
mangoes
oranges

Table B
---------
Cars
Buses
Trucks

Table C
---------
Houses
Signle Storied Buildings
Multi Storied Buildings

and so on (many different tables).

Table T
-----------
Sno Reference table Value
---- --------------- -------
1 A mangoes
2 C Houses
3 B Trucks
---- --------------- --------
---- --------------- --------
---- --------------- --------


Other than the supposed model like below (which is not possible at this moment) having two columns and having an RI on two columns

Table category
------------------
Fruits mangoes
Fruits apples
Fruits oranges
Vehicles Cars
Vehicles Buses
---------- --------
---------- --------
---------- --------

Is there any way to achieve the result, i.e, enforcing an RI? Please let me know the best possible way for the same.

Thanks and Regards
Raghav

Tom

Which is faster for record inserts?

1. NOT NULL constraints with DEFFAULT values.

AUDIT_CREATED_ID VARCHAR2(30) DEFAULT USER NOT NULL,
AUDIT_CREATED_DATE DATE DEFAULT SYSDATE NOT NULL,
AUDIT_LAST_UPDATED_ID VARCHAR2(30) DEFAULT USER NOT NULL,
AUDIT_LAST_UPDATED_DATE DATE DEFAULT SYSDATE NOT NULL


or

2. use Triggers to populate the default values for columns and the columns are defined as follows, without NOT NULL constraints)

AUDIT_CREATED_ID VARCHAR2(30),
AUDIT_CREATED_DATE DATE,
AUDIT_LAST_UPDATED_ID VARCHAR2(30),
AUDIT_LAST_UPDATED_DATE DATE,

or

3. use Triggers to populate default values for columns and the columns are defined as follows, with NOT NULL constraints)

AUDIT_CREATED_ID VARCHAR2(30) NOT NULL,
AUDIT_CREATED_DATE DATE NOT NULL,
AUDIT_LAST_UPDATED_ID VARCHAR2(30) NOT NULL,
AUDIT_LAST_UPDATED_DATE DATE NOT NULL,


Thanks

Dear TOM,
Requirement is to create a unique index on the 5 columns( name columns as shown in the index creation below) when ever the data_set_type='UNQ'. I have used the following conditional index to achieve the same. How ever, the select queries written go for full table scan. Is there a way to change the queries for unique scan of the index / or a better way to create the conditional unique index. Your help on this is highly appreciated.

SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> CREATE TABLE PERSON_PROFILE
  2  (
  3    PERSON_ENTITY_ID    NUMBER NOT NULL,
  4    NAME_PREFIX         VARCHAR2(96 CHAR),
  5    FIRST_NAME          VARCHAR2(384 CHAR),
  6    MIDDLE_NAME         VARCHAR2(384 CHAR),
  7    LAST_NAME           VARCHAR2(384 CHAR),
  8    NAME_SUFFIX         VARCHAR2(96 CHAR),
  9    DATA_SET_TYPE  VARCHAR2(32 CHAR)
 10  )
 11  /

Table created.

SQL> create unique index idx_pp_uk on person_profile
  2  (CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "FIRST_NAME" ELSE NULL END ,
  3  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "MIDDLE_NAME" ELSE NULL END ,
  4  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "LAST_NAME" ELSE NULL END ,
  5  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "NAME_PREFIX" ELSE NULL END ,
  6  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "NAME_SUFFIX" ELSE NULL END )
  7  /

Index created.

SQL> begin
  2  for i in 1..3000
  3  loop
  4  insert into person_profile values ( i, 'NP', 'FN', 'MN', 'LN', 'NS', 'NUNQ') ;end loop;
  5  commit;
  6  for i in 3001..6000
  7  loop
  8  insert into person_profile values ( i, 'NP'||i, 'FN', 'MN', 'LN', 'NS', 'UNQ') ;
  9  end loop;
 10  commit;
 11  end ;
 12  /

PL/SQL procedure successfully completed.

SQL> analyze index idx_pp_uk validate structure;

Index analyzed.

SQL> 
SQL> select name, lf_rows from index_stats;

NAME                              LF_ROWS
------------------------------ ----------
IDX_PP_UK                            3000                                                        

SQL> 
SQL> analyze index idx_pp_uk compute statistics;

Index analyzed.

SQL> alter session set query_rewrite_enabled = true;

Session altered.

SQL> 
SQL> alter session set query_rewrite_integrity = trusted;

Session altered.

SQL> explain plan for
  2  select *  from person_profile a
  3  where
  4  "FIRST_NAME"    = CASE WHEN "DATA_SET_TYPE" = 'UNQ' THEN :first_name     ELSE NULL END and
  5  "MIDDLE_NAME"   = CASE WHEN "DATA_SET_TYPE" =  'UNQ' THEN :middle_name    ELSE NULL END and
  6  "LAST_NAME"     = CASE WHEN "DATA_SET_TYPE" =  'UNQ' THEN :last_name      ELSE NULL END and
  7  "NAME_PREFIX"   = CASE WHEN "DATA_SET_TYPE" =  'UNQ' THEN :name_prefix    ELSE NULL END and
  8  "NAME_SUFFIX"   = CASE WHEN "DATA_SET_TYPE" =  'UNQ' THEN :name_suffix    ELSE NULL END
  9  
SQL> 
SQL>  SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4257669096

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |  2089 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSON_PROFILE |     1 |  2089 |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("FIRST_NAME"=CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN :FIRST_NAME
              ELSE NULL END  AND "MIDDLE_NAME"=CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN
              :MIDDLE_NAME ELSE NULL END  AND "LAST_NAME"=CASE "DATA_SET_TYPE" WHEN 'UNQ'
              THEN :LAST_NAME ELSE NULL END  AND "NAME_PREFIX"=CASE "DATA_SET_TYPE" WHEN
              'UNQ' THEN :NAME_PREFIX ELSE NULL END  AND "NAME_SUFFIX"=CASE
              "DATA_SET_TYPE" WHEN 'UNQ' THEN :NAME_SUFFIX ELSE NULL END )

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

Thanks a lot Tom. This worked. Really appreciate your immediate response.

SQL>  explain plan for
  2  select *  from person_profile a
  3  where
  4  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN FIRST_NAME ELSE NULL END = :first_name and
  5  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN Middle_NAME ELSE NULL END = :middle_name and
  6  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN LAST_NAME ELSE NULL END = :last_name and
  7  CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN NAME_prefix ELSE NULL END = :name_prefix and
  8* CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN NAME_SUFFIX ELSE NULL END = :name_suffix
SQL> /

Explained.

SQL> 
SQL> 
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1146264087

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |  2089 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON_PROFILE |     1 |  2089 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_PP_UK      |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access(CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "FIRST_NAME" ELSE NULL END
              =:FIRST_NAME AND CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "MIDDLE_NAME" ELSE NULL END
              =:MIDDLE_NAME AND CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "LAST_NAME" ELSE NULL END
              =:LAST_NAME AND CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "NAME_PREFIX" ELSE NULL END
              =:NAME_PREFIX AND CASE "DATA_SET_TYPE" WHEN 'UNQ' THEN "NAME_SUFFIX" ELSE NULL END
              =:NAME_SUFFIX)

Note
-----

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement

23 rows selected.

Here is my trigger

create or replace trigger orderscomplete
before update of order_completed on orderheader
for each row
when(orderline.orderline_fufilled = 'Y')
begin
:new.order_completed :(SYSDATE);
end;
/

can you please advise I am using two tables 'Orderheader' and 'Orderline' when the Orderline_fufilled = 'Y' it will update the order_completed in orderheader to systdate

Excellent!

Hi Tom,

##############################
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

###########################################
I have done exactlly what "maverick from US dec-14th-2005" on this page done and did not get anything written into
the log_table (which happened as I expected).




CREATE TABLE DEPT
(
DEPTNO NUMBER(5),
DNAME VARCHAR2(20 BYTE),
LOC VARCHAR2(20 BYTE)
) ;

CREATE TABLE LOG_TABLE
(
MESSAGE VARCHAR2(2000 BYTE),
TIME_STMP DATE DEFAULT sysdate
);

CREATE OR REPLACE TRIGGER DEPT_TRG
BEFORE DELETE OR INSERT OR UPDATE
ON DEPT
FOR EACH ROW
begin
insert into log_table(message) values('DML on DEPT Table ');
dbms_output.put_line('fired');
end;
/

create or replace function dml_dept
return integer
is
begin

insert into dept(deptno) values('testing trigger behaviour');
exception
when others then
return -1;
end;
/

declare
a number;
begin
a:=dml_dept;
end;
/


####### marveric written
I am trying to write a trigger on "dept" table,whenever there is a successful DML on dept,
it has to write a record in Log table.

Problem i am facing is, trigger is getting fired even if that DML is a failure.

eg:- I am trying to insert a varchar value into a deptno [number], just to see if trigger fires ,
and it does, even after going to when others section in exception

##################You replied
the "exception" is not happening until it happens - the BEFORE trigger you have fires well before
the first byte of data is touched.


################ My question is

I tested the same and found that the before row trigger is not at all fired.
But marveric found the trigger got fired ( may be some lower version fo oracle ,but now aware).

And I am confused , was it wrong what you stated or you stated as the version behaves at that
point of time.(need your view).

Thanks as always,
Biswaranjan.

Thanks Tom for you reply and one thing I want to say
that you are too fast and too good( I was seeing your reply to all question for today one by one). People will not really believe that ask tom is a single person(Thomas kyte) but I can say "ask tom"="unbelievable" with out any doubt.
great.

thanks as always,
Biswaranjan.

Hi Tom,

Just above you wrote
############
I didn't state anything about his example other than
"if you want to do something AFTER the row has been validated, do it AFTER the row has been validated"

#################

Just below to marveric one reader asked(dont know whether that is marveric or other)
##############
Doesn't Oracle know it's gone to exception and should not fire any triggers ?

Can you please explain your reply:
<<Quote>>
but don't forget, the statement can still rollback later - eg: it can still "fail" later on.
<</Quote>>

thanks,


Followup December 14, 2005 - 3pm Central time zone:

the "exception" is not happening until it happens - the BEFORE trigger you have fires well before
the first byte of data is touched.


update t set x = 5;
rollback;


your trigger can still fire, but never have fired against any data that actually gets changed.
#####################

I was just saying about the "the "exception" is not happening until it happens - the BEFORE trigger you have fires well before
the first byte of data is touched.".
Was it really true that time ?

I just want to clear my doubt abuot the line you wrote.

Excuse me if I said anything wrong to you.

thanks,
Biswaranja.

Hi Tom,
:)

I was mixed up those two users question (marveric's and the reader's) so got confused with your follwup comments.
Now I understood.

By the way I was aware of this "we might fire your before trigger millions of times and still ultimately roll back because the last row fails."

All is your help :) .


Thanks as always,
Biswaranjan.

Hi..TOM,

I need one query from you, we have one table(ex.KSK) and it had trigger applied for getting user's information like logon,logoff,and any changes in a table(ex.KSK) and etc. The trigger(ex.KSK_TRIG) is writes information when each user accessing table(KSK) everytime.. actually, this table and trigger created by DBA's.
So that my question is I want to see the details of latest or current record of each user's information except SYS and SYSMAN users ????

Hi, Tom

One table(Example.abc) is existed in our database and it contains large records of information like username, hostname, logontime, logoff_time, action_name. Number of users accessing (abc) table when they can do any actions the trigger writes above information in that table. So my question is I need only current records of each users except sys. Please give me a query for getting current records of each users..

There are now mechanisms to set the order of executing triggers.