update table [message #322754] |
Mon, 26 May 2008 02:41 |
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 |
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 #322781 is a reply to message #322754] |
Mon, 26 May 2008 04:07 |
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 |
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 |
|
Littlefoot
Messages: 21823 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"?
|
|
|
|