Hi Thomas,
(1) defining a default value on the colum (to ensure something is put there even
if the user does not give a value on the insert itself) [...]
Beware of the DEFAULT value, since a user could send a fake value with
the INSERT statement:
ij> -- I'm sylvain
ij> INSERT INTO Client( ... , CreatedBy) VALUES (... , 'Dilbert'); -- :(
You can only trust the content of the column CreatedBy *after* the
trigger that (over)write it has fired. Do not use that column before!
And (you will dislike that - if you don't think to it), if your users
have UPDATE permission on the table, they could do something like:
ij> UPDATE Client SET CreatedBy = 'Dilbert';
Of course, this will be logged - and could be traced back to the real
user name - but prevention is better than cure...
(1) showing the initially inserted value on the columns and
(2) audit records showing the 'old' value fom the initial inserrt and records
from after the update coming fom the second trigger - whereas I would have only
had one entry in case I could have overwritten transition variable content in
the first place with whatever would have been stored there prior to this being
written back to the data base.
Concerning the "audit" part, I'm not sure to understand exactly what you
want to log:
- only historical data (who changed what and when)
- and/or users try to do something ''illegal'' (like sending a fake
identity in the column CreatedBy)
Neither where you want to log your data: in an other table, by writing
to some file, ...
Anyway, remember that:
- TRIGGER AFTER INSERT allow you to access the NEW rows
- TRIGGER AFTER DELETE allow you to access the OLD rows
- TRIGGER AFTER UPDATE allow you to access both the OLD and NEW rows
[...] whereas I would have only
had one entry in case I could have overwritten transition variable content [...]
Finally, if you are concerned by those "two entries", due to the TRIGGER
AFTER UPDATE, you could rewrite it to something more 'clever':
ij> CREATE TRIGGER LogClientOwner
> AFTER INSERT ON Client
> REFERENCING NEW_TABLE AS NEW
> UPDATE Client SET Client.CreatedBy = USER
> WHERE Client.ID IN (SELECT ID FROM NEW)
> AND Client.CreatedBy != USER;
^^^^^^^^^^^^^^^^^^^^^^^^
This will no longer update the table if the
field CreatedBy has already the right value.
Best regards,
Sylvain
--
Website: http://www.chicoree.fr
