changes log

I want to keep a simple table that logs each time an item in inventory is changed.

Here is the table I came up with thoe track this:

CHANGES_LOG
-------
ITEM_NAME
SERIAL_NUM
_DATE
USER_ID
CHANGES_MADE

I would like to capture the name of each field which is changed. I'm sure other people have implemented something like this before, can anyone please give me some advice on how to capture this, when and where?I have found it more helpful to log the specific field name, the old value and the new value, as opposed to a "CHANGES_MADE" column which would probably have a bunch of stuff jumbled together.

CHANGES_LOG
-------
ITEM_NAME
SERIAL_NUM
_DATE
USER_ID
FIELDNAME
OLD_VALUE
NEW_VALUE

Currently, we have a trigger doing the insert into the log table. This seemed to be the logical place to do it, since every change would be captured regardless of what method would be used to make the change.

However, we are considering moving the logging code into a stored procedure and then calling this stored procedure from the different stored procedures which update the inventory table. One of the reasons among others is the needed ability to capture the user ID.

Terri|||Thanks a lot for the response.

I am still wondering how specifically you determined which field was changed? How does the trigger get the values?

I have not used triggers much because in my classes (I am not working at a co-op), they always told me that triggers were buggy and undependable, therefore use stored procedures whenever possible.|||

if update(<columnname>)

tells you if a particular column has been modified..

HTH|||Wow I never knew you could do that, that is awesome!|||::I have not used triggers much because in my classes (I am not working at a co-op), they
::always told me that triggers were buggy and undependable, therefore use stored
::procedures whenever possible.

i have designed an inventory maanegment system at my work palce. we have 9-10 branch offices at various locations within 10 mi frm our main office. every time the branch offices make a sale, their inventory goes down (obviously)...i have used triggers to create purchase orders anytime their inventory goes down a certain level ( as set in the Re Order Level) and it automatically creates a purchase order in another table ( for the Re-Order Quantity)..so the manager (or admin) from the main branch can always know the xact inventory level at each branch offices and ship them the inventory they need..the branch offices need not even call the main office "hey we need some inventory"..'coz its already on the way.

all the triggers work perfectly in my case..
the disadv with triggers is tht they are not under your control..they are fired automatically..so you should be pretty careful with the code on when to fire them..
stored procs on the other hand, have to ( can) be called manually...they are not really a replacement for each other..each has its own situations..

HTH|||Okay - sorry about the late reply, I was out sick.

I have decided to use a trigger but I am still not understanding how to get all the data from the trigger such as . .

CHANGES_LOG
-------
ITEM_NAME
SERIAL_NUM
_DATE
USER_ID
FIELDNAME
OLD_VALUE
NEW_VALUE

I know how to get FIELDNAME using IF UPDATE as above and I can get _DATE using GETDATE() but how to I determine the rest of it from the trigger?
Can someone help?|||This is what I am suggesting:

CHANGES_LOG
-------
ITEM_NAME --> this should be the primary key --> inserted.item_name
SERIAL_NUM --> not sure what your intention is here
_DATE --> GETDATE()
USER_ID --> ay, there's the rub
FIELDNAME --> if the test for IF UPDATE('fieldname') is true, that's the fieldname
OLD_VALUE --> deleted.fieldname
NEW_VALUE --> inserted.fieldname

If you are looking for the User ID from your application's users credentials, you will not be able to get to this information. The best you can do is to have a USER_ID column in your table and make sure to update this with your calling stored procedure. You would then use inserted.USER_ID to capture the current USER_ID on the record, which may or may not be the user who made the update.

You can also use SUSER_SNAME(), which will give you the Windows authenticated user's name.

So, what I am saying is you might consider having an "SUSER" column as well as a "USER_ID" column. The SUSER column will contain Windows user name, and the USER_ID column will contain the USER_ID that was in the record at the time it was updated.

Terri|||OK that helped a lot but I am wondering. .

inserted.item_name gives you the item_name of the item which caused the trigger right? if the trigger therefore was called by UPDATE then you would use updated.item_name?

You brought up a good point, I probably don't need to store the serial num because I could look it up from the other table, with the primary key - unless that item was deleted. But I guess that is a whole other issue. I just know that the users want to be able to view the serial number of an item that was deleted or updated.

I am using windows authentication for my application anyway so wouldn't SUSER_SNAME() be what I need? I think it will. Because I was thinking that USER_ID would capture some kind of SQL user ID and that is not what I want, that does identify individuals.|||OK I think I have got it. Should this work?

CREATE TRIGGER ROUTERS_Trigger1
ON dbo.ROUTERS
FOR UPDATE
AS
DECLARE @.TEMP AS nvarchar(50)
IF UPDATE (ROUTER_NAME)
BEGIN
SET @.TEMP = 'ROUTER_NAME'
END
IF UPDATE (SERIAL_NUM)
BEGIN
SET @.TEMP = 'SERIAL_NUM'
END
IF UPDATE (MODEL)
BEGIN
SET @.TEMP = 'MODEL'
END
IF UPDATE (IOS_VER)
BEGIN
SET @.TEMP = 'IOS_VER'
END
IF UPDATE (IOS_BASED)
BEGIN
SET @.TEMP = 'IOS_BASED'
END
INSERT INTO CHANGES_LOG (ITEM_NAME, _DATE, [USER_ID], FIELDNAME, OLD_VALUE, NEW_VALUE)
VALUES (inserted.ITEM_NAME, GETDATE(), SUSER_SNAME(), @.TEMP, deleted.@.TEMP, inserted.@.TEMP)

Also what happens if several fields are changed? If there a row in the Changes_log for each one? Do I need to use a different trigger for logging INSERTS and UPDATES to Routers table?|||You should read up on triggers in Books Online. This should help clarify a few things for you.

In a trigger, the "deleted" table holds the values of the data as they were BEFORE the update happened. The "inserted" table holds the values of the data AFTER the updated. There is no "updated" table.

It sounds like SUSER_SNAME() will do the trick for you.

I am not certain of the purpose of your ITEM_NAME and SERIAL_NUM columns so I can't really comment on that. In our inventory management system, we do not physically delete inventory records; we mark them as "inactive". So we do not have the problem of disappearing items.

Terri|||When I try to use the code I posted above, I get an error for trying to insert the value inserter.ROUTER_NAME (which is the primary key of ROUTERS table) I am using visual studio and when I try to save the trigger it says:

ADO error: The Name 'ROUTER_NAME' is not permitted in this context. Only constants, expressions, or variables are allowed here. Column Names are not permitted.

Thanks!!|||you prbly might have to take those values into some variables...
ex:

select @.instemp=inserted.<colname>from inserted
select @.deltemp=deleted.<colname> from deleted

and use these variables in your insert stmt..

HTH|||Okay sorry I haven't been working on this lately. I was wrong, the SUSER_SNAME is not working for me because I am connecting using a SQL user_id and password and it always gives me that User ID, not the windows one. When I tryed to connected to SQL using windows auth. I think it only connects with one generic ASPNET user?!?

So I guess I will not be able to capture the user ID that way. . . .