Show only new and removed records by comparing to MySQL tables

Shell Programming and Scripting
1

Hello all;

I have been really frustrated with finding the correct perl code (and MySql statements) to accomplish what I thought was straight forward...I have tested I don't know how many different codes\suggestions I found on the net without any success...but anyhow let me explain my plight and what I am looking for...

I have 2 tables (in same db called audit)
Table 1:

Name: permchk_violations
Fields: `v_node` char(25)
`v_type` char(1)
`v_fd_name` char(100)
`v_path` char(100)
`v_rights` char(9)
`v_owner` char(25)
`v_group` char(25)
`v_size` decimal(15,0)
`v_moddate` char(25)
`v_detected_date` decimal(6,0)
`v_inode` decimal(8,0)

Table 2:

Name: permchk_violations_tmp
Fields: `t_node` char(25)
`t_type` char(1)
`t_fd_name` char(100)
`t_path` char(100)
`t_rights` char(9)
`t_owner` char(25)
`t_group` char(25)
`t_size` decimal(15,0)
`t_moddate` char(25)
`t_detected_date` decimal(6,0)
`t_inode` decimal(8,0)

So what my code does right now is:

  1. copies all records from permchk_violations to permchk_violations_tmp
  2. truncates permchk_violations so that it's ready to be populated with more data
  3. goes and gets data and populates permchk_violations
  4. This is what I need help with - I am suppose to check the newly populated permchk_violations table to the data that was copied to the permchk_violations_tmp table earlier...what I want from this comparison is to do the following:

a. find records that exist in permchk_violations but NOT IN permchk_violations_tmp; write these records to another table (let's call it permchk_violations_new)

b. find records that exist permchk_violations_tmp but NOT IN permchk_violations; write these records to another table (let's call it permchk_violations_removed)

IMPORTANT: I do not care about records that may be "altered" (ie a field changed it's value)

ASSUMPTION: the two tables permchk_violations_new and permchk_violations_removed will have the same field structure as the other two tables I have shown.

So this is my dilemma..hope someone can assist ASAP...I need to get this perl script into production in 1 week's time.

If you need further information please let me know.

Thanks in advance.