In the question I asked, I used the when clause. I am just asking is there anything other way than when clause. (Because when clauses does the required and takes time)

for example
update emp set sal=sal+10
in this update statement there is no user/status column. So I dont want to do any checking.
update emp set sal=sal+10,staus=nvl(status,'COMPLETE');
In this case I do want to do the checking.
With when clause even though the body does not get fired, it takes timing for checking here are stats I found.
No of Records Without Trigger With Trigger Trigger with When Clause
323336 Run 1 16 Seconds 51 Seconds 28 seconds
323336 Run 2 16 Seconds 52 Seconds 28 seconds
323336 Run 3 20 Seconds 248 Seconds

Thanks
Koshal

What happens when you do --

before update of user_id or update of stts_id or delete on

What I mean is we should have some sort of checking at the statement level itself. What is happening in the when clause is it is checking for each row.

It should be able to distinguish between

1. upate emp set sal=sal+10
and
2. update emp set status=nvl(status,'COMPLETE')

Looking at the the first statement we know that there is going to be no change to "status". I would be nice had oracle able to identify this at statement level and eliminate row level checking.

Thanks a lot tom.

Thanks
Koshal

Hi,
you can create a trigger for actions to specific columns:
SQL> set serveroutput on size 10000
SQL> 
SQL> create table trigmat (nome varchar2(30), cognome varchar2(30))
  2  /

Table created

SQL> 
SQL> create or replace trigger trigmat_update_nome
  2  before update or delete of cognome on trigmat
  3  for each row
  4  
  5  begin
  6    dbms_output.put_line('cognome modified, old: '||:old.cognome||', new: '||:new.cognome);
  7  end;
  8  /

Trigger created

SQL> 
SQL> insert into  trigmat values ('john','smith');

1 row inserted

SQL> insert into  trigmat values ('albert','red');

1 row inserted

SQL> commit;

Commit complete

SQL> 
SQL> update trigmat set nome = upper(nome) where nome = 'john';

1 row updated

SQL> commit;

Commit complete

SQL> 

Trigger didn't fire....

SQL> update trigmat set cognome = upper(cognome) where cognome = 'smith';
cognome modified, old: smith, new: SMITH

1 row updated

SQL> commit;

Commit complete

SQL> 
 Now, on update of cognome field, trigegr fired...

SQL> delete trigmat where cognome ='red';
cognome modified, old: red, new: 

1 row deleted

SQL> commit;

Commit complete

SQL> 

Also on delete.

Regards
Matteo
 

Tom,
one question on this. With any PLSQL code (which trigger is) there is a context switch - correct? Is it also true for WHEN clause in trigger? Is "WHEN" condition checked by PLSQL engine (not sure I'm correct with terminology) or Oracle is smart enough to do it in SQL context and in case condition fails escapes context switch?
thank you very much!

This is what I looking for !

Thanks
Koshal

Tom,

Is it possible to use "Query" in WHEN condition of the TRIGGER clause with OR condition:


This trigger (AFTER INSERT OR UPDATE ROW) should fire only on the following condition :

when :NEW.<COLUMN> IS NOT NULL OR (SELECT ... FROM OTHER TABLE WHERE <CONDITION> IS TRUE) ;

DOES Trigger support this construct in where condition.

Oracle 10.2.0.1

Thanks

TOm:

just curious on why you advised to use "when" caluse to filter the trigger update when you can do

CREATE OR REPLACE TRIGGER test
BEFORE UPDATE OF USER_ID ON test
FOR EACH ROW


Is not this more effective that using WHEN clause. I did not realize you can select columns in the update statement.


Figure 17-3

http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c18trigs.htm

Tom:

I got an requirement to not allow the users to update a column (say the column x) in table 'T' (even the owner should not).

create table t(x number,y date);
insert into t (x,y) values(1,sysdate);
commit;

If i need to make this table such that no modifications to be done. I can make it a read-only in 11g or enable a check constraint like below.

alter table t read only;
alter table t add constraint t_chk check(1=0) enable novalidate;

How can i defined a check such that any user (even the owner) can insert the records into column 'x' but no update should be done on column 'x'? Is that something can be achieved without using Triggers?

Tom,

We have an 3rd party application that has triggers (nearly six for insert or update action) for deriving some logic
( i know you hate triggers & saw that in your slides "Triggers Trickery" )

Now we are about to develope a new module for this application. Where we are going to update some tables (say T, where Trigger exists) based on PK
which we read from a file.

We dont want (logic inside) the Trigger to be executed for this new-module (as per business use-case), so i took the following steps.

1) Define a new application context APP_CTX
2) set dbms_session.SET_CONTEXT('APP_CTX','FLAG','Y') as very first statement in new Package about to develope.
3) Redefine the trigger as " WHEN (SYS_CONTEXT('MY_CTX','FLAG') IS NULL) "

In this way, I am not disturbing the exising application and silently supress the Trigger execution for this new development.
a) This new module will be executed during business offhours (say 8pm -4am) monthly once.
b) we will establish a connection, run this package once in a month and disconnect. we dont do any more stuff in that same session/connection.

Do you see/feel any flaw in this logic (or) any better way of doing this?

If you need i can show my script, which i used for testing.

does the 3rd party application owner care you are doing this? Many 3rd party software owners would say "you are no longer supported if you do that"

We have informed them and they are fine with this. I would like to know your thoughts.

Tom,
This seems like an enhancement request the vendor should be making

I agree, they need "UPDATE OF col1, col2" trigger but what they have is "UPDATE" trigger. We have requested them to make code change but they are not willing to make changes.

The code which we are about to develope is a new application, which will be integrated with them and we are about to do an update like this

update t set col3 = 'xxx' where pk = :b1

Which will be impacted by their triggers, so we decided to bypass them in such a way that they will not be impacted by us.

Tom

I remember reading a document in this site about capturing audit information on users that perform a SELECT on restricted columns, but do not remember how I came across. Do you have an example?

If C was a restricted column containing sensitive data and some one ran the following query,

SELECT A,B,C
FROM TAB1;

then I need to capture the following information:
1. Who ran the query
2. The table (optional)
3. The Column (optional)

Is 2 and 3 available from somehere as opposed to hard coding it?

Is the query itself stored somewhere?

Thanks