Results 1 to 7 of 7

Thread: Lock Update

  1. #1
    Join Date
    Jul 2004
    Posts
    102

    Unanswered: Lock Update

    Hi all,

    I want to prevent update a field within a table after insert a value in the field.

    Let me explain..................

    I have a table name xx contains two field (name, age)

    select * from xx;

    Name age
    ----- ----
    Momin 32
    Shaheen 31

    user can change the name but cannot change age after insert.

    Can i make it either form end or DB trigger ?

    your advise needed. If this is not possible please inform me.

    Thanks in advance.
    Working Together...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If the user is not the owner then you can restrict his privileges like this:

    GRANT UPDATE (Name) ON xx TO user_or_role_name;

    If no one (not even the table owner) should be allowed to update it then you can create a trigger:

    Code:
    CREATE TRIGGER xx_trg 
    BEFORE UPDATE OF age ON xx
    FOR EACH ROW
    WHEN (new.age <> old.age)
    BEGIN
      RAISE_APPLICATION_ERROR(-20001,'Cannot update age');
    END;

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I have also done something similiar in a trigger ... set it back to what it was

    CREATE TRIGGER xx_trg
    BEFORE UPDATE OF age ON xx
    FOR EACH ROW
    WHEN (new.age <> old.age)
    BEGIN
    age := ld.age;
    END;

    Gregg

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    BTW, I hope this example is fictitious: otherwise after a while you will have a table where all the AGE values are wrong, and no one can correct them

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    AMEN to that!!!

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This is a perfect example of GIGO (Garbage in- Garbage out), you or someone else will make mistakes in the ago. If the otehre users are runnin in a differenct schema, go with the grant, but do not use the trigger. You are just asking for problems.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2004
    Posts
    102
    Thanks you all.

    Many many thanks for your help.
    Working Together...

Posting Permissions

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