DB Question

Hey Everyone,
I am taking over a system that uses triggers exclusively for all
auditing entries. Here is a sample of the table structure:
Accounts
=========================
AccountId [Identity] [PK]
AccountName
......
AccountHistory
============================
AccountHistId [Identity] [PK]
AccountId (fk)
HistoryId (fk)
History
=========================
HistoryId [Identity] [PK]
UserId (fk)
HistorySummaryDesc
EventDateTime
HistoryDetails
===============================
HistoryDetailId [Identity] [PK]
HistoryId (fk)
There is a trigger on the Accounts DB that determines the level of
auditing set up by the user (None, Summary, Detail). If set to none, the
trigger does nothing. If set to Summary, a new row is inserted into the
History table with a summary description, then a new row is added to the
AccountHistory table pointing to the new History table row (the reason
is that the History table is used to hold ALL history changes everywhere
and the AccountHistory table is just used as an intersection table.
Here's my problem. This customer now want to be able to tell from PB
what the resulting AccountHistory ID was from the Insert/Update that was
issued from PB. I can't think of a reliable way to do this. Moving the
History generation into a SP call made from DB is not an option. They
require it to stay Trigger based.
Since this in a 100+ user environment, I can't simply expect to do a
SELECT on the AccountHistory table for the AccountId for the latest
entry, that may or may not be correct.
Does anyone have any suggestions?
Thank you
John DHi
I find your post confusing!
See http://www.aspfaq.com/etiquett__e.asp?id=5006 on how to post usable DDL
and the trigger code. Example data as insert statements
http://vyaskn.tripod.com/code.__htm#inserts would also help.
At a guess you will need to store some form of transaction identifier.
John
"John" <JohnD_NO_SPAM@.DelWare.com> wrote in message
news:%23Um8MeuMFHA.2384@.tk2msftngp13.phx.gbl...
> Hey Everyone,
> I am taking over a system that uses triggers exclusively for all auditing
> entries. Here is a sample of the table structure:
> Accounts
> =========================
> AccountId [Identity] [PK]
> AccountName
> ......
>
> AccountHistory
> ============================
> AccountHistId [Identity] [PK]
> AccountId (fk)
> HistoryId (fk)
>
> History
> =========================
> HistoryId [Identity] [PK]
> UserId (fk)
> HistorySummaryDesc
> EventDateTime
>
> HistoryDetails
> ===============================
> HistoryDetailId [Identity] [PK]
> HistoryId (fk)
>
> There is a trigger on the Accounts DB that determines the level of
> auditing set up by the user (None, Summary, Detail). If set to none, the
> trigger does nothing. If set to Summary, a new row is inserted into the
> History table with a summary description, then a new row is added to the
> AccountHistory table pointing to the new History table row (the reason is
> that the History table is used to hold ALL history changes everywhere and
> the AccountHistory table is just used as an intersection table.
> Here's my problem. This customer now want to be able to tell from PB what
> the resulting AccountHistory ID was from the Insert/Update that was
> issued from PB. I can't think of a reliable way to do this. Moving the
> History generation into a SP call made from DB is not an option. They
> require it to stay Trigger based.
> Since this in a 100+ user environment, I can't simply expect to do a
> SELECT on the AccountHistory table for the AccountId for the latest entry,
> that may or may not be correct.
> Does anyone have any suggestions?
> Thank you
> John D|||>> I am taking over a system that uses triggers exclusively for all
auditing entries. Here is a sample of the table structure: <<
So based on the DDL that you did not post, it looks like the idiot who
did this uses IDENTITY instead of proper keys so you have no data
integrity and weak temporal model.
option. They
require it to stay Trigger based. <<
Boy, they hate RDBMS with a passion, don't they? Can you talk them
into buying a audit tool which is what they should have had in the
first place?|||--CELKO-- wrote:
> auditing entries. Here is a sample of the table structure: <<
> So based on the DDL that you did not post, it looks like the idiot who
> did this uses IDENTITY instead of proper keys so you have no data
> integrity and weak temporal model.
>
> option. They
> require it to stay Trigger based. <<
> Boy, they hate RDBMS with a passion, don't they? Can you talk them
> into buying a audit tool which is what they should have had in the
> first place?
>
I suggested that they purchase a log tool such as ApexSQL, but they
insist on being able to examine the audit entries from the app itself.
I can make some minor table adjustments, but a hard requirement is for
it to stay trigger based.
John|||>> I suggested that they purchase a log tool such as ApexSQL, but they
insist on being able to examine the audit entries from the app itself.
<<
Why? Doesn't that give the guy who broke the rules the ability to see
the audit trail he wants to cover up? And the log tool would be
cheaper in the long run than the extreme overhead of using and
maintaiing so many triggers.
Have they looked into generational concurrency databases like
Interbase? They would get the audit and the ability to rollback to a
point in time automatically and it would not interfere with normal
operations.
Oh well, time to look for another company to work for, I guess.|||Then you have a real dilemma.. If the data necessary to satisfy this
requirement is not accessible by this trigger, and you're not allowed t ouse
anything but triggers...
You don't need to be a rocket surgeon to figure that one out.
A) You need to use something other than a trigger... How much of the
functional architecture will lie within a trigger, and how much will be
outside is then the only choice to be made... or...
B) you have to get them to reconsider the rules... or...
C) you get to slow leak them for as long as possible while you look for
other work for a more reasonable employer...
"John" wrote:

