How to determine which tables are being audited within the LIMS-plus 3.x database
LIMS Administrators, Database Administrators
How can I check to see if a table is being audited on the backend(database)?
When attempting to view the audittrail on the case tab within LIMS, nothing appears. Is this field being audited?
LIMS-plus 3.x has the functionality to allow LIMS Administrators to view an audit trail from within the application. Database Administrators can view the audittrail table from outside of the application as well. The audittrail table is created when the LIMS database is initially configured for use. the audittrail table tracks changes made to records based on a series of triggers applied to select tables within the database. A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs.
During the initial installation of the LIMS-plus 3.x database, if the create triggers script is not run, tables will not have triggers appliedto them. As a result, the Audittrail table will not any data stored to it. ]
A laboratory is settings up a test environment and is creating the LIMS-plus database from scratch. The database administrator has chosen to run the database scripts without the use of the LIMS-plus 3.x Upgrader setup utility. After performing the database scripts, the database administrator alerts the LIMS Administrator that the database is ready for use within the test environment. A copy of the live production database was not restored when setting up the database in the test environment.
After setting up the Laboratory, departments and services within System Administration, the LIMS administrator alerts test users to begin entering in test cases. Two months go by and the LIMS administrator decides to look at the audit trail information on a few test cases. No information is coming up when engaging the audit functionality within the LIMS-plus 3.x application.
The LIMS Administrator was previously a controlled substance analyst prior to being promoted to the LIMS Administrator of the Laboratory. The LIMS Administrator does not have database management experience and leans heavily on the Database Administrator to make and/or review the database tables themselves.
The Database Administrator contacts JusticeTrax support to find out how to determine if a table is being audited.
Each table in your database being audited should have at least one trigger applied that records information into the AUDITTRAIL and AUDITSESSIONS tables.
[ In Microsoft SQL Server Enterprise Manager, you can view triggers on a single table by:
1. Selecting the table
2. Right-click and select All Tasks | Manage Triggers
3. From the drop list, select each trigger to view it
To list all triggers on your database, you will need to query the system tables.
In Microsoft SQL Server Enterprise Manager:
1. Select Tools | SQL Query Analyzer
2. Copy and paste the query that appears below and execute the query
select t.name,o.name from sysobjects t, sysobjects o
where t.type ='tr' and o.id=t.parent_obj
order by o.name, t.name
The screenshot listed below is the example of the query performed within SQL Server Management Studio Express 2005. The results of will be presented on the Results tab. Scroll through the list to see all tables being audited.