Results 1 to 3 of 3

Thread: Record stamps

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Record stamps

    Is there a way to tell when a value has been supplied in an UPDATE statement?

    Code:
    SQL> create table mytable (f1 varchar2(10), f2 varchar2(10));
    
    Table created.
    
    SQL> insert into mytable values ('field1', 'field2');
    
    1 row created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace trigger mytable_bt
      2  before update
      3  on mytable
      4  for each row
      5  begin
      6    dbms_output.put_line('the old value for f1 is: '||:OLD.f1);
      7    dbms_output.put_line('the new value for f1 is: '||:NEW.f1);
      8    dbms_output.put_line('the old value for f2 is: '||:OLD.f2);
      9    dbms_output.put_line('the new value for f2 is: '||:NEW.f2);
     10* end;
    SQL> /
    
    Trigger created.
    
    SQL> set serveroutput on
    SQL> ed
    Wrote file afiedt.buf
    
      1  update mytable
      2* set f1 = 'f1 new val'
    SQL> /
    the old value for f1 is: field1
    the new value for f1 is: f1 new val
    the old value for f2 is: field2
    the new value for f2 is: field2
    
    1 row updated.
    I was hoping that the value for :NEW.f2 would be NULL when there's no value explicitly passed into it, although I now see why that wouldn't work so well. Is there any other way to check and see if a value was passed in, though?

    -Chuck

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Are you sure you want to fire the trigger even if the old and new values are the same ? because if not, you could easily get around that by evaluating ld.f2 <> :new.f2. Otherwise, you can use updating.
    Code:
    SQL> create or replace trigger mytable_bt
      2  before update
      3  on mytable
      4  for each row
      5  begin
      6    if updating( 'f2' )
      7    then
      8       dbms_output.put_line('the old value for f1 is: '||:OLD.f1);
      9       dbms_output.put_line('the new value for f1 is: '||:NEW.f1);
     10       dbms_output.put_line('the old value for f2 is: '||:OLD.f2);
     11       dbms_output.put_line('the new value for f2 is: '||:NEW.f2);
     12    end if;
     13  end;
     14  /
    
    Trigger created.
    
    SQL> update mytable
      2  set f1 = 'f1 new val'
      3  /
    
    1 row updated.
    
    SQL> update mytable
      2  set f2 = 'trigger it'
      3  /
    the old value for f1 is: f1 new val
    the new value for f1 is: f1 new val
    the old value for f2 is: field2
    the new value for f2 is: trigger it
    
    1 row updated.
    
    SQL>

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    We've got some applications which actually require user logins to the database, while others use a shared login. We're trying to set up our triggers so that if a username and datetime are passed in, we'll set the CREATE_ID, CREATE_DATE, MOD_ID, MOD_DATE using that, otherwise we'll revert to picking up the database user and sysdate for those values.

    This works perfectly, thanks,
    -Chuck

    Code:
    CREATE OR REPLACE TRIGGER SCHEMA.TABLE_BRT
     BEFORE INSERT OR UPDATE OR DELETE
     ON TABLE FOR EACH ROW
    
    BEGIN
    
      /* MAINTAINED STAMP COLUMNS */  
      /*******************************************/
      IF INSERTING THEN
      
        IF :NEW.CREATE_DATE IS NULL THEN
          :NEW.CREATE_DATE := SYSDATE;
        END IF; 
        IF :NEW.CREATE_ID IS NULL THEN
          :NEW.CREATE_ID := USER;
        END IF;   
        IF :NEW.MOD_DATE IS NULL THEN
          :NEW.MOD_DATE := SYSDATE;
        END IF; 
        IF :NEW.MOD_ID IS NULL THEN
          :NEW.MOD_ID := USER;
        END IF;
     
      END IF;
      /*******************************************/
      IF UPDATING THEN
      
        IF NOT UPDATING('MOD_DATE') THEN
          :NEW.MOD_DATE := SYSDATE;
        END IF;
        IF NOT UPDATING('MOD_ID')THEN
          :NEW.MOD_ID := USER;
        END IF;
    
      END IF;
      /*******************************************/
      
    END;

Posting Permissions

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