Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001
    Location
    chennai
    Posts
    21

    Unanswered:

    hi
    in SQL Server there is an Update(Column) clause which detects a change only on that column. may be it will work in oracle.
    try
    K.Ramakrishnan
    Chennai

  2. #2
    Join Date
    Jun 2001
    Posts
    2
    well, this is an old post and you probably have figured it out already, but for kicks here is one way to do it.

    -- my test table (column Z is the 'irrelevant' column)

    SQL> create table wuh (x number, y number, z number);

    Table created.

    SQL> insert into wuh values (0, 1, 2);

    1 row created.

    SQL> commit;

    Commit complete.

    -- the trigger:

    SQL> create or replace trigger blahtrg before update on wuh for each row
    2 declare
    3 real_change number := 0;
    4 begin
    5 for colnames in (select column_name
    6 from user_tab_columns
    7 where table_name = 'WUH'
    8 and column_name != 'Z')
    9 loop
    10 if updating(colnames.column_name) then
    11 real_change := 1;
    12 exit;
    13 end if;
    14 end loop;
    15 if real_change = 1 then
    16 dbms_output.put_line('there was a real change');
    17 else
    18 dbms_output.put_line('only Z was updated.');
    19 end if;
    20 end;
    21 /

    Trigger created.

    -- test it:

    SQL> set serveroutput on
    SQL> update wuh set y = 0 where x = 0;
    there was a real change

    1 row updated.

    SQL> update wuh set z = 0 where x = 0;
    only Z was updated.

    1 row updated.

    -- HTH.

Posting Permissions

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