Creating an Audit Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

djjohnst - 27 Mar 2008 19:33 GMT

Hello,

I have a table called employee. Any time anything is added or updated
on this table there is trigger that fires off and send copies of the
data to another table called Emp_audit with the current timestamp. I
want to create a query of the employee's name, date, and time from
Emp_audit. However the tricky part is I've been asked to put the
string 'inserted on' by the rows date when it was added and 'Updated
on' by the date when it was updated.

For example, I need to Select NAME, 'Inserted on' || DATE, from
EMP_audit where rows that are insert rows.

AND

Select Name, 'Updated on' || Date, from EMP_audit where *rows that are
update rows*

I need to do this in one statement but I have no idea where to begin
here. Anyone have any ideas?

Reply to this Message

Dave Hughes - 27 Mar 2008 20:51 GMT

> Hello,
>
[quoted text clipped - 16 lines]
> I need to do this in one statement but I have no idea where to begin
> here. Anyone have any ideas?

I'm not 100% clear on what you're asking here... If you're looking for
a way to determine whether a row in the EMP_AUDIT table was created as
the result of an insert or an update, then short of there being an
explicit column that indicates this (filled in by the trigger), you
can't (*).

Alternatively, if you've got such an indicator already, then it's
simple... Assuming the indicator column is called ACTION and contains
"I" for rows resulting from an INSERT, and "U" for rows resulting from
an UPDATE, something like this should do the trick:

SELECT
NAME,
CASE ACTION
WHEN 'I' THEN 'Inserted on '
WHEN 'U' THEN 'Updated on '
END || CHAR(DATE)
FROM
EMP_AUDIT

(*) Not strictly true depending on various things. If rows in the
source table cannot be deleted, or they can be deleted and deletions
are logged in the audit table then it's possible (but difficult).
Otherwise, it's impossible (as any row that looks like an update could
equally be a deletion followed by a reinsertion).

Cheers,

Dave.

Reply to this Message

djjohnst - 28 Mar 2008 18:49 GMT

Thank you for the help. However, I found a solution that I was going
for. There actually is a way to determine whether or not it is a
insert or update. Here is the code I used. CHNG_TS is the timestamp
field.

db2 "SELECT EMPNO, LASTNAME || ', ' ||FIRSTNME || ' ' ||MIDINIT as
EMPLOYEE, 'Inserted on ' || substr(char(date(CHNG_TS)),1,10) || ' ' ||
substr(char(time(CHNG_TS)), 1,8) as DATE_AND_TIME
FROM EMP_DAN_AUDIT EDA
WHERE CHNG_TS =
(select MIN(CHNG_TS)
FROM EMP_DAN_AUDIT ED2
WHERE EDA.EMPNO = ED2.EMPNO)
UNION
SELECT EMPNO, LASTNAME || ', ' ||FIRSTNME || ' ' ||MIDINIT as
EMPLOYEE, 'Updated on ' || substr(char(date(CHNG_TS)),1,10) || ' ' ||
substr(char(time(CHNG_TS)), 1,8) as DATE_AND_TIME
FROM EMP_DAN_AUDIT EDA
WHERE CHNG_TS <>
(select MIN(CHNG_TS)
FROM EMP_DAN_AUDIT ED2
WHERE EDA.EMPNO = ED2.EMPNO) ORDER BY EMPNO"

> > Hello,
>
[quoted text clipped - 48 lines]
>
> - Show quoted text -

Reply to this Message