A new row will be inserted everytime that an update statement occurs on another table and it should describe what was updated. I want to essential check if each new value is = to old value and if it is add that to a string that will become the value of "changes" for the new row.
It might contain 1 field name or many.
Here is the SQL code I am trying to work with inside a stored procedure:
INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
END
But it only adds "ROUTER_NAME" even when both are changed. Maybe I'm going about this all wrong, can anyone offer me and tips on how to log what fields were changed in the database, by who and when?Your UPDATE statments have no WHERE clause. Other than that, I am not clear on exactly what you are trying to do.|||You are right! I totally forgot and left that out!
INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
WHERE ITEM_NAME = @.NAME
ENDselect * from changes_log
output (using a different or changed name and serial_num so that both if statements would be true:
ITEM_NAME _DATE USER_ID CHANGES
------------------------------
NEW 10/17/2003 12:17:33 PM LOUMAS\NCL4504S ROUTER_NAME
Where Changes should contain "ROUTER_NAME SERIAL_NUM"
I might be approaching this all wrong but here is what the requirement is:
KEEP A LOG OF EACH ITEM THAT HAS BEEN EDITED, WHO EDITED IT, WHEN DID THEY DO IT, WHAT FIELDS WERE CHANGED.
NOTE: these items are always edited through a stored procedure. there many field unique for different types of items (from different tables) that could be edited. that is why I attempted to simply capture the field name of field that was changed.
Any ideas or help is welcome, I can change the structure of the Changes_Log table (above) if necessary. I can start from scratch if necesary.|||This is incorrect:
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
try...
|||thanks for the reply. I made your suggested change and the result is "changes" in the new row = NULL|||try...
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = IsNull(CHANGES,'') + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
I gather CHANGES defaults to NULL, and so NULL + anything is NULL. IsNull returns the leftmost non-null argument, and thus should work.|||If I understand correctly you want one record in changes_log per update with a concatenated list of changes in the changes field indicating which fields in another table have been edited.
Why don't you declare a @.Changed varchar and assemble that before making any inserts to changes_log? Do a single insert at the end instead of an insert and two updates.
Are you launching this from a before trigger?
Where does @.Router come from?
Glenn|||YES IT WORKED! THANKS SO MUCH!|||OK, you do understand what I am trying to do correctly.
I'm not sure what you are getting at with declaring a @.Changed varchar? Is this a temporary variable to do a single insert? If so that sounds like a good idea, I just wanted to get something to work first. Actually there will be way more than 2 updates when complete, unless I do it this way.
I am not using any triggers.
@.Router is a value passed to the stored procedure that identifies the item by it's original name even if the name has been changed. @.Name would be the new name if name has been changed, otherwise @.Name = @.Router.
Thanks.|||Ok I changed my code as you suggested and things were working fine, until after adding all the fields I realized that if the current value of a field is NULL the IF statement returns false. Logically I think
IF NOT (@.NEW_VALUE = (SELECT NAME FROM TABLE WHERE ID = @.ID) )
where the @.NEW_VALUE is not NULL and the NAME value returned is null, should return true but it must not compare the same for NULL or something because only those statements do no execute.
Here is my code: note that I tried checking if the value returned from select is null on some items but it still did not work.
ALTER PROCEDURE sp_UPDATE_ROUTER
(
@.ROUTER nvarchar(50),
@.NAME nvarchar(50) = @.ROUTER, /*IF NO NEW NAME IS PASSED DEFAULT = CURRENT ROUTER NAME*/
@.SERIAL nvarchar(25) = NULL,
@.MODEL nvarchar(30) = NULL,
@.IOS_VER nvarchar(15) = NULL,
@.IOS_BASED nvarchar(3) = NULL,
@.BOOT_VER nvarchar(15) = NULL,
@.NETWORK nvarchar(3) = NULL,
@.FIREWALL nvarchar(16)= NULL,
@.REACH nvarchar(60) = NULL,
@.DIAL nvarchar(30) = NULL,
@.FAC_ID nvarchar(20) = NULL,
@.FAC_NAME nvarchar(40) = NULL,
@.STREET nvarchar(60) = NULL,
@.CITY nvarchar(40) = NULL,
@.STATE nvarchar(2) = NULL,
@.ZIP nvarchar(10) = NULL,
@.CONTACT nvarchar(60) = NULL,
@.CONTACT2 nvarchar(60) = NULL,
@.PO nvarchar(20) = NULL,
@.MO nvarchar(20) = NULL,
@.COMMENTS nvarchar(50) = NULL,
@.USER_ID nvarchar(50)
)
AS
DECLARE @.TEMP nvarchar(300)
IF NOT @.NAME = @.ROUTER
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
IF NOT @.MODEL = (SELECT MODEL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MODEL '
END
IF NOT @.IOS_BASED = (SELECT IOS_BASED FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_BASED '
END
IF NOT @.IOS_VER = (SELECT IOS_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_VER '
END
IF NOT @.NETWORK = (SELECT NETWORK FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'NETWORK '
END
IF NOT @.FIREWALL = (SELECT FIREWALL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FIREWALL '
END
IF NOT (@.BOOT_VER = (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'BOOT_VER '
END
IF NOT (@.REACH = (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'REACHABLE_FROM '
END
IF NOT @.DIAL = (SELECT DIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'DIAL_NUM '
END
IF NOT @.FAC_ID = (SELECT FACILITY_ID FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY_ID '
END
IF (NOT @.FAC_NAME = (SELECT FACILITY_NAME FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STREET = (SELECT STREET FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.CITY = (SELECT CITY FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STATE = (SELECT STATE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.ZIP = (SELECT ZIP_CODE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER))
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY ADDR '
END
IF NOT @.CONTACT = (SELECT CONTACT FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT '
END
IF NOT @.CONTACT2 = (SELECT CONTACT2 FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT2 '
END
IF NOT @.PO = (SELECT PO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'PO '
END
IF NOT @.MO = (SELECT MO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MO '
END
IF NOT @.COMMENTS = (SELECT COMMENTS FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'COMMENTS '
ENDINSERT INTO CHANGES_LOG (ITEM_NAME, _DATE, SERIAL_NUM, USER_ID, CHANGES) VALUES(@.NAME, GETDATE(), @.SERIAL, @.USER_ID, @.TEMP)
select * from changes_log
RETURN
THANKS|||What I meant with the @.Changed varchar was what you did with @.TEMP.|||If I understand your problem, you need to check the @.Parameters if they are null before comparing them with the previous value selected from ROUTERS.
A nested IF for each value would work.
IF NOT IsNull(@.SERIAL,'') = ''BEGIN
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
END
Also I think you should be able to SET @.TEMP = '' right off, and eliminate the IsNull check in your existing code. This would go back to the simpler...
SET @.TEMP = @.Temp + 'SERIAL_NUM '
I don't know if that will finish it, but it's another step...|||well I cna tell you just to test it I used 'xyz' for every value and none are null and none currently have a value of 'xyz'|||And?
What were your results? Your changelog should've shown a change for each field right? Did it work?|||The same as above, I posted that message with the datavalues that I described. I have already thought of all that.
You were right. Sorry and thank you.
LacOniC: