Database Audit

  • Nqobilemoyo

    SSC Veteran

    Points: 252

    I have an already existing database and was hoping if someone could help me create a audit. 

    Find below the database sample: 

    https://www.dropbox.com/s/r1noqu1bczzcuou/17-18Sheffield%20Cycles1%281%29.mdb?dl=0

    The requirements of the audit table are that it should track users(username) who update the tblSOLine table and the time that they do an update. This will involve creating a trigger that fires when an update is made (it doesn't need to track insert and deletes. 

    The information which needs to provide are:
    the create table code 
    the create trigger code 
    the 'update a record in tblSOLine' code 
    the 'display the records in the audit table' code to show that the trigger has worked
    the 'insert a new record to tblSOLine' code

  • Gail Shaw

    SSC Guru

    Points: 1004424

    MDB? Is that an MS Access file?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nqobilemoyo

    SSC Veteran

    Points: 252

    Yes it is

  • Gail Shaw

    SSC Guru

    Points: 1004424

    I'll see if I can get this moved to the MS Access forum, as a trigger that is written for SQL Server won't work in Access (I can't even remember if it even has triggers)

    In the mean time, can you post DDL and sample data for your tables?
    Some will be hesitant to download and open an unknown Access DB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nqobilemoyo

    SSC Veteran

    Points: 252

    Hi there, thanks for the help however i am unsure what content is needed for the DDL as i am brand new to sql and was looking for help

  • Gail Shaw

    SSC Guru

    Points: 1004424

    The CREATE TABLE statements for the tables, if Access even does that. It's been years since I looked at Access to be honest.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nqobilemoyo

    SSC Veteran

    Points: 252

    Here i created something however im not sure if its right according to my database:
    Use Sheffield_Cycle 

    Insert into tblSOLIine 

    (

      OrderID integer Identity(1,1) primary key,

      OrderApprovalDateTime datetime,

      OrderStatus varchar(20)

    )

    create table tblOrdersAudit

    (

      OrderAuditID integer Identity(1,1) primary key,

      OrderID integer,

      OrderApprovalDateTime datetime,

      OrderStatus varchar(20),

      UpdatedBy nvarchar(128),

      UpdatedOn datetime

    )

    go

    create trigger tblTriggerAuditRecord on tblSOLine

    after update, insert

    as

    begin

      insert into tblOrdersAudit 

      (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )

      select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 

      from  tblOrders t 

      inner join inserted i on t.OrderID=i.OrderID 

    end

    go

    insert into  values (NULL, 'Pending')

    insert into tblSOLIine  values (NULL, 'Pending')

    insert into tblSOLIine  values (NULL, 'Pending')

    go

    select * from tblSOLIine 

    select * from tblOrdersAudit

    update tblSOLIine  

    set OrderStatus='Approved', 

    OrderApprovalDateTime=getdate()  

    where OrderID=1

    go

    select * from tblSOLIine 

    select * from tblOrdersAudit order by OrderID, OrderAuditID

    go

    update tblSOLIine  

    set OrderStatus='Approved', 

    OrderApprovalDateTime=getdate()  

    where OrderID=2

    go

    select * from tblSOLIine 

    select * from tblOrdersAudit order by OrderID, OrderAuditID

    go

    update tblSOLIine  

    set OrderStatus='Cancelled'

    where OrderID=1

    go

    select * from tblSOLIine 

    select * from tblOrdersAudit order by OrderID, OrderAuditID

    go

  • igor.klepoch 31928

    Valued Member

    Points: 55

    MS Access has a Upsizing Wizard that moves your table(s) to MS SQL Server (so you essentially get a client-server application, MS Access still being a front-end)
    You can use trigger running on the SQL Server afterwards.

  • Sue_H

    SSC Guru

    Points: 89699

    Take a look at Data Macros - they are similar to triggers in SQL Server.
    Audit Trail Using Data Macros

    Sue

  • WILLIAM MITCHELL

    SSChampion

    Points: 13535

    Here is an article that describes how to create an audit trail in Access:
    http://www.wvmitchell.com/tips/Change_Tracking_in_Access.html
    HTH

  • Chris Harshman

    SSC-Forever

    Points: 41610

    igor.klepoch 31928 - Tuesday, July 10, 2018 4:57 AM

    MS access has a upsizing wizard that moves your table(s) to MS SQL server. (so you essentially get a client-server application, MS access still being a front-end)
    You can use trigger running on the SQL server afterwards.

    I'd be very very careful with using anything generated by the upsizing wizard.  I admit I haven't looked at it in many years, but I remember it would recommend poor data types for columns when it did the conversion to SQL Server.

    That being said, I've worked with a number of applications that did successfully use an MS Access front end to a SQL Server or Oracle back end.

  • WILLIAM MITCHELL

    SSChampion

    Points: 13535

    I've never used the Upsizing Wizard, but about 10 years ago I used the SSMA, SQL Server Migration Assistant, which did a fine job of moving the Access tables into SQL Server & linking from Access to SQL Server.

  • sgmunson

    SSC Guru

    Points: 110412

    Sue_H - Tuesday, July 10, 2018 7:14 AM

    Take a look at Data Macros - they are similar to triggers in SQL Server.
    Audit Trail Using Data Macros

    Sue

    This is the only way to get trigger-like functionality in MS Access, and requires Access 2010 or later.  You can't use DDL to create a trigger in MS Access, and the trigger code posted earlier was AFTER UPDATE, INSERT  which adds INSERTs to the fray, when the original requirement (same poster - the OP - Nqobilemoyo) was only for UPDATEs.   Data Macros are just like VBA modules, but operate like triggers do in SQL Server.  That kind of functionality is more useful for MS Access-built apps that use bound controls.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Sue_H

    SSC Guru

    Points: 89699

    sgmunson - Tuesday, July 17, 2018 12:36 PM

    Sue_H - Tuesday, July 10, 2018 7:14 AM

    Take a look at Data Macros - they are similar to triggers in SQL Server.
    Audit Trail Using Data Macros

    Sue

    This is the only way to get trigger-like functionality in MS Access, and requires Access 2010 or later.  You can't use DDL to create a trigger in MS Access, and the trigger code posted earlier was AFTER UPDATE, INSERT  which adds INSERTs to the fray, when the original requirement (same poster - the OP - Nqobilemoyo) was only for UPDATEs.   Data Macros are just like VBA modules, but operate like triggers do in SQL Server.  That kind of functionality is more useful for MS Access-built apps that use bound controls.

    Glad you noticed that one, not surprised though 🙂

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply