Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-24 : 09:43:19
|
| Clyde writes "I have a trigger on a table called problemthat fires when the status of a record changes.The trigger inserts the information into my AuditTrail tableIn the problem table I have 2 fields problemNumber and RecordNumber. I need to include one of these fields in my auditTrail table so I can identify the record the change occurred on. I am not sure how this is done. Please help or point me in the right direction.Thank you for reading and taking the time to help. CREATE TRIGGER AuditUpdate ON Problem FOR INSERT,UPDATE AS DECLARE @comments VarChar(255) IF UPDATE(Status) SELECT @comments='Status Column was modified' INSERT INTO AuditTrail(TableName, ActionTaken, ActionUser, ActionDate, Comment) VALUES ('Problem','U',User_Name(),GetDate(), @comments)" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-24 : 13:32:22
|
| Note that IF UPDATE(Status) doesn't mean that the value was changed - only that the field was included in an update statement.You either need to change the structure of the audit table to include the other fields or include the values in the commentsINSERT INTO AuditTrail(TableName, ActionTaken, ActionUser, ActionDate, Comment) select 'Problem','U',User_Name(),GetDate(), @comments + '<problemNumber=' + convert(varchar(20),problemNumber) + '><RecordNumber=' + convert(varchar(20),RecordNumber) + '>'from insertedhave a look athttp://www.mindsdoor.net/#Triggersespecially the audit trails==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Source of Trigger