Re: [SQL] WHERE clause?

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_key primary 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 ,
constraint pk_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(at)llnl(dot)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(at)computing(dot)com (512) 374-1143
>
>
>