OraFAQ Forum: SQL & PL/SQL » Need to identify columns not referenced in UPDATE SET clause.

Home » SQL & PL/SQL » SQL & PL/SQL » Need to identify columns not referenced in UPDATE SET clause.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to identify columns not referenced in UPDATE SET clause. [message #634980]

Wed, 18 March 2015 09:55

gajini
Messages: 262
Registered: January 2006

Senior Member

Hi All,

The :NEW variable is equal to :OLD variable if the column is not referenced in the UPDATE SET clause.
For example, consider this table,

create table emp(emp_id number,update_date date,update_by varchar2(200));

Now I want to have an before update trigger on this table and in this trigger, if the columns update_date and update_by are not referenced
in the UPDATE SET clause, for example:

update emp set emp_id=x where emp_id=y;

then inside trigger I need to identify the columns update_date and update_by are not used in UPDATE and need to consider :NEW.update_date
and :NEW.update_by as NULL values. If there is any such a possibility available to find the column not referenced in UPDATE SET, please let me know.

Thanks You Very Much...

Lalit : added code tags

[Updated on: Thu, 19 March 2015 10:35] by Moderator

Report message to a moderator

Re: Need to identify columns not referenced in UPDATE SET clause. [message #634986 is a reply to message #634980]

Wed, 18 March 2015 11:03

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No.
There is no difference between not give them or given them with the current values.
But does it really matter?

Report message to a moderator

Re: Need to identify columns not referenced in UPDATE SET clause. [message #635004 is a reply to message #634986]

Wed, 18 March 2015 17:51

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

This is all in the manuals. Read them.

18:49:12 SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.17
18:49:22 SQL>
18:49:22 SQL> create table t1 (a integer,b integer,c integer);

Table created.

Elapsed: 00:00:00.04
18:49:22 SQL> create or replace trigger bur_t1
18:49:30   2  before update on t1
18:49:30   3  for each row
18:49:30   4  begin
18:49:30   5     if updating('a') then raise_application_error(-20999,'updating a'); end if;
18:49:30   6     if updating('b') then raise_application_error(-20999,'updating b'); end if;
18:49:30   7     if updating('c') then raise_application_error(-20999,'updating c'); end if;
18:49:30   8  end;
18:49:30   9  /

Trigger created.

Elapsed: 00:00:00.04
18:49:30 SQL> insert into t1 values (null,null,null);

1 row created.

Elapsed: 00:00:00.01
18:49:34 SQL> update t1 set a = 1;
update t1 set a = 1
       *
ERROR at line 1:
ORA-20999: updating a
ORA-06512: at "KEVIN.BUR_T1", line 2
ORA-04088: error during execution of trigger 'KEVIN.BUR_T1'


Elapsed: 00:00:00.03
18:49:38 SQL> update t1 set b = 2;
update t1 set b = 2
       *
ERROR at line 1:
ORA-20999: updating b
ORA-06512: at "KEVIN.BUR_T1", line 3
ORA-04088: error during execution of trigger 'KEVIN.BUR_T1'


Elapsed: 00:00:00.01
18:49:38 SQL> update t1 set c = 3;
update t1 set c = 3
       *
ERROR at line 1:
ORA-20999: updating c
ORA-06512: at "KEVIN.BUR_T1", line 4
ORA-04088: error during execution of trigger 'KEVIN.BUR_T1'


Elapsed: 00:00:00.01

Report message to a moderator

Re: Need to identify columns not referenced in UPDATE SET clause. [message #635059 is a reply to message #634980]

Thu, 19 March 2015 10:31

gajini
Messages: 262
Registered: January 2006

Senior Member

Thanks Kevin for the right solution.
It helped me to implement what I needed to achieve when the columns not included in UPDATE SET clause.

Thanks all for your help.

Report message to a moderator

Re: Need to identify columns not referenced in UPDATE SET clause. [message #635060 is a reply to message #634980]

Thu, 19 March 2015 11:05

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

gajini wrote on Wed, 18 March 2015 09:55

Hi All,

The :NEW variable is equal to :OLD variable if the column is not referenced in the UPDATE SET clause.
For example, consider this table,
create table emp(emp_id number,update_date date,update_by varchar2(200));
Now I want to have an before update trigger on this table and in this trigger, if the columns update_date and update_by are not referenced
in the UPDATE SET clause, for example:
update emp set emp_id=x where emp_id=y;
then inside trigger I need to identify the columns update_date and update_by are not used in UPDATE and need to consider :NEW.update_date
and :NEW.update_by as NULL values. If there is any such a possibility available to find the column not referenced in UPDATE SET, please let me know.

Thanks You Very Much...

Lalit : added code tags

Why does it matter? We do this all the time, in almost every table we have. The CORRECT value of 'update_date' is always the current value of sysdate at the time of the update. And update by is always the current user at the time of the update. We just have the trigger identify that for itself and supply it regardless of what the app may or may not have supplied.

Report message to a moderator

Previous Topic:	Result in same rows
Next Topic:	need query based on the below requirement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:48:16 CDT 2024