October 17, 2007 at 4:36 am
I recently saw a featured script on this site and thought it would be very useful to use in my system.
The code was a stored procedure that generated the SQL needed to create an audit table and the 3 triggers needed to populate it. The insert trigger I am using so far is:
CREATE TRIGGER [trg_CUSTOM_TEXT_UPDATE] ON [something].[CUSTOM_TEXT] FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [SomeDB].[something].[CUSTOM_TEXT_AUDIT]
(
[CustomSentancePK]
,[SiteFK]
,[PageFK]
,[CMSPageFK]
,[SentanceID]
,[Sentance]
,[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
[CustomSentancePK]
,[SiteFK]
,[PageFK]
,[CMSPageFK]
,[SentanceID]
,[Sentance]
,'update'
,suser_sname()
,GETDATE()
FROM INSERTED
END
GO
Now it all works great but I was hoping to extend one part of it and don't really know the best way to go about it.
At the moment the audit triggers log the user that made the update using suser_name(). As my system is a web based CMS system this value is always going to be the same user as its the logon used by the website to connect to the DB.
What I really would like to do is if the changes are made from the website then append to this the actual website admin logon details that are held in Session variables once the administrator logs onto the site through a web based logon page. However as all the auditing is done through triggers I don't know the best way of passing that information into the trigger. I have two ideas and I am wondering which would be the best.
Idea 1.
Create a new column in the table that's being audited CUSTOM_TEXT that stores the last webuser who made a change eg CUSTOM_TEXT.LastChangedBy
This way I can pass the webusers details to the stored procedure that carries out updates to this table as a parameter and insert it along with the other information and it will automatically be in the INSERTED recordset.
RTRIM(suser_sname() + ' ' + ISNULL(LastChangedBy,''))
The downside to this is that I will have to create a new column in the table CUSTOM_TEXT that won't be used for anything apart from this one bit of logging.
Although most of the changes to the table needing auditing does occur from the website sometimes changes are done directly in the DB so having a trigger does seem the best way of auditing the changes unless maybe I did the following.
Idea 2
Create a stored proc that does the actual insert into the audit table and call that from two places depending on where the change is being made from.
So the stored procedure that is used by the website to do updates to CUSTOM_TEXT table handles its own auditing and calls another stored proc usp_audit_custom_text passing into the web user logon.
Then to handle updates not done by the website I would still have the insert trigger but that would also call the usp_audit_custom_text proc ONLY if the suser_sname <> 'myweb_logon'. So it would only handle audits for non web based updates.
As the ActionUser parameter would be passed into the usp_audit_custom_text proc I wouldn't need to have this extra column in the table.
I haven't done much work with Triggers before so I don't know which way would be best or if there is another route to take. All I know is that I need to be able to audit the actual person who made the changes to the CMS table not just the system user.
Does anyone have some suggestions?
Thanks in advance for any help and ideas.
October 23, 2007 at 9:00 am
To the best of my knowledge, the only way to get information to a trigger is to have the information in the table. Here is an idea on how to do this. You return the Primary key of the audit record to your web application and then call an sp that updates the ActionUser with the user name stored in the session.
Of the 2 options you suggest I would go with option 1 for the following reasons:
1. Can you guarantee that no one is ever going to login to the server using the web login, but from SSMS or access? If not, then basing actions on a trigger on a specific suser_sname() could cause you to miss actions.
2. The inserted and deleted tables are not available outside the trigger, so the sp called by your trigger will not have access to them. You would have to put the data from inserted and deleted into temporary tables in order to access that data in the sp.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply