I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose.
History
guid (uniqueidentifier)
dateCreated(datetime)
lastDateUpdated(datetime)
changesMade(varchar(50)) -- eg; dropdownlist
oldValue(varchar(50)) -- compaq
newValue(varchar(50)) --dell
updateBy(varchar(10)) <-- username of registered user
comNo(int) = foreign key for Computer table
History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.
You can use a trigger in MSSQL to take action anytime a change to a row (insert, update, delete) takes place.
|||
I try to use trigger and get this error. Incorrect syntax near @.TEMP
This is the code
CREATE TRIGGERChangesTrigger
ONdbo.Computer
FOR UPDATE
AS
DECLARE@.TEMPAs varchar(50)
IF UPDATE(active)
BEGIN
SET@.TEMP ='active'
END
IF UPDATE(make)
BEGIN
SET@.TEMP ='make'
END
IF UPDATE(model)
BEGIN
SET@.TEMP ='model'
END
IF UPDATE(processor)
BEGIN
SET@.TEMP ='processor'
END
IF UPDATE(speed)
BEGIN
SET@.TEMP ='speed'
END
IF UPDATE(memory)
BEGIN
SET@.TEMP ='memory'
END
IF UPDATE(harddisk)
BEGIN
SET@.TEMP ='harddisk'
END
IF UPDATE(serialNo)
BEGIN
SET@.TEMP ='serialNo'
END
IF UPDATE(assetNo)
BEGIN
SET@.TEMP ='assetNo'
END
IF UPDATE(opsystem)
BEGIN
SET@.TEMP ='opSystem'
END
IF UPDATE(msOfficeVersion)
BEGIN
SET@.TEMP ='msOfficeVersion'
END
IF UPDATE(invoiceNo)
BEGIN
SET@.TEMP ='invoiceNo'
END
IF UPDATE(poNo)
BEGIN
SET@.TEMP ='poNo'
END
IF UPDATE(networkCard)
BEGIN
SET@.TEMP ='networkCard'
END
IF UPDATE(ipAddress)
BEGIN
SET@.TEMP ='ipAddress'
END
IF UPDATE(ras)
BEGIN
SET@.TEMP ='ras'
END
IF UPDATE(rasvpn)
BEGIN
SET@.TEMP ='rasvpn'
END
IF UPDATE(device)
BEGIN
SET@.TEMP ='device'
END
IF UPDATE(floppyDisk)
BEGIN
SET@.TEMP ='ROUTER_NAME'
END
IF UPDATE(userId)
BEGIN
SET@.TEMP ='ROUTER_NAME'
END
INSERT INTOHistory(lastDateUpdated, changesMade, oldValue, newValue, wscNo, updateBy)
VALUES(GETDATE(), @.TEMP, deleted.@.TEMP, inserted.@.TEMP, updated.wscNo,SUSER_SNAME())
Then I found out that SUSER_NAME doesn't insert the name of people who logged in and update the data.
|||Why do you usedeleted.@.TEMP/inserted.@.TEMP ? @.TEMP is a variable, not a column in the table. If you want to insert into History table with values from different columns in deleted/inserted table, you need different INSERT command for each case (for different updated columns).|||
Or we can make a trick by using COLUMNS_UPDATED() function and EXECUTE dynamic SQL statement in the trigger. Suppose the dbo.Computer table looks like:
dbo.Computer (ComID,active,make,model,processor,speed,memory,harddisk,serialNo,assetNo,
opSystem,msOfficeVersion,invoiceNo,poNo,networkCard,ipAddress,ras,rasvpn)
Then we can use such a trigger:
ALTER TRIGGER ChangesTrigger ON dbo.Computer FOR UPDATE
AS
DECLARE @.TEMP As varchar(50)
SELECT @.TEMP=CASE WHEN COLUMNS_UPDATED()&2=2
THEN 'active'
WHEN COLUMNS_UPDATED()&4=4
THEN 'make'
WHEN COLUMNS_UPDATED()&8=8
THEN 'model'
WHEN COLUMNS_UPDATED()&16=16
THEN 'processor'
--<add other conditions here...>
WHEN COLUMNS_UPDATED()&power(2,20-1)=power(2,20-1)
THEN 'ROUTER_NAME'
WHEN COLUMNS_UPDATED()&power(2,21-1)=power(2,21-1)
THEN 'ROUTER_NAME'
END
EXEC('INSERT INTO History(lastDateUpdated, changesMade, oldValue, newValue, wscNo, updateBy)
VALUES(GETDATE(),'''+@.TEMP+''',deleted.['+@.TEMP+'],inserted.['+@.TEMP+'], updated.wscNo, SUSER_SNAME())')
go
For more information about COLUMNS_UPDATED function, you can refer to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
|||
I got a new idea: if update to the dbo.Computer table will always effect only 1 column, we can get the value of @.Temp (that's the effected column name, right?) directly from system table:
ALTER trigger trg_test on tblTrg for update
as
declare @.temp sysname
SELECT @.temp=name FROM syscolumns
WHERE id=OBJECT_ID('tblTrg') and POWER(2,colid-1)=COLUMNS_UPDATED()
EXEC('INSERT INTO History(lastDateUpdated, changesMade, oldValue, newValue, wscNo, updateBy)
VALUES(GETDATE(),'''+@.TEMP+''',deleted.['+@.TEMP+'],inserted.['+@.TEMP+'], updated.wscNo, SUSER_SNAME())')
go
|||What is the numbers anyway and how to use it. Anyway, I never use trigger and stored procedure before so I don't know the command that can be used in trigger. After all, the code that i use for trigger was taken from other sources and i modify according to my database. thanks for direction. i will try the codes.|||
This is the error I get.
The name "deleted.serialNo" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
This is the trigger I wrote since I'm confuse with the use of power(2,20-1). What is wrong with the code. I change updated.wscNo to inserted.wscNo since wscNo couldn,t be updated and I actually don't know what should i put in inserted.wscNo because I want to catch the no of computer that changes are made.
ALTER TRIGGER
ChangesTriggerONdbo.ComputerFOR UPDATE
AS
DECLARE
@.TEMPAs varchar(50)
SELECT
@.TEMP=CASE WHEN COLUMNS_UPDATED()&2=2THEN'active'WHEN COLUMNS_UPDATED()&4=4THEN'make'WHEN COLUMNS_UPDATED()&8=8THEN'model'WHEN COLUMNS_UPDATED()&16=16THEN'processor'WHEN COLUMNS_UPDATED()&32=32THEN'speed'WHEN COLUMNS_UPDATED()&64=64THEN'memory'WHEN COLUMNS_UPDATED()&128=128THEN'harddisk'WHEN COLUMNS_UPDATED()&256=256THEN'serialNo'WHEN COLUMNS_UPDATED()&512=512THEN'assetNo'WHEN COLUMNS_UPDATED()&1024=1024THEN'opSystem'WHEN COLUMNS_UPDATED()&2048=2048THEN'version'WHEN COLUMNS_UPDATED()&4096=4096THEN'opSerial'WHEN COLUMNS_UPDATED()&8192=8192THEN'msOfficeVersion'WHEN COLUMNS_UPDATED()&16384=16384THEN'invoiceNo'WHEN COLUMNS_UPDATED()&32768=32768THEN'poNo'WHEN COLUMNS_UPDATED()&65536=65536THEN'networkCard'WHEN COLUMNS_UPDATED()&131072=131072THEN'ipAddress'WHEN COLUMNS_UPDATED()&262144=262144THEN'ras'WHEN COLUMNS_UPDATED()&524288=524288THEN'rasvpn'WHEN COLUMNS_UPDATED()&1048576=1048576THEN'device'WHEN COLUMNS_UPDATED()&2097152=2097152THEN'floppyDisk'WHEN COLUMNS_UPDATED()&4194304=4194304THEN'userId'ENDEXEC('INSERT INTO History(lastDateUpdated, changesMade, oldValue, newValue, wscNo, updateBy)VALUES(GETDATE(), '''+@.TEMP+''', deleted.['+@.TEMP+'], inserted.['+@.TEMP+'], inserted.wscNo, SUSER_SNAME())')
I delete go because I got an error regarding go.
|||
deleted and inserted aren't available in that context (EXEC) because it's technically not executing within the context of the trigger any longer. Also INSERT INTO ... VALUES would be the wrong syntax for accessing deleted and inserted pseudo tables. You would need an INSERT ... SELECT. The trigger would also fail if you modified multiple columns (Only recording a single change), or multiple rows (possibly, depends on how you fix the problems), and logging changes that don't really happen (UPDATE table SET col=col) would show records being changed that don't get changed.
You would need to do something like:
DECLARE@.TEMP varchar(50)
DECLARE @.oldval varchar(50)
DECLARE @.newval varchar(50)
IF COLUMNS_UPDATED()&2=2 SELECT @.TEMP='active',@.oldval=deleted.active,@.newval=deleted.active FROM inserted JOIN deleted ON inserted.wsc=deleted.wsc
...
and at then reference @.oldval and @.newval instead of inserted/deleted pseudo tables, but that still has the multiple columns and multiple row update problems.
However, I would suggest a change in approach, which works much better. Design a standard audit table like:
AuditID identity int
ChangeType char(1) ' I/U/D
ChangeDate datetime default getdate()
(a column here for each column in your table with same column definiation - with the exception that all columns are nullable)
The create your triggers like:
ON INSERT:
INSERT INTO History(ChangeType,...) SELECT 'I',I.* FROM inserted
ON UPDATE:
INSERT INTO History(ChangeType,...) SELECT 'U',I.* FROM inserted i ' Optionally add JOIN deleted d WHERE (i.col1<>d.col1 OR (i.col1 IS NULL AND d.col1 IS NOT NULL) OR (i.col1 IS NOT NULL AND d.col1 IS NULL)) and repeat for each column to remove false change entries
ON DELETE:
INSERT INTO History(ChangeType,wscNo) SELECT 'D',deleted.wscNo FROM deleted
Then you can track every change to every column, including the cases where multiple columns (or records) are updated at a time. Although, it does make the logic for displaying what was changed a bit more difficult, it's not that complicated, and history tracking usually isn't done that often in most cases. This is also a pretty common audit table, so it can carry over from table to table and project to project. (Most people would call this table something like AuditComputers, where Computers is the original table).
You will have the problem where SUSER_SNAME won't give you the name of the person who did the change, unless you create a SQL user for each person that is allowed to make changes, and each connection by those users are done with their own credentials, but that's a different issue. If you need to propegate actual user information from asp.net where they don't log in to sql server with their own identity, then it gets a bit more tricky. You can still do it (Mostly) with triggers, if you define an UpdatedBy column in the original table. You can also enforce specific rules within the insert/update triggers to make sure it's being filled LIKE:
IF NOT UPDATED(UpdatedBy) BEGIN RAISEERROR('You must supply the UpdatedBy column when modifying records') ROLLBACK END ELSE BEGIN ... END
Deleted entries are a bit more tricky because you don't have the user information in any of the information available to the trigger when it happens. In that case, you may want to make a stored procedure to deleted records from the table, and insert the data into the audit table within that. Then remove access to delete from the table directly through permissions.
|||
This is the code I made. I got an error between END and ON. I think it require me to put some code but i don't know what it is.
CREATE TRIGGERChangedTrigger
ONdbo.Computer
FOR UPDATE
AS
DECLARE
@.changeTypechar(1),
@.guiduniqueidentifier,
@.dateCreateddatetime,
@.lastDateUpdateddatetime,
@.changesMadevarchar(50),
@.oldValuevarchar(50),
@.newValuevarchar(50),
@.wscNobigint,
@.updateByvarchar(10)
ON INSERT:
BEGIN
INSERT INTOHistory(guid,changeType,dateCreated,lastDateUpdated,changesMade,oldValue,newValue,wscNo,updateBy)
VALUES(@.guid,'I',GETDATE(),GETDATE(),@.changesMade,@.oldValue,@.newValue,@.wscNo,SUSER_NAME())
SELECT'I', I.*FROMinserted
END
ON UPDATE:
BEGIN
INSERT INTOHistory(guid,changeType,lastDateUpdated,changesMade,oldValue,newValue,wscNo,updateBy)
VALUES(@.guid,'U',GETDATE(),@.changesMade,@.oldValue,@.newValue,@.wscNo,SUSER_NAME())
SELECT'U', I.*FROMinserted iJOINdeleted d
END
ON DELETE:
BEGIN
INSERT INTOHistory(guid,changeType,wscNo)VALUES(@.guid,'D',@.wscNo)
SELECT'D', deleted.wscNoFROMdeleted
END
|||Sorry, the ON INSERT: wasn't a T-SQL command. The On insert would look like:
CREATE TRIGGERComputer_Inserted
ONdbo.Computer
FOR INSERT
AS
BEGIN
INSERT INTOHistory(changeType,{List each column in the Computer Table here})
SELECT'I', i.*FROMinserted i
END
THe update portion would be:
CREATE TRIGGERComputer_Updated
ONdbo.Computer
FOR UPDATE
AS
BEGIN
INSERT INTOHistory(changeType,{List each column in the Computer Table here})
SELECT'U', i.*FROMinserted i
END
And the deleted trigger would be:
CREATE TRIGGERComputer_Updated
ONdbo.Computer
FOR DELETE
AS
BEGIN
INSERT INTOHistory(ChangeType,wscNo)
SELECT'D', deleted.wscNoFROMdeleted
END
|||
Owh! It should be separated. Okay I'll try and let u know if any problem occur.
|||
I got this error for insert and update trigger. 'Incorrect syntax near {'
Update:
ALTER TRIGGERInsertTrigger
ONdbo.Computer
FOR INSERT
AS
BEGIN
INSERT INTOHistory(changeType,{wscNo,active,make,model,processor,speed,memory,harddisk,serialNo,assetNo,opSystem,version,opSerial,msOfficeVersion,invoiceNo,poNo,networkCard,ipAddress,ras,rasvpn,device,floppyDisk,userId})
SELECT'I', i.*FROMinserted i
END
Insert:
ALTER TRIGGERUpdateTrigger
ONdbo.Computer
FOR UPDATE
AS
BEGIN
INSERT INTOHistory(changeType,{wscNo,active,make,model,processor,speed,memory,harddisk,serialNo,assetNo,opSystem,version,opSerial,msOfficeVersion,invoiceNo,poNo,networkCard,ipAddress,ras,rasvpn,device,floppyDisk,userId})
SELECT'U', i.*FROMinserted i
END
If I remove {} the error says that all the computer table column is not exist
|||remove the {}'s and make sure your History table also has those columns (wscNo,active,make,model, etc)|||Oh, I have to include the computer table attribute in it. Is there any other way, let's say like History table i've created, i make wscNo(primary key for tabel Computer) as a foreign key.