> --CELKO-- wrote:
> I suggested that they purchase a log tool such as ApexSQL, but they
> insist on being able to examine the audit entries from the app itself.
> I can make some minor table adjustments, but a hard requirement is for
> it to stay trigger based.
> John
>|||> Here's my problem. This customer now want to be able to tell from PB what
> the resulting AccountHistory ID was from the Insert/Update that was
> issued from PB. I can't think of a reliable way to do this. Moving the
> History generation into a SP call made from DB is not an option. They
> require it to stay Trigger based.
How on earth will they use this information? Is this going to tell the user
what they have changed after they do it?
Don't you have the key of the table you have just modified? Can't you use
it, plus the userId who made the change?
One thing you might try is to simply return values from your trigger. The
app would then have to respond to results/messages being sent back. I would
use raiserror with a known number and state and let the client deal with it.
For example, add a trigger (which aren't all bad if you structure them
well.) to the AccountHistory table that raises an error with the new row
number. This will be easy to implement, and the database will have very
little responsibilty in the change. The app will have to become smart
enough to use the error message as informational and keep going.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"John" <JohnD_NO_SPAM@.DelWare.com> wrote in message
news:%23Um8MeuMFHA.2384@.tk2msftngp13.phx.gbl...
> Hey Everyone,
> I am taking over a system that uses triggers exclusively for all auditing
> entries. Here is a sample of the table structure:
> Accounts
> =========================
> AccountId [Identity] [PK]
> AccountName
> ......
>
> AccountHistory
> ============================
> AccountHistId [Identity] [PK]
> AccountId (fk)
> HistoryId (fk)
>
> History
> =========================
> HistoryId [Identity] [PK]
> UserId (fk)
> HistorySummaryDesc
> EventDateTime
>
> HistoryDetails
> ===============================
> HistoryDetailId [Identity] [PK]
> HistoryId (fk)
>
> There is a trigger on the Accounts DB that determines the level of
> auditing set up by the user (None, Summary, Detail). If set to none, the
> trigger does nothing. If set to Summary, a new row is inserted into the
> History table with a summary description, then a new row is added to the
> AccountHistory table pointing to the new History table row (the reason is
> that the History table is used to hold ALL history changes everywhere and
> the AccountHistory table is just used as an intersection table.
> Here's my problem. This customer now want to be able to tell from PB what
> the resulting AccountHistory ID was from the Insert/Update that was
> issued from PB. I can't think of a reliable way to do this. Moving the
> History generation into a SP call made from DB is not an option. They
> require it to stay Trigger based.
> Since this in a 100+ user environment, I can't simply expect to do a
> SELECT on the AccountHistory table for the AccountId for the latest entry,
> that may or may not be correct.
> Does anyone have any suggestions?
> Thank you
> John D