Results 1 to 10 of 10

Thread: Lock a field.

  1. #1
    Join Date
    Jul 2004
    Posts
    102

    Unanswered: Lock a field.

    Dear ALL,


    I have a table XYZ with the following fields.

    aa varchar2
    bb date
    cc number

    I want to restrict field bb if it is not null. If the bb field have some value then no one can change or delete that value.

    I want to manage this by db trigger. Please help me.

    Thanks in advance for your help.
    Working Together...

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I want to restrict field bb if it is not null.
    That would be the NOT NULL constraint - you don't have to code it.


    If the bb field have some value then no one can change or delete that value.
    Trigger is something I'd use as well. Did you try to write one? How does it look like?

  3. #3
    Join Date
    Jul 2004
    Posts
    102
    That would be the NOT NULL constraint - you don't have to code it.
    The field may be null or not null but when it is not null for some rows then it is unchangable....


    Trigger is something I'd use as well. Did you try to write one? How does it look like?
    Nope i can't write single line coz, i still dont understand how it will be.
    Working Together...

  4. #4
    Join Date
    Jul 2004
    Posts
    102
    thanks all. I made it by myself;
    Working Together...

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    [EDIT: OK then, you did it! Good! I didn't see your last message and had to do something in the meantime, so ... sorry.]


    You should really read some documentation. Coding triggers is what you need.

    Here's a table and a trigger:
    Code:
    SQL> create table test
      2  (id number,
      3   ename varchar2(20),
      4   edate date
      5  );
    
    Table created.
    
    SQL> create or replace trigger trg_test
      2    before update of ename on test
      3    for each row
      4  begin
      5    if :old.ename is not null then
      6       raise_application_error(-20001, 'Ename can not be modified');
      7    end if;
      8  end;
      9  /
    
    Trigger created.
    Let's insert two records: one with ENAME column filled with a value, and the other one is empty:
    Code:
    SQL> insert into test (id, ename, edate) values (1, 'Little', sysdate);
    
    1 row created.
    
    SQL> insert into test (id, ename, edate) values (2, null, sysdate);
    
    1 row created.
    Now, testing: let's update the first record (as its ENAME column is not empty, it shouldn't be allowed):
    Code:
    SQL> update test set ename = 'Foot' where id = 1;
    update test set ename = 'Foot' where id = 1
           *
    ERROR at line 1:
    ORA-20001: Ename can not be modified
    ORA-06512: at "SCOTT.TRG_TEST", line 3
    ORA-04088: error during execution of trigger 'SCOTT.TRG_TEST'
    The other one should be OK (as its ENAME is empty):
    Code:
    SQL> update test set ename = 'Foot' where id = 2;
    
    1 row updated.

  6. #6
    Join Date
    Jul 2004
    Posts
    102
    Thanks Master,

    i made this waya..

    CREATE OR REPLACE TRIGGER DB_TRIGG_test_update_bb
    BEFORE UPDATE OF bb ON test
    FOR EACH ROW
    WHEN (
    old.bb is not null
    )
    BEGIN
    RAISE_APPLICATION_ERROR(-20001,'Cannot update or delete value');
    END;
    Working Together...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why raise an error. simply

    Code:
    CREATE OR REPLACE TRIGGER DB_TRIGG_test_update_bb
    BEFORE UPDATE OF bb ON test
    FOR EACH ROW
    WHEN (
    old.bb is not null
          )
    BEGIN
        :new.bb := :old.bb;
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2004
    Posts
    102
    Quote Originally Posted by beilstwh View Post
    Why raise an error. simply

    Code:
    CREATE OR REPLACE TRIGGER DB_TRIGG_test_update_bb
    BEFORE UPDATE OF bb ON test
    FOR EACH ROW
    WHEN (
    old.bb is not null
          )
    BEGIN
        :new.bb := :old.bb;
    END;

    Coz, I just want to restrict the change and i don't know how through any message by DB trigger.
    Working Together...

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That will keep it the same no matter what update is used once it is set without an annoying message popping out.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Right, but - if that's done silently - user might find himself amused. Gee, I changed it in my form, re-executed query and nothing happened! I must be crazy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •