Home » SQL & PL/SQL » SQL & PL/SQL » To find out a column value is updated or not? (merged 6)
To find out a column value is updated or not? (merged 6) [message #384572] Wed, 04 February 2009 05:42 Go to next message
SRISREENATH
Messages: 13
Registered: January 2006
Junior Member
Hi

I have a table t1 and I am trying to update a record.
There is a audit column Modified present in t1. For evey updation the modified column need to be updated.
Is there any way to find out in mylast updation whether modified column is updated or not. If not I want to throw and error message through the before update trigger. Note that the same person can update the record also. Meaning I cannot give a condition like :old.modified=:new.modified throw an error.

example.
update t1 set sal=33434 where empid=100. Here I am not changing the 'modified' column. I expect an error here. How to manage this?
Re: To find out a column value is updated or not? [message #384574 is a reply to message #384572] Wed, 04 February 2009 05:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why not just write a trigger that sets the MODIFIED column on every update of the table?
To find out a column value is updated or not? [message #384580 is a reply to message #384572] Wed, 04 February 2009 06:00 Go to previous messageGo to next message
SRISREENATH
Messages: 13
Registered: January 2006
Junior Member
Hi

I have a table t1 and I am trying to update a record.
There is a audit column Modified present in t1. For evey updation the modified column need to be updated.
Is there any way to find out in mylast updation whether modified column is updated or not. If not I want to throw and error message through the before update trigger. Note that the same person can update the record also. Meaning I cannot give a condition like :old.modified=:new.modified throw an error.

example.
create table t1(a varchar2(10), b number, modified varchar2(20))

insert into t1 values('mat', 1, 'SCOTT');
commit;

I am updating this record as below

update t1 set a='cat' where b=1;

Here I am not passing the "modified" column in set clause. In this case I need it to throw an error

update t1 set a='cat', modified='SCOTT' where b=1;

Here I passed the same value as the old value of "MODIFIED" column. Now it should not throw any error.

Could any one please help me out in this scenario?

Thanks in advance
Re: To find out a column value is updated or not? [message #384587 is a reply to message #384580] Wed, 04 February 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't create a new (2 new) topic(s) for the same question.

Regards
Michel
How to find the value is passed for a column in an update statement? Merged [message #384590 is a reply to message #384572] Wed, 04 February 2009 06:25 Go to previous messageGo to next message
SRISREENATH
Messages: 13
Registered: January 2006
Junior Member
Hi Every body,

I have a strange requirement about the updation of a table.

I have a table TAB1
The structure of it is
a varchar2(10)
b number
modified_by varchar2(20)

I am inserting a record into this table as below

INSERT INTO TAB1 VALUES('SKY', 1, 'SCOTT');
COMMIT;

Now I am going to update the same record as below

UPDATE TAB1 SET A='SPACE' WHERE B=1;

Here I did not set any value for MODIFIED_BY column. In this case I want to throw an error.

If I update this recard as below

UPDATE TAB1 SET A='SPACE', MODIFIED_BY='SCOTT' WHERE B=1;

I gave MODIFIED_BY='SCOTT' which is same as the old value for this column. Now it should not throw error. That mean I can't give :OLD.MODIFIED_BY=:NEW.MODIFIED_BY in the trigger because in the second update statement I passed the existing vale.


Could any body please help me to achieve it?

Thanks in advance

Regards
Sreenath
Re: How to find the value is passed for a column in an update statement? [message #384592 is a reply to message #384590] Wed, 04 February 2009 06:30 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
OK, stop posting the same question multiple times. If you get a timeout message when you post a question. Wait, refresh the forum list and double check that your post was not added already BEFORE you simply add the same post another half dozen times.
Re: How to find the value is passed for a column in an update statement? Merged [message #384596 is a reply to message #384590] Wed, 04 February 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe I could help but I spend my time to merge and delette your duplicate posts.
Too bad!

Regards
Michel
Re: To find out a column value is updated or not? [message #384597 is a reply to message #384580] Wed, 04 February 2009 06:39 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi Srisreenath,

Can you please explain what is the difference in logic with the following statements you gave:
1.
Quote:

update t1 set a='cat' where b=1;
Here I am not passing the "modified" column in set clause.


&
2.
Quote:

update t1 set a='cat', modified='SCOTT' where b=1;
Here I passed the same value as the old value of "MODIFIED" column.


Basically, both will have the same impact by the sample data you have given. Can you explain the Business Requirement for the scenario?

Why can't you use a trigger to update the 'Modified' column irrelevant of whatever columns you supplied in the corresponding update statement?
[***Added: Oops!!! Didn't know this was already suggested by JRowbottom]
Have you tried the suggestion given by JRowbottom?

Regards,
Jo

[Updated on: Wed, 04 February 2009 06:44]

Report message to a moderator

Re: To find out a column value is updated or not? [message #384601 is a reply to message #384597] Wed, 04 February 2009 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only other way of doing it that I can think of would be to have a Row Level Before Insert trigger that fired when the Modified column was updated, and stored the Rowid in a collection in a package.
Then you'd need a Row Level After Update trigger that fired for all updates. This 2nd trigger would check that the rowid it is currently looking at had been logged by the first trigger, andhad therefor had it's Modified column updated.
Re: To find out a column value is updated or not? [message #384604 is a reply to message #384601] Wed, 04 February 2009 07:10 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Wed, 04 February 2009 13:02
The only other way of doing it that I can think of would be to have a Row Level Before Insert trigger that fired when the Modified column was updated, and stored the Rowid in a collection in a package.
Then you'd need a Row Level After Update trigger that fired for all updates. This 2nd trigger would check that the rowid it is currently looking at had been logged by the first trigger, andhad therefor had it's Modified column updated.


Had to re-read that a few times before I got my head round it.
Clever.
But liable to confuse other developers.

I have to wonder what the O/P is trying to achieve with this and iif there's a better (simpler) way of doing it.
Previous Topic: Effect of drop Table
Next Topic: Modifying custom types that are payloads on AQ
Goto Forum:
  


Current Time: Sat Dec 10 09:25:10 CST 2016

Total time taken to generate the page: 0.10192 seconds