July 9, 2018 at 3:13 am
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
July 9, 2018 at 4:26 am
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
July 9, 2018 at 4:44 am
Yes it is
July 9, 2018 at 5:27 am
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
July 9, 2018 at 5:32 am
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
July 9, 2018 at 6:50 am
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
July 9, 2018 at 6:55 am
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
July 10, 2018 at 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.
July 10, 2018 at 7:14 am
Take a look at Data Macros - they are similar to triggers in SQL Server.
Audit Trail Using Data Macros
Sue
July 11, 2018 at 8:37 am
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
July 11, 2018 at 10:47 am
igor.klepoch 31928 - Tuesday, July 10, 2018 4:57 AMMS 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.
July 11, 2018 at 4:32 pm
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.
July 17, 2018 at 12:36 pm
Sue_H - Tuesday, July 10, 2018 7:14 AMTake a look at Data Macros - they are similar to triggers in SQL Server.
Audit Trail Using Data MacrosSue
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 17, 2018 at 1:11 pm
sgmunson - Tuesday, July 17, 2018 12:36 PMSue_H - Tuesday, July 10, 2018 7:14 AMTake a look at Data Macros - they are similar to triggers in SQL Server.
Audit Trail Using Data MacrosSue
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply