To find out a column value is updated or not? (merged 6) [message #384572] |
Wed, 04 February 2009 05:42 |
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?
|
|
|
|
To find out a column value is updated or not? [message #384580 is a reply to message #384572] |
Wed, 04 February 2009 06:00 |
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
|
|
|
|
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 |
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: To find out a column value is updated or not? [message #384597 is a reply to message #384580] |
Wed, 04 February 2009 06:39 |
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 |
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 |
cookiemonster
Messages: 13952 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.
|
|
|