October 23, 2008 at 10:53 am
This is a regular mdb, not an adp or whatever other extension we have...lol
On the Form Properties the Record Source is the table name and then the text box Control Source is the name of the column in the table...
having changed a value i would assume it would fire off the trigger... Is there some disconnect I'm not aware of, nor suprised if there is...lol
Any ideas?
Thanks,
John
October 23, 2008 at 1:28 pm
You would get more ideas if you adequately explained your problem. Am I to understand that you have a SQL Server database which you are connecting to using MS Access (Linked tables I assume) and on one of the SQL Server tables you have placed a trigger which you expect to fire when you update a value in the table?
If I am correct in my assumptions, could you please post the schema of the table (table name, column names and data types) and the trigger code? Also please post what you expect to happen, for example when I update last_name I expect the full_name column to be updated to be last_name, first_name.
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
October 23, 2008 at 3:38 pm
If I am correct in my assumptions, could you please post the schema of the table (table name, column names and data types) and the trigger code? Also please post what you expect to happen, for example when I update last_name I expect the full_name column to be updated to be last_name, first_name.
You are correct...
The @key Variables Capture the PK Value and Name...
The Insert Into is the tbl Audit and it's all pretty much VARCHAR
The CASE Statement is the Columns and there DataTypes that I want to capture changes...
The other thing is SET @SYSUSER is putting the SYSTEM_USER value in the table
Note, when I do manual change to DB, or SQL Update or thru the MS Access form, all seems to work fine for me...
but when my boss does it on the Forms it doesn't seem to work for him????
****
If you notice my 2 derived tables, one for inserted and one for deleted; can't that be combined into 1; I hesitated because this one took for ever and I had a vulcon mind melt...doh
Thanks,
John
ALTER TRIGGER [dbo].[trg_tlkpSegmentGroupSource_AuditUpdates]
ON [dbo].[tlkpSegmentGroupSource]
--Author: John Steinbeck
--Date: 24 Sept 08
--Purpose: This Trigger is designed to capture any column whose value is updated... you will specify what columns you want
FOR UPDATE
AS
---- Declarations
DECLARE @TableName VARCHAR(100)
DECLARE @KeyField VARCHAR(100)
DECLARE @KeyVal VARCHAR(100)
DECLARE @OldVal VARCHAR(500)
DECLARE @NewVal VARCHAR(500)
DECLARE @COL_NAME NVARCHAR(100)
DECLARE @SYSUSER VARCHAR(100)
DECLARE @getdate-2 DATETIME
DECLARE @XID VARCHAR(50)
---- Instantiate
SET @TableName = 'tlkpSegmentGroupSource' --Table Name of the Table used for this Trigger CHANGE MY VALUE
SET @KeyField = 'SegmentGroupSourceID'
SELECT @KeyVal = SegmentGroupSourceID FROM INSERTED --CHANGE MY VALUE
SET @getdate-2 = CAST(GETDATE() AS DATETIME)
SET @XID= SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER)+1,(LEN(SYSTEM_USER)+1) - CHARINDEX('\',SYSTEM_USER))
SELECT @SYSUSER = USERNAME FROM tblAuthorizations
WHERE XID = @XID
SET @SYSUSER = ISNULL(@SYSUSER, @XID)
-- FOR INSERT AND UPDATE ONLY...
---- Update Audit Fields on Table
Update tlkpSegmentGroupSource
SET UpdatedBy = @SYSUSER, LastUpdate = @getdate-2
WHERE SegmentGroupSourceID IN (SELECT SegmentGroupSourceID FROM Inserted)
-- LOOPING THRU SYSTEM FUNCTION TO SEE WHAT COLUMNS HAVE BEEN UPDATED AND GET THE NAMES... STORE IN TEMP TBL
INSERT INTO tblAuditLog
(TableName, KeyField, KeyValue, UpdatedBy, LastUpdate, ChangedColumn, OldValue, NewValue)
SELECT
@TableName AS TBL
, @KeyField AS KY
, @KeyVal AS KV
, @SYSUSER AS SYS
, @getdate-2 AS THEDATE
, OLD.NAME AS COLNAME
, ISNULL(CAST(OLD.OLD_COL_VALUES AS VARCHAR(500)),'') AS OLD
, ISNULL(CAST(NEW.NEW_COL_VALUES AS VARCHAR(500)),'') AS NEW
FROM (
SELECT
COLS.N
, (CASE COLS.N
WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
END) OLD_COL_VALUES
, COLS.NAME
FROM
(
SELECT DISTINCT *
FROM DELETED
) D
CROSS Join
(
SELECT
S.NAME, T.N AS N
FROM
DBO.TALLY T INNER JOIN SYSCOLUMNS S
ON T.N = S.COLID
WHERE ((N IN (2, 3, 4, 5, 6)) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND ID = object_id(@TableName))
AND (CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
End
) = 1
) COLS
) OLD INNER JOIN
(
SELECT
COLS.N
, (CASE COLS.N
WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
END) NEW_COL_VALUES
, COLS.NAME
FROM
(
SELECT DISTINCT *
FROM INSERTED
) I
CROSS Join
(
SELECT
S.NAME, T.N AS N
FROM
DBO.TALLY T INNER JOIN SYSCOLUMNS S
ON T.N = S.COLID
WHERE ((N IN (2, 3, 4, 5, 6)) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND ID = object_id(@TableName))
AND (CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
End
) = 1
) COLS
) NEW
ON OLD.N = NEW.N
October 23, 2008 at 6:13 pm
John
Did you grant insert permission to public on tblAuditLog? That might cause it to not work for others. Just a thought.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 23, 2008 at 6:36 pm
I'll ask the boss, 2 questions on that... in the query you'll see where I query the tblauthorizations, I think that just lets the user use the MS Access;
What table should I check for user rights???
John
October 24, 2008 at 7:34 am
The easiest way to check user rights is to check in Enterprise Manager in the security area of the database to see if the user is
granted rights to the database and then right-click on the user to see the objects which they can access. Actually if security
is done according to best practices the user would be a member of a group.
Is any error being returned when your boss makes a change? If there is an error in the trigger the update would be rolled back, so
is the update actually successful? Are you sure your boss is linking in tables from the same database? There is also the possibility
that the columns being changed aren't in the columns you are checking in the trigger.
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
October 24, 2008 at 7:40 am
John
I'm not sure about a MDB, but with ADP, all permissions come from the Server, not the Access front end. I believe the a MDB has built in user roles and permissions, but I would suspect your server does not care about the front end, and each user will have to have appropriate permissions coming from SQL server, before they will be allowed to to anything. Again, I don't know about MDB, maybe the permissions do carry over, but it is definately something to look into.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2008 at 7:49 am
This morning my boss went in and looks and saw most changes he made yesterday... it's like 1 out of 10 where something may not end up in the log table, not to mention it takes a long time to see that update to the log... and in the table itself that is being updated the updatedby, user, etc... is sporadic too...
Is my code that messed up to work only once in awhile?
October 24, 2008 at 8:04 am
The trigger is firing all the time, the issue is that you have something in your trigger code that is missing an update. I have to be honest and say that I think the trigger is way too complex and I think it got that way because you wanted to try to have a generic update trigger for all your tables. My personal preference is to keep things as simple as possible so that it is easy to read and understand when I go back into it months or years later. Usually this means more work (typing) to get it working, but it usually makes maintenance easier. It's also why I normally do logging by just copying the before (deleted) row to a an audit table with changed_by and change_date columns. If I need to now what changed I just compare it to either the active record or the next record in the log table.
In your situation I'd rewrite the trigger with If Update(column) for each column you are concerned with and then do the insert within each IF block. You may get multiple rows because multiple columns may be updated. I'd also do a check that the column value actually changed (inserted.column <> deleted.column) as I don't know how Access is doing the update.
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
October 24, 2008 at 8:04 am
john.steinbeck (10/24/2008)
Is my code that messed up to work only once in awhile?
John, that's a pretty subjective question, but there are countless auditing methods out there. I found one trigger on Nigel Rivett's web site around two years ago when I was just learning SQL, that I have adapted for use on many of my tables, and I have never once had an issue with it. It's pretty easy to understand, and can be found at...
http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply