Trigger catches non existant changes ?
-
I have a trigger for audit purposes that catches updates to a table, and logs them.
The problem is, that it seems to catch changes that are not real changes:
ie, if a status field is updated to 'wip' and it was already 'wip', it still reads this as an update.
The way I am currently getting around this, is to cursor through the inserted list, and select the items from the deleted list, and where there is a genuine change, insert into my audit table.
This works fine, but I imagine that it puts an overhead on processing time and it requires intervention is columns are added to the main table.
Do you guys know a better method
Regards
Martin
-
Insert into dbo.AuditTable (Col1, Col2...) Select Col1, Col2 from Inserted I inner join Deleted D on I.id = D.id where (I.Col1 D.Col1 OR I.Col2 D.Col2...)
-
Nice,
Intrestingly I had just made that query too, although it still requires trigger intervention when new cols are added (although says to himself: that would probably be needed anyway to log the new data)
Regards
Martin
-
I have written a trigger for such a case. I have a linked server that holds all our billing info but just connecting to it takes like 5 seconds, so everytime we bill something new I download the data back to our server.
The problem is that I don't transfer every column and that this data must absolutely be read-only (because I only download new stuff instead of re-updating the whole thing) and that even if the data is read only, I have 2 flag columns that my system need to be able to update. So I've written a trigger that check that the updates on those tables are "legal". Now the fun part is that every once in a while I need to transfer one more column on that table and since I don't wanna have to bother with updating the trigger code, I came up with this solution :
CREATE TRIGGER [trAVT_FACTMA_BlockUpdatesDeletes] ON [dbo].[AVT_FACTMA]
INSTEAD OF UPDATE, DELETE
AS
SET NOCOUNT ON
--this trigger forbids any deletes to be made, also it will allow updates to be made only on the FFPRINTED column, anything else will trigger an error
Declare @ErrMsg as varchar(1000)
Declare @TableName as sysname
, @TriggerName as sysname
IF
EXISTS (SELECT * FROM Deleted) AND EXISTS (SELECT * FROM Inserted) --checking it's not a delete query
AND NOT EXISTS (
SELECT *
FROM dbo.SysColumns
WHERE id = (SELECT parent_obj from dbo.SysObjects WHERE id = @@procid) --this allows for the tablename to be changed without affecting the trigger (vs hardcoding the tablename in the trigger)
AND Name not in ('FFPRINTED', 'Solde') --allowed column to be updated
AND SUBSTRING(COLUMNS_UPDATED(), CAST(CEILING (Colid / 8.0) AS INT), 1)
& POWER(2, CASE WHEN Colid % 8 = 0 THEN 8 ELSE Colid % 8 END - 1) > 0
 
BEGIN
--UPDATE ALLOWED LIST of columns
UPDATE FA
SET FA.FFPRINTED = I.FFPRINTED
, FA.Solde = I.Solde
FROM dbo.AVT_FACTMA FA INNER JOIN Inserted I ON FA.FFNOFACT_INT = I.FFNOFACT_INT
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM Deleted)
BEGIN
Select @TableName = OBJECT_NAME (parent_obj), @TriggerName = OBJECT_NAME(@@procid) FROM dbo.SysObjects WHERE id = @@procid
SET @ErrMsg = 'The table ' + @TableName + ' doesn''t allow deletes nor updates (' + @TriggerName + ')'
RAISERROR (@ErrMsg, 13, 1)
END
--ELSE
--BEGIN
--@@ROWCOUNT = 0, no need to generate any err msg.
--END
END
Now the real fun part of this trigger is that I can just copy/paste on another table and it works right away... almost since I still have to change the list of allowed columns updates and remake the update statement but that doesn't take too long
. To make it 100% pastable, I'd have to add a table that would list the allowed columns name for each table but I didn't get my head around to that just yet 
!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply