Thread: WHERE clause?

I'm trying to create a log table to track changes to a table..  so I've
created a set of rules that look something like this:

CREATE RULE "m_log_change" AS ON UPDATE TO "machine"
do ( INSERT INTO machine_log (who, date, machnum, col, newval)   SELECT getpgusername(), 'now'::text, old.machnum,
'host',new.host   WHERE (new.host != old.host) or (old.host IS NOT NULL and new.host IS NULL) or    (old.host IS NULL
andnew.host IS NOT NULL);
 
... more similar INSERT statements with different WHERE clauses..
);

My question is:

Is this the best way to code the WHERE?  What I'm really after is "did the
value change?".  I found that the tests to see if one-but-not-both of the
values is NULL are required to identify times when the value changed to or
from NULL.  

Seems like a lot of extra work...


Jim Rowan            DCSI            DCE/DFS/Sysadmin Consulting
jmr@computing.com                                            (512) 374-1143

Jim Rowan <jmr@computing.com> writes:
>     WHERE (new.host != old.host) or 
>     (old.host IS NOT NULL and new.host IS NULL) or
>         (old.host IS NULL and new.host IS NOT NULL);

> Is this the best way to code the WHERE?  What I'm really after is "did the
> value change?".  I found that the tests to see if one-but-not-both of the
> values is NULL are required to identify times when the value changed to or
> from NULL.  

Yes, because any ordinary operator applied to NULL will produce NULL,
which WHERE interprets as FALSE.  The only operations that actually
work on nulls are IS NULL/IS NOT NULL.  In 6.5 you can use COALESCE
as a handy abbreviation for certain sorts of IS NOT NULL tests, but
offhand I don't see a good way to apply it here.
        regards, tom lane

To the best of my knowledge, this is the way to do it; however, if you
wanted to save on coding, you could always create a function to do the
comparison for you and then call this function for each column.  Another
thing I've done (under Oracle) is to generalize and normalize the audit log
tables so that they can be used for changes to ANY table in the database.
The three tables required for the would be

--
-- top level table, stores general info about the SQL command that caused a
-- record's data to change
--
create table audit_event
(   audit_id               number(9)              not null,    /* unique
key                   */   dbms_operation         char(1)                null    ,    /* (I)nsert,
(U)pdate, (D)elete */   table_name             varchar2(30)           null    ,    /* table
being modifed          */   username               varchar2(30)           null    ,    /* user
making the modification */   audit_ts               timestamp              null    ,    /* when the
change was made     */   constraint pk_audit_event primary key (audit_id)
)

--
-- contains the column name/value pairs which unquely identify the record
being changed.
-- one-to-many relationship with audit_event table.  Will contain multiple
records per
-- SQL statement for modification of records with multi-column PK
--
create table audit_key
(   audit_id               number(9)              not null,    /*  FK to
audit_event */   key_col_name           varchar2(30)           not null,    /*  PK
column name    */    key_col_value          varchar2(64)           not null,    /*  PK value        */   constraint
pk_audit_keyprimary key (audit_id, key_col_name)
 
)


--
-- contains the column name/value pairs which for columns affected by the
SQL command.
-- For INSERT, this will contain all columns in the table.  For UPDATE,
this will 
-- contain only the columns which are modified.  No columns exist in this
table for a 
-- DELETE operation.
--
create table audit_column
(   audit_id               number(9)              not null,   column_name            varchar2(30)           not null,
old_value             varchar2(2000)         null    ,   new_value              varchar2(2000)         null    ,
constraintpk_audit_column primary key (audit_id, column_name)
 
)



Hope this gives some "food for thought" on how you can implement a
generalized audit capability for all of your tables.



Steven Bradley
Lawrence Livermore National Laboratory
PO Box 808
Livermore, California 94550
sbradley@llnl.gov



At 12:17 AM 7/14/99 -0500, you wrote:
>
>I'm trying to create a log table to track changes to a table..  so I've
>created a set of rules that look something like this:
>
>CREATE RULE "m_log_change" AS ON UPDATE TO "machine"
>do (
>  INSERT INTO machine_log (who, date, machnum, col, newval)
>    SELECT getpgusername(), 'now'::text, old.machnum,
>     'host', new.host
>    WHERE (new.host != old.host) or 
>    (old.host IS NOT NULL and new.host IS NULL) or
>        (old.host IS NULL and new.host IS NOT NULL);
>... more similar INSERT statements with different WHERE clauses..
>);
>
>My question is:
>
>Is this the best way to code the WHERE?  What I'm really after is "did the
>value change?".  I found that the tests to see if one-but-not-both of the
>values is NULL are required to identify times when the value changed to or
>from NULL.  
>
>Seems like a lot of extra work...
>
>
>Jim Rowan            DCSI            DCE/DFS/Sysadmin Consulting
>jmr@computing.com                                            (512) 374-1143
>
>
>