Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Unanswered: Tigger to insure value for modified_by column

    Does anybody have a way to insure that a modified_by column value is furnished any time a user updates a table row?

    The column is nullable because it is not required for insertions.

    Some sort of "BEFORE UPDATE" trigger?

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does anybody have a way to insure that a modified_by column value is furnished any time a user updates a table row?

    Is this a 2-tier or 3-tier application.
    Does application utilize connection pooling to connect to the DB?

    Post actual & operational PL/SQL code for a function that returns the correct content which should populate the modified_by column.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by anacedent View Post
    Is this a 2-tier or 3-tier application.
    Does application utilize connection pooling to connect to the DB?
    Huh?
    The database is agnostic as far as who connects and how they connect. All sorts of applications may be connecting concurrently now or in the future. I just need to insure that for all updates to any row in table x, that set modified_by='whatever' is included, else reject the update.

    Post actual & operational PL/SQL code for a function that returns the correct content which should populate the modified_by column.
    Huh?
    The value must be set in the sql update command, no matter by what means the db is accessed.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The value must be set in the sql update command, no matter by what means the db is accessed.
    Then proceed to do so.
    ensure the UPDATE statement includes SET MODIFIED_BY=USER
    Last edited by anacedent; 09-16-10 at 14:16.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by anacedent View Post
    >The value must be set in the sql update command, no matter by what means the db is accessed.
    Then proceed to do so.
    ensure the UPDATE statement includes SET MODIFIED_BY=USER
    I guess I have not made myself clear.

    My aim is to enforce a business rule at the database level. The idea is to protect the database.

    The applications performing the updates are written elsewhere by others.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    You can target update situations in general in a single trigger

    Code:
    create or replace trigger my_table_bt
    before update
    on my_table
    for each row
    begin
      if :NEW.MODIFIED_BY is NULL then
        raise_application_error(-20500,'MY_TABLE.MODIFIED_BY cannot by NULL for UPDATE');
      end if;
    end my_table_bt;
    You can target that one field in the "BEFORE UPDATE" clause
    Code:
    create or replace trigger my_table_bt
    before update of MODIFIED_BY
    on my_table
    for each row
    begin
      if :NEW.MODIFIED_BY is NULL then
        raise_application_error(-20500,'MY_TABLE.MODIFIED_BY cannot by NULL for UPDATE');
      end if;
    end my_table_bt;
    Or, we pull everything into a single trigger, and just test for state using the built-in variables of "INSERTING", "UPDATING" and "DELETING". We take that route, since 1) we didn't want a bunch of triggers per table and 2) if you need triggers to fire in a particular order, in 10gR1, you cannot control that (don't know about later versions):

    Code:
    create or replace trigger my_table_bt
    before insert or update or delete
    on my_table
    for each row
    begin
      if  updating and :NEW.MODIFIED_BY is NULL then
        raise_application_error(-20500,'MY_TABLE.MODIFIED_BY cannot by NULL for UPDATE');
      end if;
    end my_table_bt;

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The problem with only check if modified_by is not null is that anyone can set that column to any value. Do the following

    create or replace trigger my_table_bt
    Code:
    before insert or update 
    on my_table for each row begin 
      :NEW.MODIFIED_BY := user;
    end my_table_bt;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    It all depends. In our environment, the application authenticates the user and controls database object privileges through programming. Each user, however, ultimately logs into the database with the same account (within each application).

    So USER, in our case, would just put a generic account name in that column.

    --=cf

  9. #9
    Join Date
    Sep 2010
    Posts
    6
    The trigger solutions do not work.

    Or, rather, they only work as long as the modified_by is currently null in the database.

    Because if modified_by is not null in the db and no value is set in the update statement then :NEW.MODIFIED_BY contains the database value, not a null value.

    You can test this very easily:

    set serveroutput on

    create table foo(id number(10), modified_by varchar2(100));

    create or replace trigger my_table_bt before update on foo for each row
    begin

    dbms_output.put_line('>' || :NEW.MODIFIED_BY);

    if :NEW.MODIFIED_BY is NULL then
    raise_application_error(-20500,'MY_TABLE.MODIFIED_BY cannot by NULL for UPDATE');
    end if;
    end my_table_bt;
    /

    insert into foo (id,modified_by) values (1,'test1');

    update foo set id=2;

    drop table foo;

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    Good point

    Code:
    create or replace trigger my_table_bt before update on foo for each row begin
    
      if NOT UPDATING("MODIFIED BY") then
        raise_application_error(-20500,'You must supply a value for MY_TABLE.MODIFIED_BY during an UPDATE');
      end if;
    
      if :NEW.MODIFIED_BY is NULL then
        raise_application_error(-20500,'MY_TABLE.MODIFIED_BY cannot by NULL for UPDATE');
      end if;
    
    end my_table_bt;
    I've also seen the code written as follows, which falls into the same trap you're referring to:

    Code:
    if NVL(:OLD.MODIFIED_BY, ' ') = NVL(:NEW.MODIFIED_BY, ' ') then
    The code breaks since it assumes that the same person would insert then update, or update then update ...

  11. #11
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by chuck_forbes View Post
    Good point
    I've been using Oracle since 5.1b and I just discovered this situation with triggers yesterday!

    D*mn!

    But I can't really fault the Oracle Docs, because they say that NEW and OLD reflects what is in the DB before and after the update.

    Solution may be to create a separate child table for the modified_by whose rows would be inserted by the update trigger on the parent. The parent would then insert null into its own modified_by in all cases.

  12. #12
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by chuck_forbes View Post
    Code:
      if NOT UPDATING("MODIFIED BY") then
        raise_application_error(-20500,'You must supply a value for MY_TABLE.MODIFIED_BY during an UPDATE');
      end if;
    ...
    Oh yes, I see your answer now, I just had to replace the double quotes with single quotes around "MODIFIED_BY".

    Thanks a million.

  13. #13
    Join Date
    Dec 2003
    Posts
    1,074
    Single quotes ... yep. I get a "D" for sloppy posting today

Posting Permissions

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