Home » Applications » Oracle Fusion Apps & E-Business Suite » update table
update table [message #322754] Mon, 26 May 2008 02:41 Go to next message
hany_marawan
Messages: 198
Registered: April 2005
Location: Cairo - Egypt
Senior Member
Hello,
In the HR module there's special information.
I want some of these informations the user can not delete or update the record but can add a new record.
I tried the personalization, but it not worked.
I also tried creating database trigger (before update) on this table but also it not worked.
How can I do this.

Thanks
Re: update table [message #322762 is a reply to message #322754] Mon, 26 May 2008 03:10 Go to previous messageGo to next message
hany_marawan
Messages: 198
Registered: April 2005
Location: Cairo - Egypt
Senior Member
Hello,
Thanks first.
This table is common for many informations, so the user can use it in many ways, but I want him can not update or delete the data in some cases.
I hope it clear now.

Thanks
Re: update table [message #322763 is a reply to message #322754] Mon, 26 May 2008 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I tried the personalization, but it not worked.
I don't know anything about "personalization", so I can not comment it.
Quote:
I also tried creating database trigger (before update) on this table but also it not worked.
I know something about database triggers, so - if you failed to restrict UPDATE and DELETE actions - you must have done something wrong. Because, as far as I can tell, database triggers will successfully do that job (if correctly written).
Re: update table [message #322767 is a reply to message #322754] Mon, 26 May 2008 03:24 Go to previous messageGo to next message
hany_marawan
Messages: 198
Registered: April 2005
Location: Cairo - Egypt
Senior Member
I wrote this code

begin
if :old.code!=:new.code then
null;
end if;
end;

Is this code right and doing what I want.
Re: update table [message #322777 is a reply to message #322767] Mon, 26 May 2008 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is an anonymous PL/SQL block, not a database trigger. Why did you choose to post a partial code and not the complete CREATE TRIGGER script?
Re: update table [message #322781 is a reply to message #322754] Mon, 26 May 2008 04:07 Go to previous messageGo to next message
hany_marawan
Messages: 198
Registered: April 2005
Location: Cairo - Egypt
Senior Member
Sir,
I sent the code written in the trriger after building it.
But if you want the complete script of building this trigger,
here's

CREATE OR REPLACE TRIGGER test_update
BEFORE
UPDATE
ON test
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
:new.code!=:old.code then
null;
end if;
end;
/
Re: update table [message #322782 is a reply to message #322754] Mon, 26 May 2008 04:07 Go to previous messageGo to next message
hany_marawan
Messages: 198
Registered: April 2005
Location: Cairo - Egypt
Senior Member
CREATE OR REPLACE TRIGGER test_update
BEFORE
UPDATE
ON test
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if :new.code!=:old.code then
null;
end if;
end;
/
Re: update table [message #322826 is a reply to message #322782] Mon, 26 May 2008 06:24 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Database trigger works fine, if correctly written:
SQL> create table test (code number, name varchar2(20));

Table created.

SQL> insert into test (code, name) values (100, 'Littlefoot');

1 row created.
SQL> create or replace trigger test_update
  2    before update on test
  3    for each row
  4  begin
  5    if :new.code <> :old.code then
  6       raise_application_error(-20001, 'Error: new code <> old code');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> select * from test;

      CODE NAME
---------- --------------------
       100 Littlefoot
SQL> update test set
  2    name = 'Bigfoot';

1 row updated.

SQL> update test set
  2    code = 200,
  3    name = 'Middlefoot';
update test set
       *
ERROR at line 1:
ORA-20001: Error: new code <> old code
ORA-06512: at "SCOTT.TEST_UPDATE", line 3
ORA-04088: error during execution of trigger 'SCOTT.TEST_UPDATE'


SQL>

I believe you noticed that I've used RAISE_APPLICATION_ERROR, as opposed to NULL in your code. NULL means "don't do anything" so - why did you expect trigger to do "something" if you told it to do "nothing"?
Re: update table [message #328898 is a reply to message #322762] Mon, 23 June 2008 04:56 Go to previous message
cjack
Messages: 22
Registered: June 2008
Junior Member
I guess Form Personalization can work with your requirement.

May I have you setup detail?
Previous Topic: Oracle Inventory - material description
Next Topic: Customers
Goto Forum:
  


Current Time: Wed Jul 03 13:06:05 CDT 2024