April 7, 2009 at 7:56 am
TABLE AUDIT:
CREATE TABLE [dbo].[tblAudit](
[tableid] [nchar](10) NULL,
[employeeid] [nchar](10) NULL,
[tablename] [nvarchar](50) NULL,
[fieldname] [nvarchar](50) NULL,
[oldvalue] [nvarchar](200) NULL,
[newvalue] [nvarchar](200) NULL,
[changedate] [nvarchar](200) NULL,
[userid] [nvarchar](200) NULL,
[username] [nvarchar](200) NULL,
[action] [nchar](10) NULL
) ON [PRIMARY]
FULL TRIGGER:
ALTER TRIGGER [trgInsertAudit]
ON [dbo].[tblEmployeeId]
for update
AS
BEGIN
declare @tableid nvarchar(10)
declare @employeeid nchar (10)
declare @tablename nvarchar(50)
declare @fieldname nvarchar(100)
declare @changedate nvarchar(30)
declare @userid nvarchar(50)
declare @username nvarchar(50)
declare @action nchar(2)
declare @dummyfld nvarchar(50)
declare @tmpOldQuery nvarchar(200)
declare @tmpNewQuery nvarchar(200)
declare @oldvalue nvarchar(50)
declare @newvalue nvarchar(50)
set @tableid = (select tableid from inserted where employeeid = '1')
set @EmployeeId =(select employeeid from inserted where employeeid = '1')
set @tablename = 'tblEmployeeId'
set @ChangeDate = CONVERT(VARCHAR(20), GETDATE(), 100)
set @userid = system_user
set @username = suser_sname()
set @action = 'U'
Select * Into #Ins From Inserted
Select * Into #Del From Deleted
DECLARE @ParamDef nvarchar(2000)
declare @rtnVal nvarchar(1000)
declare @rtnValX nvarchar(1000)
DECLARE fieldnames_cursor CURSOR
FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'tblEmployeeId'
OPEN fieldnames_cursor
FETCH NEXT FROM fieldnames_cursor INTO @fieldname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
IF @fieldname <> 'DateCreated'
BEGIN
SET @fieldname = RTRIM(@fieldname)
SELECT @tmpOldQuery = N'select @rtnVal = @fieldname from #Del where employeeid = 1'
SELECT @ParamDef = N'@fieldname nvarchar(200), @rtnVal nvarchar(200) OUTPUT'
EXEC dbo.sp_executesql @tmpOldQuery,
@ParamDef,
@fieldname,
@rtnVal=@fieldname output
Select @rtnVal as RtnVal
-- Here the oldValue will be set to rtnval and similar code to get the @new value
If @oldvalue <> @newvalue then
exec [dbo].[spInsertAuditRec] @tableid, @employeeid, @tablename, @fieldname, @oldvalue, @newvalue, @changedate, @userid, @username, @action
END
END
FETCH NEXT FROM fieldnames_cursor INTO @fieldname
END
CLOSE fieldnames_cursor
DEALLOCATE fieldnames_cursor
end
April 7, 2009 at 8:06 am
What's the primary key of the Employee table?
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
April 7, 2009 at 8:21 am
EmployeeId
April 7, 2009 at 9:14 am
Here you go. This will work no matter whether you update one row or one thousand rows.
I don't know how well unpivot works on larger row sets, but it will work significantly better than nested cursors.
CREATE TRIGGER [trgInsertAudit] ON [dbo].[tblEmployeeId] AFTER UPDATE
AS
SET NOCOUNT ON
INSERT INTO tblAudit (TableID, EmployeeId, TableName, FieldName, OldValue, NewValue, ChangeDate, userid, username, action)
SELECT unPvtInserted.tableid, unPvtInserted.EmployeeId, 'tblEmployeeId' AS TableName, unPvtInserted.NewColumnName, unPvtDeleted.OldColumnValue, unPvtInserted.NewColumnValue, GETDATE(), system_user, suser_sname(), 'U'
FROM
(SELECT d.EmployeeId, CAST(SSN4 AS NVARCHAR(200)) AS SSN4, CAST(SSN5 AS NVARCHAR(200)) AS SSN5, CAST(AcctngKey AS NVARCHAR(200)) AS AcctngKey,
CAST(d.Status AS NVARCHAR(200)) AS Status, d.tableid
FROM deleted d) p
UNPIVOT (OldColumnValue FOR OldColumnName IN (SSN4, SSN5, AcctngKey, Status)) AS unPvtDeleted
INNER JOIN
(SELECT EmployeeId, CAST(SSN4 AS NVARCHAR(200)) AS SSN4, CAST(SSN5 AS NVARCHAR(200)) AS SSN5, CAST(AcctngKey AS NVARCHAR(200)) AS AcctngKey, CAST(Status AS NVARCHAR(200)) AS Status, tableid
FROM inserted i) p
UNPIVOT (NewColumnValue FOR NewColumnName IN (SSN4, SSN5, AcctngKey, Status)) AS unPvtInserted
ON unPvtDeleted.EmployeeId = unPvtInserted.EmployeeId AND unPvtDeleted.OldColumnName = unPvtInserted.NewColumnName
WHERE OldColumnValue != NewColumnValue
I would also strongly suggest that you change the changedate column in the audit table to a datetime column. Dates should not be stored in string columns.
Note that this will not work properly if the EmployeeID is ever changed. That's because there's no way to match rows between the inserted and deleted tables in that case. It's one of the reasons primary keys should never change.
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
April 7, 2009 at 12:00 pm
Wow! Now that is some fancy script. I ran it as is but did no records were inserted into Audit table. I did receive the 1 row(s) was affected message.
Questions:
1) Am I supposed to add to the code to make it work?
2) Where can I place a SELECT or PRINT statement to help me debug what's happening in your trigger? I usually debug by executing UPDATE statement in the same window as the trigger if this matters.
2) What is the difference between UPDATE and AFTER UPDATE?
3) Curious, I hope someone will review my original code to tell me why my dynamic SQL within the cursor is not working.
4) Is the PIVOT and UNPIVOT New SQL Server 2005? I'll have to read up on this to know exactly what your code is doing.
I do thank you. It's not everyday that someone will take the time out to do what you did? Hopefully, one day when you need ASP/VB.NET help, I can return the favor.
Rod,
- I wanna change the world, but they won't give me the source code.
April 7, 2009 at 12:15 pm
rodney.williams (4/7/2009)
I did receive the 1 row(s) was affected message.
That wouldn't have been from the trigger. Because NOCOUNT is on, it won't return the n rows affected message.
1) Am I supposed to add to the code to make it work?
No. It works as is and I have tested it. If you update the tblEmployeeID table and change any of the column values, it inserts into the audit table, or at least the version of the audit table you gave me
I used this to insert a row of test data into the table, since you didn't give me any
INSERT INTO [tempdb].[dbo].[tblEmployeeId]
([EmployeeId],[SSN4],[SSN5],[AcctngKey],[Status],[DateCreated],[CreatedBy],[tableid])
VALUES('A1','123','456','xyz','Z',GETDATE(),'Me','123')
GO
I then ran this update statement
UPDATE [tempdb].[dbo].[tblEmployeeId]
SET SSN5 = '5231'
WHERE EmployeeID = 'A1'
GO
and afterwards the audit table contained the following row.
[font="Courier New"]
tableid employeeid tablename fieldname oldvalue newvalue changedate userid username action
123 A1 tblEmployeeId SSN5 456 5231 Apr 7 2009 8:24PM AVALON\Gail AVALON\Gail U
[/font]
2) Where can I place a SELECT or PRINT statement to help me debug what's happening in your trigger? I usually debug by executing UPDATE statement in the same window as the trigger if this matters.
Triggers are an absolute pain in the neck to debug. You could remove the insert into and examine the resultset that comes back. Remove the where clause to make sure it's not a case where the update isn't changing values.
2) What is the difference between UPDATE and AFTER UPDATE?
None. I just like being explicit.
4) Is the PIVOT and UNPIVOT New SQL Server 2005? I'll have to read up on this to know exactly what your code is doing.
Yup. They're there to switch columns to rows and vis versa
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
April 7, 2009 at 12:52 pm
I ran the query below and also went into employee table and changed SSN4 to 985 and in neither case, there was nothing placed in Audit table. However, yours did work. I'll leave it alone until I've had a chance to play it some more and report back.
.
I had used
Update tblEmployeeID
set SSN4 = '981'
where EmployeeID = '123'
Before theupdate, the record was record was
123235 123 123 1231231231
Despite the fact that you threw new terms at me, one learning triggers and dynamic SQL, and despite the fact that someone called my code B.R.A.B, or B.A.B.S, or B.U.R.P , you're still a sweety. However, I still think my original code , with the fetch, and enumerating through fields with dynamic SQL was a great job and I bat myself on the pack for coming up with it.
Anyhoo, And there you were ...wrapped in swaddling clothes.
April 7, 2009 at 1:06 pm
Cancel that. It did work. Thanks again. How do I give you points.
April 7, 2009 at 1:22 pm
rodney.williams (4/7/2009)
and despite the fact that someone called my code B.R.A.B, or B.A.B.S, or B.U.R.P
RBAR = Row by agonising row. The point being that SQL is a set-based language and, as such, works very badly when processing rows one by one, as with a cursor.
However, I still think my original code , with the fetch, and enumerating through fields with dynamic SQL was a great job and I bat myself on the pack for coming up with it.
The problem with your approach is that it would not work properly if more than one row was updated. In SQL, triggers fire once per update and, if the update affects multiple rows, there will multiple rows in the inserted and deleted tables. Your solution didn't cater for that and if 3 rows were updated by a single statement it would only have audited one of them
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
April 7, 2009 at 1:25 pm
rodney.williams (4/7/2009)
Cancel that. It did work. Thanks again.
Pleasure
How do I give you points.
You don't. Points on this forum are given 1 for each post that a person makes plus any for answering the Question of the Day right
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
April 7, 2009 at 1:56 pm
The update is executed only when human resources changes data about an employee via a web form. (They can't change the employee id). The system is not designed for multiple updates.
April 8, 2009 at 7:13 am
I was reluctant to post this because it was simple oversight on my part, but the script ran on 2 of my SQL Server 2005 machines(1 WinXP and 1 Vista) but would not run on a W2k3. I'm not sure whether this the O/S matters, but according to GilaMonster:
"The database is in compatibility mode 80"
April 8, 2009 at 7:19 am
The OS does not matter in the slightest. These are SQL scripts and they run withint SQL only.
The errors you gave me indicate that the DB that it won't run in is still in Compat mode 80 (compatible with SQL 2000). As such, new 2005 commands (like unpivot) won't work.
Check the compatibility level of the database and, if it's 80, ask the DBA/person in charge if there's any reason that it's in mode 80. If not, set it to mode 90 (testing first, of course, changing the compat mode can break badly written queries) and your trigger should work.
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
April 8, 2009 at 12:51 pm
Tried to change Compat level manually in DB properties but level 90 was not listed. So I ran
sage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.
sp_dbcmptlevel Onboarding, 90
Message:
Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.
Why might this be?
April 8, 2009 at 1:24 pm
rodney.williams (4/8/2009)
Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.
Why might this be?
Because that's a SQL 2000 server. Run SELECT @@Version, see what it says.
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
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply