January 6, 2008 at 8:16 pm
Hi.
Firstly this is my first post to any SQL forum, so please treat me gently.
I develop MS Access databases with MS-SQL backends. I'm the junior (Old guy tho') in a small office, but the seniors can't answer this one for me anyway.
For the record, I work in Tauranga, New Zealand.
I'm trying to use the DML trigger on a table (Customers), in conjuction with the 'deleted' and 'inserted' tables, to:
1. determine how many columns are in the 'deleted' table
2. loop through these columns getting the data out of each column, storing the data in a variable @OldData
3. repeat the loop in the 'inserted' table, storing again in a variable @NewData
4. compare the @OldData & @NewData variables and if different, build a string @DataString, concatenating this variable as it detects changes.
5. write the @DataString, along with other data, into a audit table.
I've had some success, using parts of the code outside the trigger, to get the column data (not just the column name) into a variable. But inside the trigger, it all fails.
It does run through, but it always writes null (or doesn't write at all I guess) into the audit table columns (EmployeeID & Datastring). The other columns in the update statement fill correctly.
While I could code each columname, I want to be able to use this code as a plug in on any other databases/tables, with only minimal changes.
I've been round and round on this and getting nowhere. If anyone out there can help me get this right, I'd much appreciate it. Thanks in advance.
The code is:
ALTER TRIGGER [dbo].[AuditDML]
ON [dbo].[Employee]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @dBaseName varchar (30)
DECLARE @TableName varchar (30)
DECLARE @ID_Field_name varchar (30)
DECLARE @ID_Field_number int
DECLARE @Counter int
DECLARE @StringColumn varchar (30)
DECLARE @DataString varchar (1000)
DECLARE @Operation char(8)
DECLARE @OldData varchar (100)
DECLARE @NewData varchar (100)
DECLARE @ColumnCount int
DECLARE @ParDefinition nvarchar (500)
DECLARE @mysql nvarchar (100)
DECLARE @result_ID int
DECLARE @result nvarchar (30)
SET @TableName = 'Employee';
SET @ID_Field_name = 'EmployeeID'; -- set this to be the id field used in the main table eg. EmployeeID, CustID etc
SET @Counter = 0;
set @DataString = '';
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @Operation = 'OLD DATA';
---- Get the old data
SET @mysql = (SELECT @result + '=' + @ID_Field_name FROM deleted);
SET @ParDefinition = N'@result_ID int OUTPUT';
EXEC sp_executesql @mysql, @ParDefinition, @result_ID = @result_ID OUTPUT ;
SET @ID_Field_number = @result_ID;
-- GET THE COUNT OF THE COLUMNS IN THE CURRENT TABLE
-- USED TO LOOP THROUGH AND GET THE COLUMN NAMES
SELECT @ColumnCount =
(
SELECT count(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'deleted'
);
WHILE @Counter < @ColumnCount
BEGIN
Set @Counter = @Counter + 1
-- find the first column name and subsequent column names
SELECT @StringColumn = (SELECT COL_NAME(OBJECT_ID(@TableName), @Counter) AS 'Column Name')
-- find the data in the same column in the deleted table
SELECT @mysql = (SELECT @result + '=' + @StringColumn FROM deleted)
SET @ParDefinition = N'@result varchar (30)OUTPUT';
EXEC sp_executesql @mysql,@ParDefinition, @result = @result OUTPUT;
SET @OldData = @result
-- Find the data in the same column in the inserted table
SELECT @mysql = (SELECT @result + '=' + @StringColumn FROM inserted)
SET @ParDefinition = N'@result varchar (30)OUTPUT';
EXEC sp_executesql @mysql,@ParDefinition, @result = @result OUTPUT;
SET @NewData = @result
-- enter script to check data in deleted against inserted and report changes
-- and enter that change into a string concatenated as it loops through the columns.
IF @OldData <> @NewData
BEGIN
SET @OldData = @StringColumn + N': ' + @OldData
SET @DataString = @DataString + @OldData + ', '
END
CONTINUE
END
END
INSERT INTO [dbo].[DMLaudit] (DateChanged, TableName, UserName,
Operation, EmployeeID, DataString)
SELECT GetDate(), @TableName, suser_sname(), @Operation,
@ID_Field_number, @DataString
-- end of script running comparisons
-- enter script to report data from inserted table (ie new data entered)
;
January 6, 2008 at 9:16 pm
A few comments:
- the inserted and deleted columns for a given table have the same structure as the table itself, since it has a record of "what was just changed" in that given table.
- I'm not sure that you're clear on this, so I'll throw it out there: there isn't one single instance of the inserted and deleted, but rather one set of virtual tables each per "real" table. As a result, you could get the column names by simply selecting for:
select * from sys.all_columns sc where sc.object_id=object_id('mytable')
- the way you're coding your trigger is going to set you up for problems, since you're assuming that the inserted and deleted table will only have one record in it at any given trigger, which is not the case. Keep in mind that they're VIRTUAL tables, so I wouldn't look them up - I'd look up the base table.
- you're checking to see if anything is updated the "hard way". there's a built-in syntax that works within triggers to detect which columns have been updated. It looks like
IF UPDATED(column)
Begin
--do something
End
Finally - I'm not 100% clear on what you're writing out, but it seems like you're taking an awfully difficult road to do something simple. Doesn't your audit table have the same structure as the "original table" (with possibly a few more for tracking purposes)? Just curious.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 7, 2008 at 12:00 am
Thanks for that Matt.
I'm sure you're right that my lack of experience is creating a long way round instead of a simple solution.
I wasn't aware that the 'deleted' & 'inserted' tables held more than one record at a time.
I thought they would be created for a record change, removed and recreated for successive record changes. My mistake obviously.
I wasn't planning on having equivalent columns in the audit table (with obvious datetime additionals). I was just looking to create a string with change details.
I'll reassess how I attack this taking into account your comments.
Again, thanks.
Brad
January 7, 2008 at 12:56 am
OMG - I cannot believe I spent so much time on this and then had it solved in less than an hour tonight.
Matt you really put me on the right track - keep it simple.
Anyway, a quick search of this forum gave me a total simple solution by George Palacean at
http://www.sqlservercentral.com/Forums/FindPost299093.aspx
So thanks to George. I've coded it, tested it, enhanced it just a little and it's exactly what I wanted.
Awesome.
January 7, 2008 at 2:27 pm
Thanks for the feedback. Good to know that you got what you needed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy