Need help with another trigger scenerio

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with another trigger scenerio

Author

Topic

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2010-10-07 : 16:00:14

I wish to start archiving data that has been changed on certain tables. For an example, I would like to do the following:

When any field in the table called ORDERS is updated, I would like to archive those changes into the table called DataChangesArchive. The fields I have in the archive table are:

-ID (primary key, auto number)
-TableName (varchar50) This table will be the table name affected
-FieldName (varchar50) This table will be the Field Name updated
-PrimaryKeyField (varchar50) This will be the name of the primary key for the specific table that was updated
-PrimaryKeyData (int) This will be the ID of the row that is affected
-OldData (varchar5000) This will have the old data before the update
-NewData (varchar5000) This will have the new data after the update
-DateTimeEdited (date/time) This will be the date/time of the edit
-Username (varchar50) This will be the username of who edited the record, based upon active directly login information

So, here is an example scenerio:

username JDOE updates the field NOTES in the table ORDERS from TEST NOTES BEFORE UPDATE to TEST NOTES AFTER UPDATE on 10/6/2010 at 14:00.

I would like my trigger to insert that information into the table DATACHANGESARCHIVE

How would I go about doing this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 16:27:13

Show us a data example of the update as well as what will go in the table. I'm lazy, and it's easier for me to see data than read people's descriptions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2010-10-07 : 17:07:54

haha, no problem :)

Here is the actual data that would be put into the table:

TableName: ORDERS
FieldName: NOTES
PrimaryKeyField: ID
PrimaryKeyData: 123456
OldData: SOME RANDOM NOTES TO REPRESENT THE OLD DATA
NewData: THESE NOTES WERE UPDATED TODAY FOR TESTING REASONS
Username: jdoe
DateTimeEdited: 10/6/2010 16:00

That is the data to be inserted, here it is in the form of an sql insert statement:

INSERT INTO DataChangesArchive (TableName, FieldName, PrimaryKeyField, PrimaryKeyData, OldData, NewData, Username, DateTimeEdited) VALUES ('ORDERS', 'NOTES', 'ID', 123456, 'SOME RANDOM NOTES TO REPRESENT THE OLD DATA', 'THESE NOTES WERE UPDATED TODAY FOR TESTING REASONS', 'jdoe', '10/6/2010 16:00')

Here is the update statement that would happen, that would trigger the insert statement:

UPDATE ORDERS SET NOTES = 'THESE NOTES WERE UPDATED TODAY FOR TESTING REASONS' WHERE (ID = 123456)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 17:11:53

Here's a start:
create trigger [dbo].[tu_Orders] ON [dbo].[Orders] FOR UPDATE
as
begin
insert into DataChangesArchive(...)
select 'Orders', 'Notes', 'Id', i.Id, d.Notes, i.Notes, ThisPartYoullNeedToWorkOn, GETDATE()
from inserted i
join deleted d
on i.Id = d.Id
end

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2010-10-08 : 14:09:09

How exactly would I get the username? Our sql server does take notice to who is running what query, I know this because there is one query somewhere in our system that causes locks, and I can find who that user is that is locking, but can't find the query anywhere (seems to be gone now though).

Each query will be ran from a web page, which uses iis authentication that integrates with our active directory, so I can grab the username on the web page, but not sure how to grab it on the sql server. Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 14:13:05

I am not sure if it'll be available from SQL Server or not. Try SUSER_NAME() function.

If that doesn't get it for you or if it's too generic of an account, then the application will need to send the data in the data modification.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2010-10-11 : 15:35:31

Also, I need the Notes in your statement to be a variable...the notes was just an example; however, I need that field to say whatever field is updated so I do not need to make 100 triggers (one for each field).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-11 : 15:38:09

You only need one trigger. Use IF UPDATE(ColumnName) to determine which column it is, and insert based upon that.

Do NOT use a variable for this as a trigger does not only handle one row. If ever an update statement affects more than one row, the trigger will need to handle that situation. Stay away from variables unless you plan on looping through the trigger tables (which is not advisable).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2010-10-11 : 17:44:30

Sory for my lack of knowledge on triggers, but do you mean the actual code is IF UPDATE(ColumnName), or, do I need to replace ColumnName with something?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-11 : 17:49:19

http://msdn.microsoft.com/en-us/library/ms187326.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources