Issue resolved, right? Well, not exactly. What happens in the event of having multiple inserts? In this case you need to handle each record separately. In order to accomplish this we need to call upon the dark side, with an evil cursor. Before implementing this, I must give forewarning. Know your application, if records are inserted, updated, or deleted in large chunks you might want to be careful, as this could consume a large amount of memory.
As you can see from the following, this takes our previous example and adds some tweaks by injecting a cursor to loop through all the records in the inserted table. For each record that is inserted, a line is written to the text file, and an event is generated.
ALTER trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)
Declare @CmdString varchar (1000)
Declare GetinsertedCursor cursor for
Select ‘Inserted | tablefortrigger | ‘ + convert(varchar(20), getdate()) + ‘ | ‘
+ convert(varchar(5), track)
+ ‘, ‘ + lastname + ‘, ‘ + firstname
from inserted
open GetinsertedCursor
Fetch Next from GetinsertedCursor
into @Msg
while @@fetch_status = 0
Begin
raiserror( 50005, 10, 1, @Msg)
Fetch Next from GetinsertedCursor
into @Msg
set @CmdString = ‘echo ‘ + @Msg + ‘ >> C:logtest.log’
exec master.dbo.xp_cmdshell @CmdString
End
close Getinsertedcursor
deallocate GetInsertedCursor
Now let’s test it by performing a multiple insert.
Insert into tablefortrigger(lastname, firstname)
Select lastname, firstname from employees
Conclusion
Before going ahead and implementing this, some thought needs to be given to performance and security. You will see overhead from writing to the text file, and this might not be viable in a database that has 5000 transactions a minute. Since the xp_cmdshell is operating outside of SQL, errors writing to the file will not rollback the transaction. If an intruder uses a covert channel to change your data it will not be logged in the text file. However, the event log will record the DML change. As a best practice, the number of events should be compared to rows in the log file to find any disparity.
There are many ways to accomplish the goal of logging, and many variations to the above script. I hope you’ll be able to take this script and make improvements and suggestions on how to make it more efficient.
]]>